mysqlクライアントプログラムによるSQLコマンドとMySQL ShellでのJavaScriptベースのコマンドの比較

MySQL

2016年10月1日 MySQL 5.7.15 + MySQL Shell 1.0.5にて検証のうえ、コマンドおよび一般ログの出力内容更新済み

MySQL 5.7.12のリリースと同時に登場したMySQL Document Storeとそれに関連するX Dev APIやMySQL Shellについて、既存のSQLとの関係をMySQL 5.7.125.7.15にて一般ログの出力を見ながら簡単に調べてみました。

初期設定等はここ↓ここに詳しく書いてあります。
MYSQLXとMYSQL SHELL初期設定と基本動作確認
基本操作はこちらにも書かれています。
MySQL Shell / X DevAPI / X Protocol - @tmtms のメモ

なお一般ログの出力からは日時、クライアントのIDおよびQueryの文字は削除してあります。

2016-05-18T02:50:58.045435Z   13 Query	SELECT DATABASE()
↓
SELECT DATABASE()

カレントデータベースの変更

mysqlクライアントからのSQLMySQL Shellのコマンド
コマンド
mysql> USE x_sample;
Database changed
mysql-js> db = session.getSchema('x_sample');
一般ログ
SELECT DATABASE()
Init DB x_sample
show databases like 'x_sample'
SHOW TABLES FROM `x_sample`
SELECT C.table_name AS name, IF(ANY_VALUE(T.table_type)='VIEW', 'VIEW', IF(COUNT(*) = COUNT(CASE WHEN (column_name = 'doc' AND data_type = 'json') THEN 1 ELSE NULL END) + COUNT(CASE WHEN (column_name = '_id' AND generation_expression = 'json_unquote(json_extract(`doc`,''$._id''))') THEN 1 ELSE NULL END) + COUNT(CASE WHEN (column_name != '_id' AND generation_expression RLIKE '^(json_unquote.(.)?json_extract.(.`doc`,''.$.(...[^[:space:][...]]+)+''.).{1,2}$') THEN 1 ELSE NULL END), 'COLLECTION', 'TABLE')) AS type FROM information_schema.columns AS C LEFT JOIN information_schema.tables AS T USING (table_name)WHERE C.table_schema = 'x_sample' GROUP BY C.table_name ORDER BY C.table_name

テーブルおよびコレクションの作成

mysqlクライアントからのSQLMySQL Shellのコマンド
コマンド
mysql> CREATE TABLE `x_sample`.`tbl_sql` (id SERIAL, doc JSON) CHARSET utf8mb4 ENGINE=InnoDB;
Query OK, 0 rows affected (0.16 sec)
mysql-js> db.createCollection('tbl_x');
一般ログ
CREATE TABLE `x_sample`.`tbl_sql` (id SERIAL, doc JSON) CHARSET utf8mb4 ENGINE=InnoDB
CREATE TABLE `x_sample`.`tbl_x` (doc JSON,_id VARCHAR(32) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(doc, '$._id'))) STORED PRIMARY KEY) CHARSET utf8mb4 ENGINE=InnoDB

テーブル一覧の表示





mysqlクライアントからのSQLMySQL Shellのテーブル一覧取得MySQL Shellのコレクション一覧取得
コマンド
mysql> SHOW TABLES;

                                          • +
Tables_in_x_sample
                                          • +
tbl_sql
tbl_x
                                          • +

