Slide 1
Slide 1 text
No content
Slide 2
Slide 2 text
○
○
https://github.com/hmatsu47/mysql80_no_usui_hon
○
https://hmatsu47.booth.pm/
2
Slide 3
Slide 3 text
○
https://qiita.com/hmatsu47/items/aa687aa30c4570bac861
○
https://qiita.com/hmatsu47/items/013da2971c8934d867e7
3
Slide 4
Slide 4 text
○
https://qiita.com/hmatsu47/items/aa687aa30c4570bac861
○
https://qiita.com/hmatsu47/items/013da2971c8934d867e7
4
Slide 5
Slide 5 text
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html#mysqld-8-0-21-json
5
Slide 6
Slide 6 text
6
Slide 7
Slide 7 text
7
Slide 8
Slide 8 text
8
mysql> CREATE DATABASE jsontest;
Query OK, 1 row affected (0.01 sec)
mysql> USE jsontest;
Database changed
mysql> CREATE TABLE t1(
-> j JSON,
-> INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
-> );
1 row in set (0.00 sec)
Slide 9
Slide 9 text
9
INSERT INTO t1 SET j = '{"id": 100, "val": [1, 2, 3]}';
INSERT INTO t1 SET j = '{"id": 101, "val": [4, 5, 6, 7]}';
INSERT INTO t1 SET j = '{"id": 110, "val": [8, 9, 0]}';
INSERT INTO t1 SET j = '{"id": 120, "val": [1, 2]}';
INSERT INTO t1 SET j = '{"id": 122, "val": 3}';
INSERT INTO t1 SET j = '{"id": 130, "val": [4, 5]}';
INSERT INTO t1 SET j = '{"id": 140, "val": [6, 7, 8]}';
INSERT INTO t1 SET j = '{"id": 150, "val": [9, 0, 1, 2]}';
INSERT INTO t1 SET j = '{"id": 200, "val": [3, 4, 5]}';
INSERT INTO t1 SET j = '{"id": 220, "val": [6, 7]}';
Slide 10
Slide 10 text
10
mysql> EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 150\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
Slide 11
Slide 11 text
11
mysql> EXPLAIN SELECT * FROM t1 WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 150\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
Slide 12
Slide 12 text
12
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`j` json DEFAULT NULL,
KEY `i1` ((json_value(`j`, _utf8mb4'$.id' returning unsigned)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
Slide 13
Slide 13 text
13
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`j` json DEFAULT NULL,
KEY `i1` ((json_value(`j`, _utf8mb4'$.id' returning unsigned)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
Slide 14
Slide 14 text
14
mysql> EXPLAIN SELECT * FROM t1 WHERE json_value(`j`, _utf8mb4'$.id' returning unsigned) = 150\G
Slide 15
Slide 15 text
15
mysql> EXPLAIN SELECT * FROM t1 WHERE json_value(`j`, _utf8mb4'$.id' returning unsigned) = 150\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: i1
key: i1
key_len: 9
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
Slide 16
Slide 16 text
https://qiita.com/hmatsu47/items/bd1634b93bf9c1fbce1e
16
// Create Collection
const session = await mysqlx.getSession(connectParam);
const collection = await session.getSchema(schemaName).createCollection(collectionName);
const flag = await collection.createIndex('labels',
{fields: [{"field": "$.labels", "type":"CHAR(100)", "array": true}]});
Slide 17
Slide 17 text
https://qiita.com/hmatsu47/items/bd1634b93bf9c1fbce1e
17
// Create Collection
const session = await mysqlx.getSession(connectParam);
const collection = await session.getSchema(schemaName).createCollection(collectionName);
const flag = await collection.createIndex('labels',
{fields: [{"field": "$.labels", "type":"CHAR(100)", "array": true}]});
CREATE TABLE `image_labeling` (
`doc` json DEFAULT NULL,
`_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id')))
STORED NOT NULL,
PRIMARY KEY (`_id`),
KEY `labels` ((cast(json_extract(`doc`,_utf8mb4'$.labels') as char(100) array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Slide 18
Slide 18 text
https://qiita.com/hmatsu47/items/bd1634b93bf9c1fbce1e
18
// Create Collection
const session = await mysqlx.getSession(connectParam);
const collection = await session.getSchema(schemaName).createCollection(collectionName);
const flag = await collection.createIndex('labels',
{fields: [{"field": "$.labels", "type":"CHAR(100)", "array": true}]});
CREATE TABLE `image_labeling` (
`doc` json DEFAULT NULL,
`_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id')))
STORED NOT NULL,
PRIMARY KEY (`_id`),
KEY `labels` ((cast(json_extract(`doc`,_utf8mb4'$.labels') as char(100) array)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
https://github.com/mysql/mysql-server/tree/8.0/plugin/x/src