CreateArtTechnology
/ Blog
Login
最新文章
Java
语言相关
库相关
虚拟机相关
CreateArtTechnology
项目搭建
使用的工具
自研的工具
开源工具
ELK
ElasticSearch
Jenkins
Markdown
GraphQL
Arthas
生产工具
Linux
Nginx
VersionControl
Subversion
Git
Redis
Archiva
Maven
Zookeeper
Spring
SpringBoot
MySql
HBase
Cassandra
容器化
Docker
Kubernetes
服务容器化从零开始
未分类笔记
算法相关
概念相关
豆知识
机器学习
机器学习从零开始
省时省力的MySql 5.7 Json数据类型及操作
21
2019-10-25 20:27:13
生产工具
MySql
## 背景 工作中遇到一个需求,同时调用了两个异步接口,这两个接口在一段时间之后会回调预设的地址,将结果返回,在回调都完成后进行下一步操作。两个接口之间没有依赖关系,有可能同时返回。 我的设计非常简单: 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] | +----+------------------------------+-----------+ ``` ### 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 的创建,插入,查询,更新](http://www.lnmp.cn/mysql-57-new-features-json.html) [MySQL JSON数据类型操作 - 掘金](https://juejin.im/post/59e4bb1151882578d224a87e)
发布文章 101
文章被阅读 1603
最近修改
什么是“丝滑”的曲线
2021-12-08 15:19:20
高效空间数据索引R树及其批量加载方法STR简介
2021-09-29 20:33:37
关于分库分表的一些事儿
2021-06-25 11:51:25
获得诺奖的稳定匹配理论之TTC算法与GS算法
2021-03-14 23:04:48
算法小白的机器学习入门实践,从零到上线
2021-01-13 14:28:27
分站宗旨
一站式资料平台,减少重复检索,减少重复采坑。