2 rows in set (0.00 sec)
mysql-js> db.getTables();
[

]
mysql-js> db.getCollections();
[

]
一般ログ
Query SHOW TABLES
SHOW TABLES FROM `x_sample`
SELECT C.table_name AS name, IF(ANY_VALUE(T.table_type)='VIEW', 'VIEW', IF(COUNT(*) = COUNT(CASE WHEN (column_name = 'doc' AND data_type = 'json') THEN 1 ELSE NULL END) + COUNT(CASE WHEN (column_name = '_id' AND generation_expression = 'json_unquote(json_extract(`doc`,''$._id''))') THEN 1 ELSE NULL END) + COUNT(CASE WHEN (column_name != '_id' AND generation_expression RLIKE '^(json_unquote.(.)?json_extract.(.`doc`,''.$.(...[^[:space:][...]]+)+''.).{1,2}$') THEN 1 ELSE NULL END), 'COLLECTION', 'TABLE')) AS type FROM information_schema.columns AS C LEFT JOIN information_schema.tables AS T USING (table_name)WHERE C.table_schema = 'x_sample' GROUP BY C.table_name ORDER BY C.table_name
SHOW TABLES FROM `x_sample`
SELECT C.table_name AS name, IF(ANY_VALUE(T.table_type)='VIEW', 'VIEW', IF(COUNT(*) = COUNT(CASE WHEN (column_name = 'doc' AND data_type = 'json') THEN 1 ELSE NULL END) + COUNT(CASE WHEN (column_name = '_id' AND generation_expression = 'json_unquote(json_extract(`doc`,''$._id''))') THEN 1 ELSE NULL END) + COUNT(CASE WHEN (column_name != '_id' AND generation_expression RLIKE '^(json_unquote.(.)?json_extract.(.`doc`,''.$.(...[^[:space:][...]]+)+''.).{1,2}$') THEN 1 ELSE NULL END), 'COLLECTION', 'TABLE')) AS type FROM information_schema.columns AS C LEFT JOIN information_schema.tables AS T USING (table_name)WHERE C.table_schema = 'x_sample' GROUP BY C.table_name ORDER BY C.table_name

データの追加


mysqlクライアントからのSQLMySQL ShellのCRUDでの通常のテーブルへのデータの追加MySQL ShellのCRUDでのコレクションへのデータの追加
コマンド
mysql> INSERT INTO tbl_sql(doc) VALUES('{"id": 1, "name": "Mike", "Team": "Products"}');
Query OK, 1 row affected (0.06 sec)
mysql-js> db.tbl_sql.insert(['doc']).values('{"id": 2, "name": "Joe", "Team": "Sales", "Title": "VP"}').values('{"id": 3, "name": "Tomas", "Team": "Development"}').execute();
Query OK, 1 item affected (0.07 sec)
mysql-js> db.tbl_x.add({"id": 1, "name": "Mike", "Team": "Products"}).execute();
Query OK, 1 item affected (0.10 sec)

mysql-js> db.tbl_x.add([{"id": 2, "name": "Joe", "Team": "Sales", "Title": "VP"}, {"id": 3, "name": "Tomas", "Team": "Development"}]).execute();
Query OK, 1 item affected (0.07 sec)

mysql-js> db.tbl_x.add([{"id": 4, "name": "Luis", "Team": "Development"}, {"id": 5, "name": "Rebeca", "Team": "Marketing"}]).execute();
Query OK, 1 item affected (0.08 sec)
一般ログ
INSERT INTO tbl_sql(doc) VALUES('{"id": 1, "name": "Mike", "Team": "Products"}')
INSERT INTO `x_sample`.`tbl_sql` (`doc`) VALUES ('{\"id\": 2, \"name\": \"Joe\", \"Team\": \"Sales\", \"Title\": \"VP\"}'),('{\"id\": 3, \"name\": \"Tomas\", \"Team\": \"Development\"}')


INSERT INTO `x_sample`.`tbl_x` (doc) VALUES ('{\"Team\":\"Products\",\"_id\":\"2ae1f178a81ce611620d5a37036c1421\",\"id\":1,\"name\":\"Mike\"}')
INSERT INTO `x_sample`.`tbl_x` (doc) VALUES ('{\"Team\":\"Sales\",\"Title\":\"VP\",\"_id\":\"fc451c81a81ce611620d5a37036c1421\",\"id\":2,\"name\":\"Joe\"}'),('{\"Team\":\"Development\",\"_id\":\"28471c81a81ce611620d5a37036c1421\",\"id\":3,\"name\":\"Tomas\"}')
INSERT INTO `x_sample`.`tbl_x` (doc) VALUES ('{\"Team\":\"Development\",\"_id\":\"7c7bed8ba81ce611620d5a37036c1421\",\"id\":4,\"name\":\"Luis\"}'),('{\"Team\":\"Marketing\",\"_id\":\"0c7ded8ba81ce611620d5a37036c1421\",\"id\":5,\"name\":\"Rebeca\"}')

複数ドキュメントの追加には、add()の引数としてドキュメントの配列を渡すか、ドキュメントごとにadd()にドキュメントを渡す

[add()の引数にドキュメントの配列を指定]
db.tbl_x.add([{"id": 2, "name": "Joe", "Team": "Sales", "Title": "VP"}, {"id": 3, "name": "Tomas", "Team": "Development"}]).execute();
　または
[ドキュメントごとにadd()を指定]
db.tbl_x.add({"id": 2, "name": "Joe", "Team": "Sales", "Title": "VP"}).add({"id": 3, "name": "Tomas", "Team": "Development"}).execute();

