mysql

配置mysql使用docker

这里使用docker安装了，比较简洁，不想使用了直接就可以把容器删掉，首先获取下镜像，如下命令

docker pull container-registry.oracle.com/mysql/community-server

这里直接默认使用最新版本的mysql了

然后启动容器，如下命令，-P是端口映射，因为我们需要外面连接，因此加上，要不然不好连接

docker run --name=mysql --restart on-failure -d  -P container-registry.oracle.com/mysql/community-server:latest

登录mysql是需要密码的，因此我们需要找到密码，如下命令，使用docker

docker logs mysql 2>&1 | grep GENERATED

然后登入mysql修改下密码就行了，如下面命令

docker exec -it mysql mysql -uroot -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

使用docker看下端口映射的情况，如下命令，8.0以上会有个33060是针对x插件的

docker ps

编程使用

这里来学习下各种db的编程使用，首先是耳熟能详的mysql，看了下官方文档，说有个新的c++ api叫Connector/C++，说是很屌的样子，支持sql语句和文档存储，那就从他开始吧，这里先搞c++了，本来想使用bazel来做包管理器呢，但是搜了下，bazel还没有支持这个库，我就使用vcpkg了。

首先使用vcpkg初始化下

vcpkg new --application
vcpkg add port fmt
vcpkg add port mysql-connector-cpp

具体如何配置可以看之前的博客 vcpkg 使用

我以为vcpkg已经非常成熟了，我直接加上依赖它就能自动下载完成，并把依赖搞完，我直接在cmake里面加上这个库就行了，但是发现有很多依赖是需要自己添加到cmake文件里面的，当然依赖它是自己下载完的，就是需要自己把其写道cmake里面，有点麻烦的，我下面写出cmake里面需要的依赖，还是比较麻烦的

cmake_minimum_required(VERSION 3.5.0)
project(mysqlConnectorCpp)
find_package(unofficial-mysql-connector-cpp CONFIG REQUIRED)
find_package(fmt CONFIG REQUIRED)
find_package(OpenSSL REQUIRED)
find_package(protobuf CONFIG REQUIRED)
find_package(ZLIB REQUIRED)
find_package(lz4 CONFIG REQUIRED)
find_package(zstd CONFIG REQUIRED)
add_executable(mysqlConnectorCpp main.cpp)
target_link_libraries(mysqlConnectorCpp PRIVATE unofficial::mysql-connector-cpp::connector)
target_link_libraries(mysqlConnectorCpp PRIVATE fmt::fmt)
target_link_libraries(mysqlConnectorCpp PRIVATE OpenSSL::SSL)
target_link_libraries(mysqlConnectorCpp PRIVATE OpenSSL::Crypto)
target_link_libraries(mysqlConnectorCpp PRIVATE protobuf::libprotobuf)
target_link_libraries(mysqlConnectorCpp PRIVATE ZLIB::ZLIB)
target_link_libraries(mysqlConnectorCpp PRIVATE lz4::lz4)
target_link_libraries(mysqlConnectorCpp PRIVATE zstd::libzstd)
target_link_libraries(mysqlConnectorCpp PRIVATE -lresolv -lutil)

把这些依赖搞完后，发现可以编译了，但是遇见了未知错误，如下，我认为是写的url是不对的。

Creating session on mysqlx://root@172.29.34.32:32770 ...
ERROR: CDK Error: unexpected message

找到问题所在了，这个使用connector的话，不能使用mysql的3306开的服务端口，需要使用专门的端口，这个也许是8.0以后单独开启的监听端口，因为我是docker安装的mysql，因此我们看下mysql的日志使用命令docker logs <container name>，我们可以看到X Plugin开启的端口是33060，因此我们的代码需要设置的端口是33060，而不是3306，因为使用的docker，因此需要看下主机映射的端口是哪个，然后再去连接就行了，大家去找下面的字眼就行了

2025-01-24T02:24:56.607449Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

解决好这个问题就可以运行了，运行结果如下(port是我主机映射的端口):

Creating session on mysqlx://root@172.29.34.32:32769 ...
Session accepted, creating collection...
Inserting documents...
- added doc with id: 00006792f9f8000000000000000a
- added doc with id: 00006792f9f8000000000000000b
- added doc with id: 00006792f9f8000000000000000c
- added doc
Fetching documents...
doc#0: {"_id": "00006792f9f8000000000000000b", "age": 2, "name": "bar", "toys": ["car", "ball"]}field `_id`: 00006792f9f8000000000000000bfield `age`: 2field `name`: barfield `toys`: ["car", "ball"]name: bar
- toys:carballdoc#1: {"_id": "00006792f9f8000000000000000c", "age": 3, "date": {"day": 20, "month": "Apr"}, "name": "baz"}field `_id`: 00006792f9f8000000000000000cfield `age`: 3field `date`: {"day": 20, "month": "Apr"}field `name`: bazname: baz
- date fielddate `day`: 20date `month`: Aprmonth: Aprday: 20Done!

除了会有输出外，会在服务器上建立一个数据库名为test，并在test下面创建个名为c1的表，里面会添加四条记录，如下图

代码我也放出来了，就是官网的代码

#include <fmt/core.h>
#include <mysqlx/xdevapi.h>#include <iostream>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://root@172.29.34.32:32769");cout << "Creating session on " << url << " ..." << endl;Session sess(url);{RowResult res = sess.sql("show variables like 'version'").execute();std::stringstream version;version << res.fetchOne().get(1).get<string>();int major_version;version >> major_version;if (major_version < 8) {cout << "Can work only with MySQL Server 8 or later" << endl;cout << "Done!" << endl;return 0;}}cout << "Session accepted, creating collection..." << endl;Schema sch = sess.createSchema("test", true);Collection coll = sch.createCollection("c1", true);cout << "Inserting documents..." << endl;coll.remove("true").execute();{Result add;add = coll.add(R"({ "name": "foo", "age": 1 })").execute();std::vector<string> ids = add.getGeneratedIds();cout << "- added doc with id: " << ids[0] << endl;add =coll.add(R"({ "name": "bar", "age": 2, "toys": [ "car", "ball" ] })").execute();ids = add.getGeneratedIds();if (ids.size() != 0)cout << "- added doc with id: " << ids[0] << endl;elsecout << "- added doc" << endl;add = coll.add(R"({"name": "baz","age": 3,"date": { "day": 20, "month": "Apr" }})").execute();ids = add.getGeneratedIds();if (ids.size() != 0)cout << "- added doc with id: " << ids[0] << endl;elsecout << "- added doc" << endl;add = coll.add(R"({ "_id": "myuuid-1", "name": "foo", "age": 7 })").execute();ids = add.getGeneratedIds();if (ids.size() != 0)cout << "- added doc with id: " << ids[0] << endl;elsecout << "- added doc" << 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;}
}