MySQL数据库的JSON数据类型详解

JSON 数据类型意义

其实，没有JSON数据类型的支持，我们一样可以通过varchar类型或者text等类型来保存这一格式的数据，但是，为什么还要专门增加这一数据格式的支持呢？其中肯定有较varchar或者text来存储此类型更优越的地方。

1.保证了 JSON 数据类型的强校验，JSON 数据列会自动校验存入此列的内容是否符合 JSON 格式，非正常格式则报错，而 varchar 类型和 text 等类型本身是不存在这种机制的。
2.MySQL 同时提供了一组操作 JSON 类型数据的内置函数。
3.更优化的存储格式，存储在 JSON 列中的 JSON 数据会被转成内部特定的存储格式，允许快速读取。
4.基于 JSON 格式的特征，支持修改指定的字段值。

存储JSON串

insert into commodity(commodity_id,title,current_selling_price,attributes)
values (9,'小米手机',2000,'{"specValueId":"3845862150911746064","specValue":"深红色","specValueType":"红色系","specValueTypeId":"3845862150911746056","specId":"3845862150911746061","specName":"xx颜色","specType":"颜色","extdata1":"rgba(255, 9, 9, 1)","extdata2":"http://localhost:8080/123.png"}');

这里需要提醒的是：
JSON 类型的列存储的数据要么是 NULL，要么必须是 JSON 格式数据，否则会报错。
JSON 数据类型默认值只能是 NULL。

修改JSON串中指定字段的值

可以使用两个内置函数来修改JSON串中指定字段的值，示例如下：

# 将列attributes中的json串中的字段specValue的值设置为‘绿色’
update commodity set attributes = JSON_SET(attributes,'$.specValue','绿色') where title like '%手机';
# 将列attributes中的json串中的字段specValue的值替换为‘yellow’
update commodity set attributes = JSON_REPLACE(attributes,'$.specValue','yellow') where title like '%手机';

查询 JSON 串中的数据

查询 JSON 串中指定字段的值

select JSON_EXTRACT(attributes,'$.specValue') from commodity where commodity_id = 9;

这里要特别注意了，读取json串中指定字段的值，如果该值是字符串则会把双引号也读取出来，可以使用函数 JSON_UNQUOTE() 去掉双引号：

select json_unquote(JSON_EXTRACT(attributes,'$.specValue')) from commodity where commodity_id = 9;

特殊语法

查询列 attributes 中的 json 串中的字段 specValue 的值，可以使用下面两种查询语句：

select attributes->>'$.specValue' from commodity;

或者

select attributes->'$.specValue' from commodity;

精确查询

假设有个名为 player 的表，有个名为 remarks 的 JSON 类型的列，存储的数据格式如下：

{"name":"lisi","age":39,"address":{"city":"rizhao","region":"lanshan"}}

查询 remarks 列中的json串的name字段的值为“zhangsan”的所有记录：

SELECT * FROM `player` WHERE JSON_EXTRACT(`Remarks`, '$.name') = 'zhangsan';

或者

SELECT * FROM `player` WHERE JSON_CONTAINS(Remarks,JSON_OBJECT('name','zhangsan'));

或者

select * from player where json_contains(remarks,'"zhangsan"','$.name');

嵌套精确查询

查询用户居住城市是日照的所有记录：

SELECT * FROM `player` WHERE JSON_EXTRACT(`Remarks`, '$.address.city') = 'rizhao';

模糊查询

查询列 remarks 中的json串中的字段name的值中包含“zhangsan”的所有记录：

SELECT * FROM `player` WHERE JSON_EXTRACT(`Remarks`, '$.name') LIKE '%zhangsan%';

查询列 remarks 中的 json 串中的字段 age 的值大于等于 25 的所有记录：

SELECT * FROM `player` WHERE JSON_EXTRACT(`Remarks`, '$.age') >= 25;

优化 JSON 查询

