作者：朱智武，新炬网络高级技术专家。
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
MySQL有一些函数，可以修改JSON文档，并返回修改后的新JSON文档。路径表达式指出文档中的哪部分需要修改。这些函数比如JSON_INSERT()、JSON_REPLACE()、JSON_SET()和JSON_REMOVE()。我们以以下的JSON文档为例。
mysql> set @json='["apple", {"attr": [50, true], "name": "orange"}]';
JSON_INSERT()可以添加新值，但它不会替换已存在的值。
mysql> select json_insert(@json, '$[1].attr[0]', 2, '$[2]', "pear");
+-----------------------------------------------------------+
| json_insert(@json, '$[1].attr[0]', 2, '$[2]', "pear") |
+-----------------------------------------------------------+
| ["apple", {"attr": [50, true], "name": "orange"}, "pear"] |
+-----------------------------------------------------------+
JSON_REPLACE()替换已有的值，但忽略新值，即新值不会添加到文档中。
mysql> select json_replace(@json, '$[1].attr[0]', 2, '$[2]', "pear");
+--------------------------------------------------------+
| json_replace(@json, '$[1].attr[0]', 2, '$[2]', "pear") |
+--------------------------------------------------------+
| ["apple", {"attr": [2, true], "name": "orange"}] |
+--------------------------------------------------------+
JSON_SET()替换已有路径的值，添加未有路径的值。
mysql> select json_set(@json, '$[1].attr[0]', 2, '$[2]', "pear");
+----------------------------------------------------------+
| json_set(@json, '$[1].attr[0]', 2, '$[2]', "pear") |
+----------------------------------------------------------+
| ["apple", {"attr": [2, true], "name": "orange"}, "pear"] |
+----------------------------------------------------------+
JSON_REMOVE()移除JSON文档中给定的一个或多个路径，如果路径不存在的话，函数会忽略该路径。
mysql> select json_remove(@json, '$[1].attr[0]', '$[2]');
+-----------------------------------------------+
| json_remove(@json, '$[1].attr[0]', '$[2]') |
+-----------------------------------------------+
| ["apple", {"attr": [true], "name": "orange"}] |
+-----------------------------------------------+
结语
MongoDB是主要支持JSON的数据库，而MySQL增加了对流行的JSON的支持，扩展了MySQL的应用领域。到底是使用MySQL+MongoDB更好呢？还是单单使用MySQL更好呢？在不同的场景中可能有不同的答案。此外，很多人将MySQL与PostgreSQL作对比，很多文章列出了PostgreSQL比MySQL强的地方，对JSON的支持是其中一项，现在，MySQL将这弥补上了。
