MySql

安装

MySQL Community Downloads

Ubuntu 系统若要安装最新版的 MySQL，需要先从官网下载 APT 配置，安装配置包，更新软件源，最后使用以下命令安装 MySQL Server:

sudo apt install mysql-server
sudo systemctl start mysql.service
systemctl status mysql.service

/etc/mysql/conf.d/mysql.cnf 中的默认配置。

[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
log-error       = /var/log/mysql/error.log

卸载清理 MySQL:

sudo apt purge mysql-*
sudo rm -rf /etc/mysql/ /var/lib/mysql
sudo apt autoremove
sudo apt autoclean

参考博客: https://blog.csdn.net/weixin_44129085/article/details/104481986

控制台命令

登录 MysQL 数据库:

mysql -u root -p
mysql -h 127.0.0.1 -P 3306 -u root -p

1. SHOW DATABASES：查看MySQL服务器中的所有数据库。执行语句如下：

   SHOW DATABASES;

2. USE：使用该命令可以选择需要操作的数据库。语句如下：

   USE 数据库名;

3. SHOW TABLES: 查看数据库中的所有表。语句如下：

   SHOW TABLES;

4. SHOW COLUMNS: 查看表的所有字段信息。语句如下：

   SHOW COLUMNS FROM 表名;

5. CREATE DATABASE: 创建新的数据库，语句如下：

   CREATE DATABASE 数据库名;

6. DROP DATABASE: 删除数据库，语句如下：

   DROP DATABASE 数据库名;

7. CREATE TABLE: 创建表，语句如下：

   CREATE TABLE 表名 (字段1 类型,字段2 类型);

8. ALTER TABLE: 修改表的字段定义，语句如下：

   ALTER TABLE 表名 MODIFY 字段名 类型；

9. DROP TABLE：删除表，语句如下：

   DROP TABLE 表名;

10. INSERT：插入一条记录，语句如下：

    INSERT INTO 表名 (字段1 ,字段2) VALUES (值1， 值2);

11. SELECT：查询表中的记录，语句如下：

    SELECT * FROM 表名;

12. DELETE：删除满足条件的记录，语句如下：

    DELETE FROM 表名 WHERE 条件;

13. UPDATE：更新满足条件记录，语句如下：

    UPDATE 表名 SET 字段1=值1，字段2=值2 WHERE 条件;

14. TRUNCATE TABLE：清空表的所有记录，语句如下：

    TRUNCATE TABLE 表名;

15. GRANT：给用户授权，语句如下：

    GRANT 权限 ON 数据库名.表名 TO 用户名@主机；

16. DENY：拒绝用户访问，语句如下：

    DENY 权限 ON 数据库名.表名 TO 用户名@主机；

17. BACKUP：备份数据库，语句如下：

    mysqldump -u用户名 -p密码 -h主机 数据库名 > 数据库名_备份文件名.sql

18. RESTORE：恢复数据库，语句如下：

    mysql -u用户名 -p密码 -h主机 数据库名 < 数据库名_备份文件名.sql

19. FLUSH TABLE：清楚某个表的缓存，语句如下：

    FLUSH TABLE 表名;

20. FLUSH HOST：清除主机的缓存，语句如下：

    FLUSH HOST;

21. FLUSH TABLES WITH READ LOCK：为表上锁，防止表被写操作，语句如下：

    FLUSH TABLES WITH READ LOCK;

22. FLUSH PRIVILEGES：清除MySQL权限缓存，语句如下：

    FLUSH PRIVILEGES;

23. SHOW ENGINE：显示MySQL内部引擎状态，语句如下：

    SHOW ENGINE 引擎名称

24. SHOW VARIABLES：显示MySQL系统变量，语句如下：

    SHOW VARIABLES;

25. SHOW STATUS：显示MySQL的执行情况，语句如下：

    SHOW STATUS;

26. MySQL Client Programs

27. MySQL SQL Statements

Connector/C++

MySQL Connector/C++ 是 MySQL 官方提供的一个 C++ 语言的数据库连接库，用于在 C++ 程序中与 MySQL 数据库进行交互。下面是一个简单的使用示例，展示如何连接到 MySQL 数据库、执行查询并处理结果。

准备数据

创建用户:

jerry@jerry-Standard-PC-Q35-ICH9-2009:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create user 'test' identified by 'dbtu2017';
Query OK, 0 rows affected (0.04 sec)

mysql> create database test character set 'utf8mb4' collate 'utf8mb4_unicode_ci';
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> grant all privileges on test.* to 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> exit;
Bye

jerry@jerry-Standard-PC-Q35-ICH9-2009:~$ mysql -u test -p test
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| performance_schema |
| test               |
+--------------------+
3 rows in set (0.01 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.01 sec)

mysql> create table students(id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT);
Query OK, 0 rows affected (0.04 sec)

mysql> 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| students       |
+----------------+
1 row in set (0.01 sec)

mysql> 
mysql> insert into students(id, name, age) values(1, 'Tom', 18),(2, 'Jerry', 20),(3, 'Emily', 19);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from students;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Tom   |   18 |
|  2 | Jerry |   20 |
|  3 | Emily |   19 |
+----+-------+------+
3 rows in set (0.00 sec)

mysql> exit
Bye

legacy JDBC API

app.cc:

// #include <jdbc/mysql_driver.h>
// #include <jdbc/mysql_connection.h>
#include <mysql/jdbc.h>

int main() {
  try {
    sql::mysql::MySQL_Driver *driver;
    sql::Connection *con;
    sql::Statement *stmt;
    sql::ResultSet *res;

    // 创建 MySQL 驱动对象
    driver = sql::mysql::get_mysql_driver_instance();

    // 建立连接
    con = driver->connect("tcp://127.0.0.1:3306", "test", "dbtu2017");

    // 选择数据库
    con->setSchema("test");

    // 创建查询语句
    stmt = con->createStatement();
    res = stmt->executeQuery("select * from students");

    // 处理查询结果
    while(res->next()) {
      std::cout << "ID: " << res->getInt("id")
                << ", Name: " << res->getString("name")
                << ", Age: " << res->getInt("age")
                << std::endl;
    }

    // 关闭连接
    delete res;
    delete stmt;
    delete con;
  } catch (const sql::SQLException &err) {
    std::cout << "ERROR: " << err.what() << std::endl;
    return 1;
  } catch (std::exception &ex) {
    std::cout << "STD EXCEPTION: " << ex.what() << std::endl;
    return 1;
  } catch (const char *ex) {
    std::cout << "EXCEPTION: " << ex << std::endl;
    return 1;
  }
  return 0;
}

Makefile:

# Using the Connector/C++ Dynamic Library
# MYSQL_CONCPP_DIR = /home/jerry/Downloads/mysql-connector-c++-8.0.33-linux-glibc2.28-x86-64bit
# CPPFLAGS = -I $(MYSQL_CONCPP_DIR)/include -L $(MYSQL_CONCPP_DIR)/lib64
# LDLIBS = -lmysqlcppconn

# Using the Connector/C++ Static Library
MYSQL_CONCPP_DIR = /home/jerry/Downloads/libmysqlcppconn-dev_8.0.33-1ubuntu20.04_amd64
# MYSQL_CONCPP_DIR = /home/jerry/Downloads/mysql-connector-c++-8.0.33-linux-glibc2.28-x86-64bit
CPPFLAGS = -DSTATIC_CONCPP -I $(MYSQL_CONCPP_DIR)/include
LDLIBS = $(MYSQL_CONCPP_DIR)/lib64/libmysqlcppconn-static.a -lssl -lcrypto -lpthread -lresolv -ldl -lstdc++

all: app
app: app.cc
clean:
    -@rm -f app

动态链接使用的是 mysql-connector-c++-8.0.33-linux-glibc2.28-x86-64bit.tar.gz。 编译后运行可能会提示找不到 libmysqlcppconn.so.9，这是因为链接用的动态库并不在运行时的环境变量中，执行命令 export LD_LIBRARY_PATH=/home/jerry/Downloads/mysql-connector-c++-8.0.33-linux-glibc2.28-x86-64bit/lib64 即可。

这个包里面也有静态链接库，但是编译的时候总是报链接错误。这可能是由于 C++ 标准库的版本不匹配或者链接的顺序不正确所致。(比如 libmysqlcppconn-static.a 是用 gcc-12 版本编译的，而我的编译环境是 gcc 9.4.0，使用不了高版本的静态库。)

后来我专门下载了 Ubuntu 版本的 libmysqlcppconn-dev_8.0.33-1ubuntu20.04_amd64.deb，使用这个 DEB Package 里面的静态链接库编译链接运行都很正常。

X DevAPI

app.cc:

#include <iostream>
#include <mysqlx/xdevapi.h>

using ::std::cout;
using ::std::endl;
using namespace ::mysqlx;


int main(int argc, const char* argv[])
try {

  const char   *url = (argc > 1 ? argv[1] : "mysqlx://test:dbtu2017@127.0.0.1");

  cout << "Creating session on " << url
       << " ..." << endl;

  Session sess(url);

  cout <<"Session accepted, creating collection..." <<endl;

  Schema sch= sess.getSchema("test");
  Collection coll= sch.createCollection("c1", true);

  cout <<"Inserting documents..." <<endl;

  coll.remove("true").execute();

  {
    DbDoc doc(R"({ "name": "foo", "age": 1 })");

    Result add =
      coll.add(doc)
          .add(R"({ "name": "bar", "age": 2, "toys": [ "car", "ball" ] })")
          .add(R"({ "name": "bar", "age": 2, "toys": [ "car", "ball" ] })")
          .add(R"({
                 "name": "baz",
                  "age": 3,
                 "date": { "day": 20, "month": "Apr" }
              })")
          .add(R"({ "_id": "myuuid-1", "name": "foo", "age": 7 })")
          .execute();

    std::list<string> ids = add.getGeneratedIds();
    for (string id : ids)
      cout <<"- added doc with id: " << id <<endl;
  }

  cout <<"Fetching documents..." <<endl;

  DocResult docs = coll.find("age > 1 and name like 'ba%'").execute();

  int i = 0;
  for (DbDoc doc : docs)
  {
    cout <<"doc#" <<i++ <<": " <<doc <<endl;

    for (Field fld : doc)
    {
      cout << " field `" << fld << "`: " <<doc[fld] << endl;
    }

    string name = doc["name"];
    cout << " name: " << name << endl;

    if (doc.hasField("date") && Value::DOCUMENT == doc.fieldType("date"))
    {
      cout << "- date field" << endl;
      DbDoc date = doc["date"];
      for (Field fld : date)
      {
        cout << "  date `" << fld << "`: " << date[fld] << endl;
      }
      string month = doc["date"]["month"];
      int day = date["day"];
      cout << "  month: " << month << endl;
      cout << "  day: " << day << endl;
    }

    if (doc.hasField("toys") && Value::ARRAY == doc.fieldType("toys"))
    {
      cout << "- toys:" << endl;
      for (auto toy : doc["toys"])
      {
        cout << "  " << toy << endl;
      }
    }

    cout << endl;
  }
  cout <<"Done!" <<endl;
}
catch (const mysqlx::Error &err)
{
  cout <<"ERROR: " <<err <<endl;
  return 1;
}
catch (std::exception &ex)
{
  cout <<"STD EXCEPTION: " <<ex.what() <<endl;
  return 1;
}
catch (const char *ex)
{
  cout <<"EXCEPTION: " <<ex <<endl;
  return 1;
}

