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

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

23 2019-10-25 20:27:13

## 背景

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

我的设计非常简单：
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语句：
```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** 查询局部数据
```shell
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** 插入新的元素
```shell
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** 插入或覆盖元素
```shell
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** 替换已有元素
```shell
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** 删除部分元素
```shell
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] |
+----+------------------------------+-----------+
```

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

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

## 参考资料
[MySQL 5.7 新特性 JSON 的创建，插入，查询，更新](http://www.lnmp.cn/mysql-57-new-features-json.html)
[MySQL JSON数据类型操作 - 掘金](https://juejin.im/post/59e4bb1151882578d224a87e)