找出颜色是“绿色”的商品：

select * from commodity where JSON_EXTRACT(attributes,'$.specValue') = 'yellow';

查看执行计划：

explain select * from commodity where JSON_EXTRACT(attributes,'$.specValue') = 'yellow';

从执行计划可以看到，查询类型是全表扫描，这样的效率是很低的，那么如何优化呢？

按照过往的思路，我们只要设计合理的索引就能避免全表扫描，但是 JSON 列不能创建索引，官方给出的方法是：基于JSON 创建一个生成列（Generated Column），然后基于生成列创建索引，从而达到对 JSON 类型列加索引的效果。

生成列的值在插入数据时不需要设置，MySQL 会根据生成列关联的表达式自动计算填充。

我们分三步进行演示。
第一步，创建生成列：

alter table commodity add column v_spec_value varchar(15) as (attributes->'$.specValue') after attributes;

生成列 v_spec_value 的值根据表达式 attributes->'$.specValue' 自动计算填充。

第二步，为 v_spec_value 创建索引：

alter table commodity add index idx_spec_value  (v_spec_value);

第三步，使用索引字段来查询：

select * from commodity where v_spec_value = '"yellow"';

在查看执行计划：

explain select * from commodity where v_spec_value = '"yellow"';

有个疑问：根据表达式获取JSON串中指定字段值，如果是字符串类型的，会把双引号也获取到，导致存储时也会将双引号一起存进去，这个问题如何解决呢？

处理 JSON 数据的常用函数

JSON_CONTAINS_PATH

判断 JSON 串中是否有指定的字段。

查询列attributes 中的 json 串中含有字段 specValue的记录数量：

SELECT count(*), JSON_CONTAINS_PATH(attributes, 'all', '$.specValue') cp FROM commodity GROUP BY cp;

查询结果如下：

mysql> SET @j = '{"a": 1, "b": 2, "c": {"d": 4}}';
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e'); -- 指定参数one，表示只要json串中含有至少一个指定字段，则返回1，否则返回0
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a', '$.e') |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e'); -- 指定参数all，表示json串中必须含有全部指定的字段才会返回1，否则返回0
+---------------------------------------------+
| JSON_CONTAINS_PATH(@j, 'all', '$.a', '$.e') |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.c.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.c.d') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
mysql> SELECT JSON_CONTAINS_PATH(@j, 'one', '$.a.d');
+----------------------------------------+
| JSON_CONTAINS_PATH(@j, 'one', '$.a.d') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+

JSON_PRETTY

返回格式化的 json 数据：

select json_pretty(attributes) from commodity;

格式化后，显示成这样：

{
  "specId": "3845862150911746061",
  "extdata1": "rgba(255, 9, 9, 1)",
  "extdata2": "http://localhost:8080/123.png",
  "specName": "xx颜色",
  "specType": "颜色",
  "specValue": "yellow",
  "specValueId": "3845862150911746064",
  "specValueType": "红色系",
  "specValueTypeId": "3845862150911746056"
}

总结

JSON 类型是 MySQL 5.7 版本新增的数据类型，用好 JSON 数据类型可以有效解决很多业务中实际问题。最后，我总结下今天的重点内容： 使用 JSON 数据类型，推荐用 MySQL 8.0.17 以上的版本，性能更好，同时也支持 Multi-Valued Indexes。

1.JSON 数据类型的好处是无须预先定义列，数据本身就具有很好的描述性；
2.不要将有明显关系型的数据用 JSON 存储，如用户余额、用户姓名、用户身份证等，这些都是每个用户必须包含的数据；
3.JSON 数据类型推荐用于存储不经常更新的静态数据。

参考资料

1.https://dev.mysql.com/doc/refman/5.7/en/json-functions.html
2.https://zhuanlan.zhihu.com/p/31823258
3.https://dasini.net/blog/2018/07/23/30-mins-with-mysql-json-functions/