技术分享 | MySQL SHELL 是如何操作关系表的？

前言

我之前有一篇介绍在 MySQL SHELL 环境中如何对文档类数据进行操作的文章（MySQL 在NOSQL 领域冲锋陷阵），但是 MySQL SHELL 功能很多，除了可以操作文档类数据，也可以对关系表进行各种 DDL,DML 等操作。这里我就用几个简单例子来示范下如何用 MySQL SHELL 操作关系表。

此处引用的数据库示例基于官方的 SAMPLE DATABASE：WORLD，表结构以及数据可以自行下载。

MySQL SHELL 对关系型数据库的操作涉及到三个组件：

1. MySQL：传统 mysql，操作比较简单，除了写法有些差异外，基本上等同于 SQL 操作。

2. MySQL X：基于 X DEV 协议操作 mysql，其中包含很多类，除了可以操作文档数据，也可以操作关系表。

3. SHELL：包含了以上两个组件，可以随意切换，重点在于如何选择连接协议。

我们来依次看看各个组件对关系表的常用检索方式。

一、mysql 组件

连接数据库：mysql.get_session 或者 mysql.get_classic_session

可以用如下传统拼串方式连接数据库：

MySQL  Py > connection_url="mysql://root:root@localhost/world?socket=(/var/lib/mysql/official/mysql.sock)"MySQL  Py > ytt_cn1 = mysql.get_session(connection_url);MySQL  Py > ytt_cn1<ClassicSession:root@localhost>
复制

也可以用字典的方式连接数据库：

MySQL  Py > connection_url={"schema":"world","user":"root","password":"root","socket":"/var/lib/mysql/official/mysql.sock"}MySQL  Py > ytt_cn1=mysql.get_session(connection_url);MySQL  Py > ytt_cn1<ClassicSession:root@/var%2Flib%2Fmysql%2Fofficial%2Fmysql.sock>
复制

接下来可以用 ClassicSession 类提供的各种方法对关系表进行相关操作，所有的操作都可以直接用函数 run_sql 来执行：

对表 city 查询：

MySQL  Py > ytt_cn1.run_sql("table city limit 1")+----+-------+-------------+----------+------------+| ID | Name  | CountryCode | District | Population |+----+-------+-------------+----------+------------+|  1 | Kabul | AFG         | Kabol    |    1780000 |+----+-------+-------------+----------+------------+1 row in set (0.0005 sec)
复制

对表 city 插入：

MySQL  Py > ytt_cn1.run_sql("insert into city(name,countrycode,population,district) values ('test','CHN',1000000,'dd')")Query OK, 1 row affected (0.0079 sec)MySQL  Py > ytt_cn1.run_sql("select * from city where name ='test'")+------+------+-------------+----------+------------+| ID   | Name | CountryCode | District | Population |+------+------+-------------+----------+------------+| 4097 | test | CHN         | dd       |    1000000 |+------+------+-------------+----------+------------+1 row in set (0.0032 sec)
复制

对表 city 更新：

MySQL  Py > ytt_cn1.run_sql("update city set name='who know ?' where id=4097")Query OK, 1 row affected (0.0894 sec)Rows matched: 1  Changed: 1  Warnings: 0MySQL  Py > ytt_cn1.run_sql("select * from city where id=4097")+------+------------+-------------+----------+------------+| ID   | Name       | CountryCode | District | Population |+------+------------+-------------+----------+------------+| 4097 | who know ? | CHN         | dd       |    1000000 |+------+------------+-------------+----------+------------+1 row in set (0.0005 sec)
复制

对表 city 删除：

MySQL  Py > ytt_cn1.run_sql("delete from city where id=4097")Query OK, 1 row affected (0.0739 sec)MySQL  Py > ytt_cn1.run_sql("select * from city where id=4097")Empty set (0.0004 sec)
复制

开启一个事务块：

MySQL  Py > ytt_cn1.start_transaction();Query OK, 0 rows affected (0.0003 sec)MySQL  Py > ytt_cn1.run_sql("delete from city where id =1")Query OK, 1 row affected (0.0006 sec)MySQL  Py > ytt_cn1.rollback();Query OK, 0 rows affected (0.2070 sec)MySQL  Py > ytt_cn1.run_sql("select * from city where id = 1")+----+-------+-------------+----------+------------+| ID | Name  | CountryCode | District | Population |+----+-------+-------------+----------+------------+|  1 | Kabul | AFG         | Kabol    |    1780000 |+----+-------+-------------+----------+------------+1 row in set (0.0004 sec)
复制

关闭连接：

MySQL  Py > ytt_cn1.close();MySQL  Py > ytt_cn1<ClassicSession:disconnected>
复制

二、mysqlx 组件

MySQL X 组件包含了很多类，下面我来举几个常用的例子：

依然是先连接数据库 world：X 协议端口 33060 或者 X SOCKET（用 mysqlx.get_session 方法）。

