Mysql 支持 JSON 数据
我们知道，MongoDB是支持非结构化数据的，例如以JSON格式保存，实际上 MySQL Server也是能有限支持 JSON数据的。MySQL Server自5.7+版本开始支持JSON数据，所以我们在开发当中，碰到非结构化的数据，可以直接在数据库中存储JSON内容，然后用JSON支持相关函数来操纵数据库。
MySQL Server提供了以下的JSON功能：
生成JSON数据
提取JSON字段
操纵JSON字段
从JSON中生成虚拟字段
索引功能
MySQL提供两个函数 json_array() / json_object() 来构造 JSON 数据。
例如：
SELECT JSON_ARRAY(1, "abc", NULL, TRUE); select json_object( |
|---|
这两个函数，都是支持 key, value, key, value … 参数。
MySQL 提供了一个json 数据类型字段，我们可以用这个字段来存储JSON字符串对象。
例如：
create table user (uid int auto_increment, data json,primary key(uid)) engine=innodb;
上述代码创建了一个包含 uid, data 的表，其中 data 为 JSON 数据结构。
insert into user values (NULL, '{"name":"Richard","mail":"richard@apj.com","address":"Shangahai"}');
insert into user values (NULL, '{"name":"Amy","mail":"amy@gmail.com"}');
UPDATE user set data = JSON_SET(data, '$.address', "Zhuhai") where JSON_EXTRACT(data, '$.address') is null;
很明显，如果我们使用JSON相关的函数来运算，那么使用很不方便，数据较多的时候，也会存在性能问题，为了提供便利和提升性能，MySQL Server提供了虚拟字段，我们可以从 json 数据中派生一些字段，类似普通字段一样，并且可以建立索引，这样就能方便使用和提高性能了。
示例：
alter table user add v_name varchar(20) generated always as (JSON_UNQUOTE(data->'$.name')) virtual;
alter table user add v_mail varchar(60) generated always as (JSON_UNQUOTE(data->'$.mail')) virtual;
alter table user add v_address varchar(60) generated always as (JSON_UNQUOTE(data->'$.address')) virtual;
需要注意的是，我们用 data ->'$.name' 返回的数据，是包含了JSON的分隔符双引号的，所以导致我们在查询数据的时候，需要对数据增加引号，因此我们可以用 JSON_UNQUOTE 去掉双引号，这样返回的就是普通的数据了。
使用虚拟字段：
select * from user where v_name = 'Amy';
对虚拟字段建立索引：
alter table user add index idx_name(v_name);
使用限制：
虚拟字段建立索引的索引，不能参与聚合索引，只能单独使用。
虚拟字段，只能只读查询，不能直接更新
完整示例代码：
create table user ( uid int auto_increment, data json,primary key(uid)) engine=innodb;insert into user values (NULL, '{"name":"Richard","mail":"richard@apj.com","address":"Shangahai"}');insert into user values (NULL, '{"name":"Amy","mail":"amy@gmail.com"}');alter table user add v_name varchar(20) generated always as (JSON_UNQUOTE(data->'$.name')) virtual;alter table user add v_mail varchar(60) generated always as (JSON_UNQUOTE(data->'$.mail')) virtual;alter table user add v_address varchar(60) generated always as (JSON_UNQUOTE(data->'$.address')) virtual;alter table user add index idx_name(v_name);select * from user;UPDATE user set data = JSON_SET(data, '$.address', "Zhuhai") where JSON_EXTRACT(data, '$.address') is null;select * from user where v_name = 'Amy';SELECT JSON_ARRAY(1, "abc", NULL, TRUE);SELECT JSON_OBJECT('id', 87, 'name', 'carrot');运行结果：
函数名 | 功能说明 | 使用示例 | 输出结果 |
|---|---|---|---|
JSON_ARRAY() | 创建JSON数组 | SELECT JSON_ARRAY(1, "abc", NULL, TRUE); | [1, "abc", null, true] |
JSON_OBJECT() | 创建JSON对象 | SELECT JSON_OBJECT('id', 87, 'name', 'carrot'); | {"id": 87, "name": "carrot"} |
JSON_QUOTE() | 将字符串转为JSON格式字符串 | SELECT JSON_QUOTE('null'); | "null" |
JSON_CONTAINS() | 检查JSON是否包含特定值 | SELECT JSON_CONTAINS('{"a":1,"b":2}', '1', '$.a'); | 1 (true) |
JSON_CONTAINS_PATH() | 检查路径是否存在 | SELECT JSON_CONTAINS_PATH('{"a":1}', 'one', '$.a'); | 1 (true) |
JSON_EXTRACT() / -> | 提取路径对应的值 | SELECT JSON_EXTRACT('{"a":1}', '$.a'); 或 SELECT '{"a":1}'->'$.a'; | 1 |
JSON_KEYS() | 返回对象的所有键 | SELECT JSON_KEYS('{"a":1,"b":2}'); | ["a", "b"] |
JSON_SEARCH() | 查找值的路径 | SELECT JSON_SEARCH('{"a":1}', 'one', '1'); | "$.a" |
JSON_ARRAY_APPEND() | 向数组追加元素 | SELECT JSON_ARRAY_APPEND('["a"]', '$', 'b'); | ["a", "b"] |
JSON_ARRAY_INSERT() | 向数组插入元素 | SELECT JSON_ARRAY_INSERT('["a","c"]', '$[1]', 'b'); | ["a", "b", "c"] |
JSON_INSERT() | 插入新值（不替换现有） | SELECT JSON_INSERT('{"a":1}', '$.b', 2); | {"a":1,"b":2} |
JSON_MERGE_PRESERVE() | 合并多个JSON文档 | SELECT JSON_MERGE_PRESERVE('{"a":1}', '{"b":2}'); | {"a":1,"b":2} |
JSON_REMOVE() | 删除指定路径的值 | SELECT JSON_REMOVE('{"a":1,"b":2}', '$.a'); | {"b":2} |
JSON_REPLACE() | 替换指定路径的值 | SELECT JSON_REPLACE('{"a":1}', '$.a', 10); | {"a":10} |
JSON_SET() | 插入或更新值 | SELECT JSON_SET('{"a":1}', '$.a', 10, '$.b', 2); | {"a":10,"b":2} |
JSON_DEPTH() | 返回JSON最大深度 | SELECT JSON_DEPTH('{"a":{"b":1}}'); | 3 |
JSON_LENGTH() | 返回元素数量 | SELECT JSON_LENGTH('{"a":1,"b":[1,2]}'); | 2 |
JSON_TYPE() | 返回值的类型 | SELECT JSON_TYPE('{"a":1}'); | "OBJECT" |
JSON_VALID() | 验证是否为有效JSON | SELECT JSON_VALID('{"a":1}'); | 1 (true) |
JSON_PRETTY() | 格式化JSON输出 | SELECT JSON_PRETTY('{"a":1,"b":2}'); | 格式化后的多行JSON |
JSON_STORAGE_SIZE() | 返回存储字节数 | SELECT JSON_STORAGE_SIZE('{"a":1000}'); | 9 (大约) |
JSON_TABLE() | 将JSON转为表格 | SELECT * FROM JSON_TABLE('[{"x":2},{"x":3}]', '$[*]' COLUMNS(x INT PATH '$.x')) AS jt; | 两行数据：2和3 |
注意：
->是JSON_EXTRACT()的简写语法（MySQL 5.7.9+）->>是JSON_UNQUOTE(JSON_EXTRACT())的简写（MySQL 5.7.13+）所有函数在MySQL 5.7.8及以上版本可用，部分函数需要更高版本（如
JSON_TABLE()需要8.0+）
参考资料：