1. 背景
* 在MySQL 5.7.8中，MySQL支持由RFC 7159定义的本地JSON数据类型，它支持对JSON(JavaScript对象标记)文档中的数据进行有效访问.
* MySQL会对DML JSON数据自动验证。无效的DML JSON数据操作会产生错误.
* 优化的存储格式。存储在JSON列中的JSON文档转换为一种内部格式，允许对Json元素进行快速读取访问.
* MySQL Json类型支持通过虚拟列方式建立索引，从而增加查询性能提升.
2. Json 索引
* 创建Json索引表 json_key [ name 为虚拟列, virtual 表明不占用磁盘空间 ]
[ GENERATED ALWAYS 与 VIRTUAL可以不写 ]
指定获取json中的name key
|
1
2
3
4
5
|
mysql> CREATE TABLE json_key(
-> uid BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> data JSON NOT NULL,
-> name VARCHAR(32) GENERATED ALWAYS AS (json_extract(data,
'$.name'
)) VIRTUAL,
-> )ENGINE=INNODB CHARSET=utf8mb4;
|
* 创建虚拟列name索引
|
1
2
3
|
mysql>
alter
table
users
add
key
(
name
);
Query OK, 0
rows
affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
* 插入数据带 data中name key [ 插入数据时需要显示指定非虚拟列 ]
|
1
2
3
|
mysql> INSERT INTO json_key(uid, data) SELECT NULL, JSON_OBJECT(
'name'
,
'tom'
,
'sex'
,
'male'
,
'age'
,
'26'
);
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
|
* 插入数据不带 data中name key
|
1
2
3
|
mysql> INSERT INTO json_key(uid, data) SELECT NULL, JSON_OBJECT(
'sex'
,
'female'
,
'age'
,
'29'
);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
|
* 查看json_key所有数据
|
1
2
3
4
5
6
7
8
|
mysql>
select
* from json_key;
+-----+---------------------------------------------+-------+
| uid | data | name |
+-----+---------------------------------------------+-------+
| 1 | {
"age"
:
"26"
,
"sex"
:
"male"
,
"name"
:
"tom"
} |
"tom"
|
| 2 | {
"age"
:
"29"
,
"sex"
:
"female"
} | NULL |
+-----+---------------------------------------------+-------+
2 rows
in
set
(0.01 sec)
|
3. 查询测试
* 通过json方法查询
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
mysql> explain
select
* from json_key where json_extract(data,
'$.name'
) =
'tom'
\G
*************************** 1. row ***************************
id
: 1
select_type: SIMPLE
table: json_key
partitions: NULL
type
: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where
1 row
in
set
, 1 warning (0.00 sec)
|
* 通过虚拟列查询
mysql> explain select * from json_key where name = 'tom'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: NULL
type: ref
possible_keys: name
key: name
key_len: 1023
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
4. 总结
以需求驱动技术，技术本身没有优略之分，只有业务之分。
本文转自asd1123509133 51CTO博客，原文链接：http://blog.51cto.com/lisea/1943389，如需转载请自行联系原作者