MySQL  Py > connection_urlx="mysqlx://root:root@localhost/world?socket=(/var/lib/mysql/official/mysqlx.sock)"MySQL  Py > ytt_cnx1=mysqlx.get_session(connection_urlx);
复制

比如找出人口小于800的城市并且列出对应的国家名字：

SQL： select a.id,a.name,b.name country_name, a.population from city a join country b on (a.countrycode = b.code and a.population < 800);
复制

2.1 SQLRESULT 类：类似于 mysql 游标用法

MySQL  Py > sql1="select a.id,a.name,b.name country_name, a.population from city a join country b on (a.countrycode = b.code and a.population < 800)"MySQL  Py > sql_result1=ytt_cnx1.run_sql(sql1)
复制

获取前两行：默认不带字段名

MySQL  Py > sql_result1.fetch_one()[    62,    "The Valley",    "Anguilla",    595]MySQL  Py > sql_result1.fetch_one()[    1791,    "Flying Fish Cove",    "Christmas Island",    700]
复制

获取带字段名的记录：

MySQL  Py > sql_result1.fetch_one_object();{    "country_name": "Cocos (Keeling) Islands",     "id": 2316,     "name": "Bantam",     "population": 503}
复制

一次性获取剩余的行：

MySQL  Py > sql_result1.fetch_all()[    [        2317,        "West Island",        "Cocos (Keeling) Islands",        167    ],     [        2728,        "Yaren",        "Nauru",        559    ],     [        2805,        "Alofi",        "Niue",        682    ],     [        2912,        "Adamstown",        "Pitcairn",        42    ],     [        3333,        "Fakaofo",        "Tokelau",        300    ],     [        3538,        "Città del Vaticano",        "Holy See (Vatican City State)",        455    ]]
复制

2.2 SqlExecute 类：类似于 prepare 语句用法

比如把之前的人口判断条件替换为绑定变量（？或者变量(:a)），这样可以方便多个条件一起查询。

 MySQL  Py > sql2="select a.id,a.name,b.name country_name, a.population from city a join country b on (a.countrycode = b.code and a.population < ?)" MySQL  Py > sql_result2=ytt_cnx1.sql(sql2);
复制

给定两个不同的人口条件：

MySQL  Py > a=800MySQL  Py > b=500
复制

绑定变量执行结果：

MySQL  Py > sql_result2.bind(a)+------+--------------------+-------------------------------+------------+| id   | name               | country_name                  | population |+------+--------------------+-------------------------------+------------+|   62 | The Valley         | Anguilla                      |        595 || 1791 | Flying Fish Cove   | Christmas Island              |        700 || 2316 | Bantam             | Cocos (Keeling) Islands       |        503 || 2317 | West Island        | Cocos (Keeling) Islands       |        167 || 2728 | Yaren              | Nauru                         |        559 || 2805 | Alofi              | Niue                          |        682 || 2912 | Adamstown          | Pitcairn                      |         42 || 3333 | Fakaofo            | Tokelau                       |        300 || 3538 | Città del Vaticano | Holy See (Vatican City State) |        455 |+------+--------------------+-------------------------------+------------+9 rows in set (0.0022 sec)MySQL  Py > sql_result2.bind(b)+------+--------------------+-------------------------------+------------+| id   | name               | country_name                  | population |+------+--------------------+-------------------------------+------------+| 2317 | West Island        | Cocos (Keeling) Islands       |        167 || 2912 | Adamstown          | Pitcairn                      |         42 || 3333 | Fakaofo            | Tokelau                       |        300 || 3538 | Città del Vaticano | Holy See (Vatican City State) |        455 |+------+--------------------+-------------------------------+------------+4 rows in set (0.0023 sec)
复制

2.3 Table 类：获取当前连接数据库下单张表，可以对这张表进行任何 DML 操作。（获取 Table 类之前，得先获取 Schema 类）

MySQL  Py > ytt_schema1=ytt_cnx1.get_schema('world')MySQL  Py > ytt_tbname1=ytt_schema1.get_table('city');
复制

查找人口少于 800 的记录：

MySQL  Py > ytt_tbname1.select().where("population<800")+------+--------------------+-------------+-------------+------------+| ID   | Name               | CountryCode | District    | Population |+------+--------------------+-------------+-------------+------------+|   62 | The Valley         | AIA         | –           |        595 || 1791 | Flying Fish Cove   | CXR         | –           |        700 || 2316 | Bantam             | CCK         | Home Island |        503 || 2317 | West Island        | CCK         | West Island |        167 || 2728 | Yaren              | NRU         | –           |        559 || 2805 | Alofi              | NIU         | –           |        682 || 2912 | Adamstown          | PCN         | –           |         42 || 3333 | Fakaofo            | TKL         | Fakaofo     |        300 || 3538 | Città del Vaticano | VAT         | –           |        455 |+------+--------------------+-------------+-------------+------------+9 rows in set (0.0024 sec)
复制

