省时省力的MySql 5.7 Json数据类型及操作

背景

工作中遇到一个需求，同时调用了两个异步接口，这两个接口在一段时间之后会回调预设的地址，将结果返回，在回调都完成后进行下一步操作。两个接口之间没有依赖关系，有可能同时返回。

我的设计非常简单：

状态值按位表示，如右数第一位为1说明在等待异步接口A，第二位为1说明在等待异步接口B。即01表示等待A回调，10等待B，11同时等待A和B，这样在接口回调时只需要在数据库使用位操作即可确认回调已完成，如status = status ^ 2表示B接口回调完成
返回的结果是Json结构保存在表中的，这是由于该表上的数据类型太多（3种不同的type共享表中其他列的数据），使用Json区分数据用途。其实当时已经考虑到了回调同时到达的情况，但没想好处理方法，因为我以为这版需求不是我做，于是埋了坑也没管，血的教训
初步设计时认为将Json全量读出再将修改后的Json结构全量更新即可，很明显，这存在并发问题，于是犯难了——文本结构不像数字型，局部修改非常困难
如果改为串行调用两个接口，实现起来会非常麻烦和啰嗦，这是最后手段

今天看到数据导出工单里有一条奇怪的SQL语句：

SELECT json_extract(detail,"$.width") ... FROM ... WHERE ...

第一眼并没有很在意，但是鬼使神差我又回过头来看了一下这条记录，从语义上看似乎在单独读取Json结构中的数据，但是真的有这种操作吗？还是说这是自定义函数？这会不会是解决我燃眉之急的曙光？带着将信将疑的态度打开Google，没想到就此打开了新世界的大门。

MySql中的Json类型是什么

在上一家公司工作时，保存JSON数据的列我们都习惯使用text类型，要不然就使用varchar类型，所有对Json的操作都是上述读出-修改-更新的过程。

而在新公司的表结构中，出现了从来没有见过的数据类型——json类型。

json类型有一些特点：

在写入时校验是否符合Json格式，格式不符合会报错
没有默认值

说实话，虽然了解到了这些特点，除了在写入时有格式保障以外呢？还有什么吗？

MySql 5.7 的内置Json操作函数

是的，这就是Json结构的最大特点，可以像在其他高级编程语言中一样，将Json作为Json操作，而不再是当作字符串类型操作。

想象一下，如果对Json数据的局部修改从读取-反序列化-修改-序列化-回写变为直接修改，能节省多少资源；

以前我们读取局部数据时必须经历读取-反序列化-根据Key查找，而现在我们可以直接根据Key读取；

以前我们刷库时遇到Json结构，那简直是噩梦，而现在甚至可以一个语句完成；

以前我们根据Json的局部数据作为SQL的WHERE条件，那根本是件不可能的事，而Json函数就可以......

没错，MySql的内置Json函数很强大，解决了众多曾经不敢想的问题。

非常实用的Json函数

JSON_EXTRACT 查询局部数据

mysql> SELECT JSON_EXTRACT('{"name":"Zhaim","tel":"13240133388"}',"$.name");
+---------------------------------------------------------------+
| JSON_EXTRACT('{"name":"Zhaim","tel":"13240133388"}',"$.name") |
+---------------------------------------------------------------+
| "Zhaim"                                                       |
+---------------------------------------------------------------+

# 可以使用 column->path 的形式提取元素的值，对应字符串类型的 category->'$.name' 中还包含着双引号，可以用 JSON_UNQUOTE 函数将双引号去掉
# 从 MySQL 5.7.13 起也可以通过这个操作符 ->> 这个和 JSON_UNQUOTE 是等价的
# 用 -> 直接提取时要注意元素值的类型
mysql> SELECT * FROM lnmp WHERE category->>'$.name' = 'lnmp.cn';
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 2, 3] |
+----+------------------------------+-----------+

JSON_INSERT 插入新的元素

mysql> UPDATE lnmp SET category = JSON_INSERT(category, '$.name', 'lnmp', '$.url', 'www.lnmp.cn') WHERE id = 1;

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------+-----------+
| id | category                                           | tags      |
+----+----------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php.net"}                       | [1, 3, 5] |
+----+----------------------------------------------------+-----------+

JSON_SET 插入或覆盖元素

mysql> UPDATE lnmp SET category = JSON_SET(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') WHERE id = 1;

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------------------------------------+-----------+
| id | category                                                                         | tags      |
+----+----------------------------------------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "http://www.lnmp.cn", "host": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php.net"}                                                     | [1, 3, 5] |
+----+----------------------------------------------------------------------------------+-----------+

JSON_REPLACE 替换已有元素

mysql> UPDATE lnmp SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') WHERE id = 2;

mysql> SELECT * FROM lnmp;
+----+----------------------------------------------------------------------------------+-----------+
| id | category                                                                         | tags      |
+----+----------------------------------------------------------------------------------+-----------+
|  1 | {"id": 1, "url": "http://www.lnmp.cn", "host": "www.lnmp.cn", "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php"}                                                         | [1, 3, 5] |
+----+----------------------------------------------------------------------------------+-----------+

JSON_REMOVE 删除部分元素

mysql> UPDATE lnmp SET category = JSON_REMOVE(category, '$.url', '$.host') WHERE id = 1;

mysql> SELECT * FROM lnmp;
+----+------------------------------+-----------+
| id | category                     | tags      |
+----+------------------------------+-----------+
|  1 | {"id": 1, "name": "lnmp.cn"} | [1, 3, 4] |
|  2 | {"id": 2, "name": "php"}     | [1, 3, 5] |
+----+------------------------------+-----------+

Json函数列表

Name	Description
JSON_APPEND()	Append data to JSON document
JSON_ARRAY()	Create JSON array
JSON_ARRAY_APPEND()	Append data to JSON document
JSON_ARRAY_INSERT()	Insert into JSON array-> Return value from JSON column after evaluating path; equivalent to JSON_EXTRACT().
JSON_CONTAINS()	Whether JSON document contains specific object at path
JSON_CONTAINS_PATH()	Whether JSON document contains any data at path
JSON_DEPTH()	Maximum depth of JSON document
JSON_EXTRACT()	Return data from JSON document->> Return value from JSON column after evaluating path and unquoting the result; equivalent to JSON_UNQUOTE(JSON_EXTRACT()).
JSON_INSERT()	Insert data into JSON document
JSON_KEYS()	Array of keys from JSON document
JSON_LENGTH()	Number of elements in JSON document
JSON_MERGE()	Merge JSON documents, preserving duplicate keys. Deprecated synonym for JSON_MERGE_PRESERVE()
JSON_MERGE_PRESERVE()	Merge JSON documents, preserving duplicate keys
JSON_OBJECT()	Create JSON object
JSON_QUOTE()	Quote JSON document
JSON_REMOVE()	Remove data from JSON document
JSON_REPLACE()	Replace values in JSON document
JSON_SEARCH()	Path to value within JSON document
JSON_SET()	Insert data into JSON document
JSON_TYPE()	Type of JSON value
JSON_UNQUOTE()	Unquote JSON value
JSON_VALID()	Whether JSON value is valid

ps. 注意在MySql 5.7.8及以上才可以使用，如果存储结构可以选择json类型，就肯定是可以用的

总结

通过JSON_SET()方法，实现了类似status = status ^ 2这样的语句，最终实现了局部修改。

参考资料

MySQL 5.7 新特性 JSON 的创建，插入，查询，更新

MySQL JSON数据类型操作 - 掘金

本文搬自我的博客，欢迎参观！