Qt 5.14.2 connect to Mysql database

本文所使用的是MSVC 2017 Compiled belowmysql的dll驱动
Used in the third updateMinGW 64 编译的,操作和MSVC类似,Just replace one file.MSVC 2017生成的pluginThe driver can be downloaded directly.

1.下载mysql 5.7.30 winx64 

Mysql 5.7.30 winx64
本文安装目录为 D:\mysql-5.7.30-winx64

2.下载Qt 5.14.2

Qt 5.14.2
本文安装目录为 D:\Qt5.14.2
安装的时候记得选择source 以及 msvc 2017 x64的版本（This article was compiled based on this version,Please choose other versions）

3.添加系统环境变量

4.编译mysql项目工程
工程目录为：D:\Qt5.14.2\5.14.2\Src\qtbase\src\plugins\sqldrivers\mysql
使用qt5.14.2 Open this directory .pro项目文件,Modified after opening the projectpro中的代码

TARGET = qsqlmysql

HEADERS += $$PWD/qsql_mysql_p.h
SOURCES += $$PWD/qsql_mysql.cpp $$PWD/main.cpp

#QMAKE_USE += mysql

OTHER_FILES += mysql.json

PLUGIN_CLASS_NAME = QMYSQLDriverPlugin
include(../qsqldriverbase.pri)

INCLUDEPATH += "D:/mysql-5.7.30-winx64/mysql-5.7.30-winx64/include"
DEPENDPATH += "D:/mysql-5.7.30-winx64/mysql-5.7.30-winx64/include"
LIBS += "D:/mysql-5.7.30-winx64/mysql-5.7.30-winx64/lib/libmysql.lib"

5. Open after compilationD:\plugins\sqldrivers Folder discovery generated driver files,And copy the file selected below and copy to
D:\Qt5.14.2\5.14.2\msvc2017_64\plugins\sqldrivers

The copied file directory is shown in the following figure

5.复制 D:\mysql-5.7.30-winx64\mysql-5.7.30-winx64\lib The two files in this directory are shown below

Copy to the following directory D:\Qt5.14.2\5.14.2\msvc2017_64\bin

6. 在mysqlCreate a database for testing and create any table,如下图所示

7. 在QT中新建一个widget工程,测试代码如下,

7.1 首先在pro中添加 

QT += sql  

7.2 并引用相应的头文件 

#include <QSqlDatabase>

7.3 在main中或widget中添加如下代码进行测试

    qDebug()<<"available drivers:";
    QStringList drivers = QSqlDatabase::drivers();
    foreach(QString driver, drivers)
        qDebug()<<driver;

    QSqlDatabase data_base = QSqlDatabase::addDatabase("QMYSQL");
    data_base.setHostName("127.0.0.1");  //设置主机地址
    data_base.setPort(3306);  //设置端口 默认3306
    data_base.setDatabaseName("sql5user");  //设置数据库名称
    data_base.setUserName("root");  //对应数据库的用户名
    data_base.setPassword("123456");//The password of the corresponding database
    
    if(!data_base.open())
        qDebug()<<"failed";
    else
        qDebug()<<"success";

7.4 The output of the program is shown in the figure below

第一次更新
在使用qt连接阿里云服务器mysql5.28的时候,应该注意的是

1. The specified database table name should exist
2. Check the authorized username in the database,通常默认情况下root 的 host 为“localhost”,故此,A user needs to be authorized for access,使你的host 变为"%"即可
3. 默认情况下,Newly authorized access users do not have permission,So the permissions should be given to the newly added user

Check authorized firstip,authentication_string指的是 password（5.7的原因）

SELECT host,user,authentication_string,Grant_priv,Super_priv FROM mysql.user;

Then use the following statement to change the permissions,代码中xxxx指的是用户的username,
(在出现ERROR:1142 That is, when the permission is denied, the following statement needs to be executed,同时刷新mysql, 重启mysql即可解决)

   UPDATE mysql.user SET 
  `Select_priv` = 'Y',
  `Insert_priv` = 'Y',
  `Update_priv` = 'Y',
  `Delete_priv` = 'Y',
  `Create_priv` = 'Y',
  `Drop_priv` = 'Y',
  `Reload_priv` = 'Y',
  `Shutdown_priv` = 'Y',
  `Process_priv` = 'Y',
  `File_priv` = 'Y',
  `Grant_priv` = 'Y',
  `References_priv` = 'Y',
  `Index_priv` = 'Y',
  `Alter_priv` = 'Y',
  `Show_db_priv` = 'Y',
  `Super_priv` = 'Y',
  `Create_tmp_table_priv` = 'Y',
  `Lock_tables_priv` = 'Y',
  `Execute_priv` = 'Y',
  `Repl_slave_priv` = 'Y',
  `Repl_client_priv` = 'Y',
  `Create_view_priv` = 'Y',
  `Show_view_priv` = 'Y',
  `Create_routine_priv` = 'Y',
  `Alter_routine_priv` = 'Y',
  `Create_user_priv` = 'Y',
  `Event_priv` = 'Y',
  `Trigger_priv` = 'Y',
  `Create_tablespace_priv` = 'Y'
   WHERE User='xxx';

在qtThe format for running a database statement in is

#include <QSqlError>
#include <QSqlQuery>

QSqlQuery  SqlQuery;
SqlQuery.exec(" Insert yours heresql语句"); //If it can be guaranteed to be performed correctly,Just use this sentence,Otherwise, use the following statement to judge

if(!SqlQuery.exec("Insert yours heresql语句"))//此语句==!QSqlQuery.exec("Insert yours heresql语句")
 {
    
     qDebug()<<"语句执行错误";
 }
 else
 {
    
	 qDebug()<<"语句执行成功";
 }

If the query statement to run,Need to know the format and number of columns in the database table

使用如下示例,If there are two columns in the table, respectively name 和 password

#include <QString>
  QString login_check =QString("select * from userlogin;");
  if(!SqlQuery.exec(login_check))//==!QSqlQuery.exec(create_sql)
  {
    
      qDebug()<<"语句执行错误";
  }
  else
  {
    
      QString show_name ;
      QString show_pwd ;
      while(SqlQuery.next())
      {
    
          show_name = SqlQuery.value(0).toString();

          show_pwd = SqlQuery.value(1).toString();
             qDebug() <<show_name<<show_pwd;
      }
      if(show_name==NULL || show_pwd==NULL)
           qDebug()<<"not user";
  }

The data in the table and inqtThe received data is shown in Fig

第二次更新

在qt5.14.2 release后,The file packaging test is unable to connect directlymysql数据的
The workaround is after using the packager,在exe同级目录下添加libmysql.dll
libmysqlThe source file location is shown in the following figure

第三次更新
due to recent useMiniGW 64-bit 进行编译,The corresponding driver needs to be generated,首先按MSVCmethod to copy,
The environment does not need to be changed,Generate the original before compilingD:\ plugindirectory is cut elsewhere
再次使用mysql.profile to compile,选择MiniGW 64-bit 编译.
会生成如下文件
将qsqlmysql.dllRe-copy to the following directory,注意是mingw的,Make a replacement to connect to the database.
Mingw 64 的大小是284kb ,MSVC 2017 64bit 的是61kb!