使用QT连接MySQL

首先新建一个QT application，对main.cpp作如下改动：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
#include "zqd.h"
#include <QApplication>
#include <QCoreApplication>
#include <QSqlDatabase>
#include <QDebug>
#include <QStringList>
#include <QSqlQuery>
#include <QtSql/QSqlError>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);

    // output useful databases
    qDebug() << "Available drivers:";
    QStringList drivers = QSqlDatabase::drivers();
    foreach(QString driver, drivers)
        qDebug() << driver;

    // open MySQL
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL3","QPSQL7");
    db.setHostName("localhost");
    db.setDatabaseName("mydata");
    db.setUserName("root");
    db.setPassword("123456");
    if (!db.open()){
        qDebug() << "Failed to connect to root mysql admin";
        qDebug() << db.lastError().text();
    }
    else qDebug() << "open";

    QSqlQuery query(db);

    // attention that varchar should be define length, otherwise error.
    query.exec("create table student(id int primary key,name varchar(20))");

    query.exec("insert into student values(1,'xiaogang')");
    query.exec("insert into student values(2,'xiaoming')");
    query.exec("insert into student values(3,'xiaohong')");

    query.exec("select id,name from student where id >= 2");

    while(query.next())
    {
        int value0 = query.value(0).toInt();
        QString value1 = query.value(1).toString();
        qDebug() << value0 << value1 ;
    }

    return a.exec();
}

以上代码便是简单的在QT中连接MySQL的Demo

注意

MySQL版本为8.0.x-64位时，连接时如果有如下错误信息：

1
Authentication plugin 'caching_sha2_password' cannot be loaded

是因为用户登录时密码的认证方式没有修改，使用了sha2加密，登录MYSQL修改即可，过程如下：

1. 命令提示符登录MySQL
1
mysql -u root -p
2. 修改账户密码加密规则并更新用户密码
1
2
3
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;   #修改加密规则 

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';   #更新一下用户的密码
3. 刷新权限并且重置密码
1
FLUSH PRIVILEGES;   #刷新权限
4. 再次重置密码
1
alter user 'root'@'localhost' identified by '654321';

结语

QT调试过程中引用qDebug输出错误信息，能够帮助准确定位问题。以上操作之后就可以连接到数据库了，远程连接同上。