还可以继续排序以及限制记录数输出：

MySQL  Py > ytt_tbname1.select().where("population<800").order_by("population desc ").limit(3)+------+------------------+-------------+----------+------------+| ID   | Name             | CountryCode | District | Population |+------+------------------+-------------+----------+------------+| 1791 | Flying Fish Cove | CXR         | –        |        700 || 2805 | Alofi            | NIU         | –        |        682 ||   62 | The Valley       | AIA         | –        |        595 |+------+------------------+-------------+----------+------------+3 rows in set (0.0024 sec)
复制

2.4 Table 类包含几个子类：TableSelect、TableInsert、TableUpdate、TableDelete。

1）TableSelect：保存查询结果

之前查找人口小于 800 的记录结果即为 TableSelect，可以基于此类来后续操作。

MySQL  Py > tbselect1=ytt_tbname1.select().where("population<800")
复制

只拿出部分字段：

MySQL  Py > tbselect1.select("[id,name]").order_by("population desc").limit(2);+----------------------------+| JSON_ARRAY(`id`,`name`)    |+----------------------------+| [1791, "Flying Fish Cove"] || [2805, "Alofi"]            |+----------------------------+2 rows in set (0.0031 sec)
复制

2）TableInsert：执行插入语句

插入一行：

MySQL  Py > ytt_tbname1.count()4081MySQL  Py > tbinsert1=ytt_tbname1.insert(["name","population","countrycode","district"]).values('test',1000000,'CHN','dd');MySQL  Py > tbinsert1.execute();Query OK, 1 item affected (0.0054 sec)MySQL  Py > ytt_tbname1.count()4082
复制

插入多行：有两种方法。

多 VALUES 形式：

MySQL  Py > tbinsert1=ytt_tbname1.insert(["name","population","countrycode","district"]).values('test',1000000,'CHN','dd').values('test',1000000,'CHN','dd');MySQL  Py > tbinsert1.execute()Query OK, 2 items affected (0.0325 sec)Records: 2  Duplicates: 0  Warnings: 0MySQL  Py > MySQL  Py > ytt_tbname1.count()4084
复制

多次执行或者包含在事务块里：

MySQL  Py > ytt_cnx1.start_transaction();Query OK, 0 rows affected (0.0004 sec)MySQL  Py > tbinsert1=ytt_tbname1.insert(["name","population","countrycode","district"]).values('test',1000000,'CHN','dd');MySQL  Py > tbinsert1Query OK, 1 item affected (0.0008 sec)MySQL  Py > tbinsert1Query OK, 1 item affected (0.0006 sec)MySQL  Py > tbinsert1Query OK, 1 item affected (0.0008 sec)MySQL  Py > tbinsert1Query OK, 1 item affected (0.0006 sec)MySQL  Py > ytt_cnx1.commit();Query OK, 0 rows affected (0.2737 sec)MySQL  Py > ytt_tbname1.count()4088
复制

3）TableUpdate：执行更新语句

MySQL  Py > tbupdate1=ytt_tbname1.update().set('district','nothing').where("name='test'")MySQL  Py > tbupdate1Query OK, 0 items affected (0.0048 sec)Rows matched: 9  Changed: 9  Warnings: 0
复制

4）TableDelete：执行删除语句

MySQL  Py > tbdelete1=ytt_tbname1.delete().where("district='nothing'");MySQL  Py > tbdelete1Query OK, 9 items affected (0.0112 sec)MySQL  Py > ytt_tbname1.count()4079
复制

三、SHELL 组件

SHELL 组件可以在 MySQL 和 MySQL X 间随意切换，并且连接后，包含了一个默认数据库类 “db” ，db 等价于 ytt_cnx1.get_current_schema()

MySQL  Py > ytt_cnx_shell1=shell.connect(connection_urlx)Creating an X protocol session to 'root@localhost/world'Fetching schema names for autocompletion... Press ^C to stop.Your MySQL connection id is 10 (X protocol)Server version: 8.0.23 MySQL Community Server - GPLDefault schema `world` accessible through db
复制

依然还是操作表 city，

 MySQL  localhost+ ssl  world  Py > ytt_tbname2=db.get_table("city") MySQL  localhost+ ssl  world  Py > ytt_tbname2<Table:city>
复制

之后的操作和之前 mysqlx 的一样。

MySQL  localhost+ ssl  world  Py > ytt_tbname2.select(['id','name']).where("population<800").order_by("id desc").limit(3);+------+--------------------+| id   | name               |+------+--------------------+| 3538 | Città del Vaticano || 3333 | Fakaofo            || 2912 | Adamstown          |+------+--------------------+3 rows in set (0.0011 sec)
复制

所以如果用 MySQL SHELL 来操作 mysql 关系表，推荐用 SHELL 组件的方式，非常灵活。