作者：朱智武，新炬网络高级技术专家。

InnoDB/MyISAM/CSV存储引擎均支持JSON

笔者经过测试，InnoDB/MyISAM/CSV三个存储引擎支持JSON。

InnoDB
mysql>create table t ( id int, data json, primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
 
MyISAM
mysql>create table t ( id int, data json) engine=myisam;
Query OK, 0 rows affected (0.02 sec)
 
CSV
mysql>create table t (id int not null, data json not null) engine=csv;
Query OK, 0 rows affected (0.00 sec)

MEMORY存储引擎使用JSON数据类型会报错，错误显示JSON数据类型的实现与BLOB/TEXT有一定关联。

mysql>create table t ( id int, data json, primary key(id)) engine=memory;
ERROR 1163 (42000): The used table type doesn't support BLOB/TEXT columns

创建JSON

创建包含JSON字段的表

mysql>create table t ( id int, data json, primary key(id)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

插入包含合法JSON值的一条记录

mysql>insert into t values (1,'{"type":"fruit","name":"apple"}');
Query OK, 1 row affected (0.00 sec)

如果JSON值非法，会报错

mysql>insert into t values (1,'{"type":"fruit","name":"apple"})');
ERROR 3140 (22032): Invalid JSON text: "The document root must not follow by other values." at position 31 in value (or column) '{"type":"fruit","name":"apple"}}'.
mysql>insert into t values (1,'{"type":"fruit","name":"apple",}');
ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 31 in value (or column) '{"type":"fruit","name":"apple",}'.

使用SELECT语句可以将该JSON值查询出来

mysql>select * from t;
+----+-------------------------------------+
| id | data                                |
+----+-------------------------------------+
|  1 | {"name": "apple", "type": "fruit"} |
+----+-------------------------------------+

JSON值不仅仅可以以字符串表达，还可以通过函数生成，比如JSON_ARRAY()将一系列值组合成一个JSON数组，JSON_OBJECT()将键值对组合成一个JSON对象，JSON_MERGE()将两个或更多的JSON文档进行合并。

JSON_ARRAY('apple','orange') => ["apple", "orange"]
JSON_OBJECT('name','apple','type','fruit') => {"name": "apple", "type": "fruit"}
JSON_MERGE('["orange"]', '{"name":"apple"}') => ["orange", {"name": "apple"}]

查询JSON

JSON的路径表达式选择出JSON文档中的给定值。如以下的例子，我们使用JSON_EXTRACT()函数读取JSON文档键为name的值。

mysql>select json_extract('{"name": "apple", "type": "fruit"}','$.name');
+-------------------------------------------------------------+
| json_extract('{"name": "apple", "type": "fruit"}','$.name') |
+-------------------------------------------------------------+
| "apple"                                                     |
+-------------------------------------------------------------+

路径表达式以$字符开始，$字符表示该JSON文档，之后是路径选择器，包括点号和[N]，其中点号对应JSON对象，而[N]对应JSON数组（数组从0开始编号）。路径表达式中还可以包含*和**匹配符。如果JSON文档中不存在该路径，那么返回值会是NULL。

路径表达式可以内联使用，比如以下的例子。

mysql>select * from t where data->'$.name'= 'apple';
+----+------------------------------------+
| id | data                               |
+----+------------------------------------+
|  1 | {"name": "apple", "type": "fruit"} |
+----+------------------------------------+

事实上，data->'$.name'即是json_extract(data,'$.name')的同义语法。如果查看执行计划，上面的SELECT语句走的是全表扫描。

执行以下的SQL语句创建data->'$.name'的生成字段（生成字段，即Generated Column，也是MySQL 5.7的新特性），并创建相应的索引。

alter table t add column name varchar(20) as (json_unquote(data->'$.name')) virtual, add key k_name(name);

 

再次查看该语句的执行计划，可以使用上索引。

修改JSON