Ubuntu20.04上安装Qt ODBC和MySql

本文记录下如何在Ubuntu上安装ODBC，以及Qt的ODBC插件，MySql插件，最后实现通过Qt ODBC去连接MySql。 主要记录一些重要步骤，需要一些基础了解，细支末节，难以言表！
所用环境：ubuntu 20.04, Qt5.15.2, MySql8.0.25

---

ubuntu上默认没有安装ODBC开发库和驱动程序，需要用户自己安装。
所以当时编译Qt时或者 像这样单独编译时，配置结果并没有检测到。

Qt Sql Drivers:
  DB2 (IBM) .............................. no
  InterBase .............................. no
  MySql .................................. no
  OCI (Oracle) ........................... no
  ODBC ................................... no
  PostgreSQL ............................. no
  SQLite2 ................................ no
  SQLite ................................. yes
    Using system provided SQLite ......... no
  TDS (Sybase) ........................... no

ubuntu上可以通过包管理工具直接安装！

1. 安装odbc开发包：unixodbc-dev

其依赖如下：

The following additional packages will be installed:
  autoconf automake autotools-dev libltdl-dev libtool
Suggested packages:
  autoconf-archive gnu-standards autoconf-doc gettext libtool-doc gfortran | fortran95-compiler
  gcj-jdk
The following NEW packages will be installed:
  autoconf automake autotools-dev libltdl-dev libtool unixodbc-dev

可以查看，主要是安装的这个库文件起了作用：/usr/lib/x86_64-linux-gnu/libodbc.so

如此，再次在qtbase\src\plugins\sqldrivers中编译驱动插件时，可以看到qmake可以检测到odbc了，但是mySql依旧没有。

2. 安装odbc管理工具：unixodbc

其依赖如下：

Suggested packages:
  libmyodbc odbc-postgresql tdsodbc unixodbc-bin
The following NEW packages will be installed:
  libodbc1 odbcinst odbcinst1debian2 unixodbc

其中安装了这个工具，odbcinst
这个工具的介绍：

An ODBC Installer and Uninstaller. Updates system files, and increases/decreases usage counts but does not actually copy or remove any files.

odbcinst -j 输出配置信息

unixODBC 2.3.6
DRIVERS............: /etc/odbcinst.ini   可查看当前安装的odbc驱动，可以看到目前这里是空的
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

3. 安装MySql的开发库：libmysql++-dev

当时我安装的是这个库，可能并不精准，可以尝试只安装libmysqlclient-dev

其依赖如下：

The following additional packages will be installed:
  default-libmysqlclient-dev libmysql++3v5 libmysqlclient-dev
Suggested packages:
  libmysql++-doc
The following NEW packages will be installed:
  default-libmysqlclient-dev libmysql++-dev libmysql++3v5 libmysqlclient-dev

在安装了mysql的开发库后，再次到qtbase\src\plugins\sqldrivers中编译驱动插件，此时可以看到qmake已经可以检测到mySql了。

4. 安装MySql，服务端和客户端：mysql-server-8.0

其依赖如下：

 The following additional packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-perl
  libhtml-template-perl libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0
  mysql-client-core-8.0 mysql-server-core-8.0
Suggested packages:
  libipc-sharedcache-perl mailx tinyca
The following NEW packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-perl
  libhtml-template-perl libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0
  mysql-client-core-8.0 mysql-server-8.0 mysql-server-core-8.0

可选，安装库 libmysqlcppconn-dev

Description: MySQL Connector for C++ (development files)
MySQL Connector/C++ is a MySQL database connector for C++.

其依赖如下：

The following additional packages will be installed:
  libboost-dev libboost1.71-dev libmysqlcppconn7v5
Suggested packages:
  libboost-doc libboost1.71-doc libboost-atomic1.71-dev libboost-chrono1.71-dev libboost-container1.71-dev
  libboost-context1.71-dev libboost-contract1.71-dev libboost-coroutine1.71-dev libboost-date-time1.71-dev
  libboost-exception1.71-dev libboost-fiber1.71-dev libboost-filesystem1.71-dev libboost-graph1.71-dev
  libboost-graph-parallel1.71-dev libboost-iostreams1.71-dev libboost-locale1.71-dev libboost-log1.71-dev
  libboost-math1.71-dev libboost-mpi1.71-dev libboost-mpi-python1.71-dev libboost-numpy1.71-dev
  libboost-program-options1.71-dev libboost-python1.71-dev libboost-random1.71-dev libboost-regex1.71-dev
  libboost-serialization1.71-dev libboost-stacktrace1.71-dev libboost-system1.71-dev libboost-test1.71-dev
  libboost-thread1.71-dev libboost-timer1.71-dev libboost-type-erasure1.71-dev libboost-wave1.71-dev libboost1.71-tools-dev
  libmpfrc++-dev libntl-dev
The following NEW packages will be installed:
  libboost-dev libboost1.71-dev libmysqlcppconn-dev libmysqlcppconn7v5

5. 从MySql官网下载安装ODBC驱动

到目前为止，虽然Qt已经能编译出odbc和mysql插件，但是Qt程序中仍不能通过ODBC去连接MySql，因为我们还没安装适配MySql的ODBC库。要理解ODBC只是一个开放数据库互连的标准，具体的实现要相应的数据库厂商提供。

MySql的ODBC驱动似乎并没有在ubuntu的远程仓库中，所以这才到MySql官网去找相应的库。
一共需要两个安装包：

mysql-community-client-plugins_8.0.25-1ubuntu20.04_amd64.deb

位于 https://dev.mysql.com/downloads/mysql/

mysql-connector-odbc_8.0.25-1ubuntu20.04_amd64.deb

位于 https://dev.mysql.com/downloads/connector/odbc/

将这两个.deb包放到机器上后，通过命令

sudo dpkg -i mysql-community-client-plugins_8.0.25-1ubuntu20.04_amd64.deb
sudo dpkg -i mysql-connector-odbc_8.0.25-1ubuntu20.04_amd64.deb

进行安装。
到此，MySql的ODBC驱动已经安装好，并且也配置好了。
使用 odbcinst -j 并检查相应的驱动配置文件/etc/odbcinst.ini，如果已经配置好，可以看到如下内容：

[MySQL ODBC 8.0 Unicode Driver]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
SETUP=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so
UsageCount=1

[MySQL ODBC 8.0 ANSI Driver]
Driver=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
SETUP=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so
UsageCount=1

接下来一起编写个简单的测试代码，查看效果吧：

    qDebug() << QSqlDatabase::drivers();
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    QString dsn = "Driver={MySQL ODBC 8.0 Unicode Driver};"; // Driver is now {SQL Server}
    dsn.append("Server=127.0.0.1,3306;"); // IP,Port
    dsn.append("Database=sys;");  // Schema
    dsn.append("Uid=root;");           // User
    dsn.append("Pwd=root999;");
    dsn.append("MULTI_HOST=1");
    db.setDatabaseName(dsn);
//    db.setHostName("127.0.0.1");
//    db.setPort(3306);
//    db.setUserName("root");
//    db.setPassword("root999");
//    db.setDatabaseName("sys");

    if (db.open()) {
        qDebug() << "opened successfully.";
    }
    qDebug() << db.lastError();