全件取得




mysqlクライアントからのSQLMySQL ShellのCRUDでの通常のテーブルへの検索MySQL ShellのCRUDでのコレクションの検索
コマンド

mysql> SELECT * FROM tbl_sql;
+----+----------------------------------------------------------+
| id | doc                                                      |
+----+----------------------------------------------------------+
|  1 | {"id": 1, "Team": "Products", "name": "Mike"}            |
|  2 | {"id": 2, "Team": "Sales", "name": "Joe", "Title": "VP"} |
|  3 | {"id": 3, "Team": "Development", "name": "Tomas"}        |
+----+----------------------------------------------------------+
3 rows in set (0.00 sec)

 

mysql-js> db.tbl_sql.select();
+----+----------------------------------------------------------+
| id | doc                                                      |
+----+----------------------------------------------------------+
|  1 | {"id": 1, "Team": "Products", "name": "Mike"}            |
|  2 | {"id": 2, "Team": "Sales", "name": "Joe", "Title": "VP"} |
|  3 | {"id": 3, "Team": "Development", "name": "Tomas"}        |
+----+----------------------------------------------------------+
3 rows in set (0.01 sec)

 

mysql-js> db.tbl_x.find();
[
    {
        "Team": "Marketing",
        "_id": "0c7ded8ba81ce611620d5a37036c1421",
        "id": 5,
        "name": "Rebeca"
    },
<中略>
    {
        "Team": "Sales",
        "Title": "VP",
        "_id": "fc451c81a81ce611620d5a37036c1421",
        "id": 2,
        "name": "Joe"
    }
]
5 documents in set (0.00 sec)
一般ログ 


SELECT * FROM tbl_sql
 


SELECT * FROM `x_sample`.`tbl_sql`


SELECT doc FROM `x_sample`.`tbl_x`

絞り込み検索





mysqlクライアントからのSQLMySQL ShellのCRUDでの通常のテーブルへの検索MySQL ShellのCRUDでのコレクションの検索
コマンド

mysql> SELECT doc FROM tbl_sql WHERE JSON_UNQUOTE(JSON_EXTRACT(doc,'$.name')) LIKE 'Mike';
+-----------------------------------------------+
| doc                                           |
+-----------------------------------------------+
| {"id": 1, "Team": "Products", "name": "Mike"} |
+-----------------------------------------------+
1 row in set (0.00 sec)

 

mysql-js> db.tbl_sql.select(['doc']).where("JSON_UNQUOTE(JSON_EXTRACT(doc,'$.name')) like 'Mike'");
+-----------------------------------------------+
| doc                                           |
+-----------------------------------------------+
| {"id": 1, "Team": "Products", "name": "Mike"} |
+-----------------------------------------------+
1 row in set (0.01 sec)

 

mysql-js> db.tbl_x.find("name like 'Mike'");
[
    {
        "Team": "Products",
        "_id": "1436e7f2e61be611620d5a37036c1421",
        "id": 1,
        "name": "Mike"
    }
]
1 document in set (0.00 sec)
一般ログ 


SELECT doc FROM tbl_sql WHERE JSON_UNQUOTE(JSON_EXTRACT(doc,'$.name')) LIKE 'Mike'
 


SELECT `doc` FROM `x_sample`.`tbl_sql` WHERE (JSON_UNQUOTE(JSON_EXTRACT(`doc`,'$.name')) LIKE 'Mike')

SELECT doc FROM `x_sample`.`tbl_x` WHERE (JSON_UNQUOTE(JSON_EXTRACT(doc,'$.name')) LIKE 'Mike')

MySQL ShellのCRUDでの通常のテーブルへの検索時の注意点

  • select()の第一引数は取得する列名の配列、列名はシングルクオートでかこむ
  • where()内のLIKEなどの演算子は小文字にする


MySQL Shellでクオーテーションが入れ子になる場合、シングルクオートまたはダブルクオートのいずれを外にしても問題なさそうです。

mysql-js> db.tbl_x.find("name like 'Mike'");
　または
mysql-js> db.tbl_x.find('name like "Mike"');


検索する値をパラメタ化してバインドしてもSQL的には同じ結果でした。

mysql-js> db.tbl_x.find("name like 'Mike'");
　または
mysql-js> db.tbl_x.find('name like :param').bind('param','Mike').execute();