阅读 363

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

背景

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

我的设计非常简单:

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

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

SELECT json_extract(detail,"$.width") ... FROM ... WHERE ...
复制代码

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

MySql中的Json类型是什么

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

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

json类型有一些特点:

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

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

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数据类型操作 - 掘金

本文搬自我的博客,欢迎参观!

image