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クライアントからのSQL | MySQL 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クライアントからのSQL | MySQL 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クライアントからのSQL | MySQL Shellのテーブル一覧取得 | MySQL Shellのコレクション一覧取得 | ||||
|---|---|---|---|---|---|---|
| コマンド | mysql> SHOW TABLES;
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クライアントからのSQL | MySQL 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(); mysql-js> db.tbl_x.add([{"id": 4, "name": "Luis", "Team": "Development"}, {"id": 5, "name": "Rebeca", "Team": "Marketing"}]).execute(); |
| 一般ログ | 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クライアントからのSQL | MySQL 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) |
| 一般ログ |
|
|
|
絞り込み検索
| mysqlクライアントからのSQL | MySQL 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 `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();