Makefile:

# Using the Connector/C++ Dynamic Library
# MYSQL_CONCPP_DIR = /home/jerry/Downloads/mysql-connector-c++-8.0.33-linux-glibc2.28-x86-64bit
# CPPFLAGS = -I $(MYSQL_CONCPP_DIR)/include -L $(MYSQL_CONCPP_DIR)/lib64
# LDLIBS = -lmysqlcppconn8
# CXXFLAGS = -std=c++17

# Using the Connector/C++ Static Library
MYSQL_CONCPP_DIR = /home/jerry/Downloads/libmysqlcppconn-dev_8.0.33-1ubuntu20.04_amd64
# MYSQL_CONCPP_DIR = /home/jerry/Downloads/mysql-connector-c++-8.0.33-linux-glibc2.28-x86-64bit
CPPFLAGS = -DSTATIC_CONCPP -I $(MYSQL_CONCPP_DIR)/include
LDLIBS = $(MYSQL_CONCPP_DIR)/lib64/libmysqlcppconn8-static.a -lssl -lcrypto -lpthread -lresolv -ldl -lstdc++
CXXFLAGS = -std=c++17

all: app
app: app.cc
clean:
    -@rm -f app

使用静态库时遇到了和上面一样的问题，通过下载跟本地环境同系统版本的开发库解决。

• MySQL Connector/C++
• MySQL Connector/C++

参考资料

MySQL 8.0 Reference Manual