面试官问: MySQL里面可以用JSON吗？
保姆级教程；阅读完本篇文章需要
10分钟
MySQL 支持RFC 7159定义的原生的JSON类型。我们知道MongoDB中单个文档的最大限制为16M，而 MySQL 里面单个文档的最大限制受限于参数max_allowed_packet。
我们在上一篇关于JSON的文章中讨论了JSON数组的一些操作，但主要集中在json_table上，所以本篇文章我们一起来看下MySQL中一些基础和常用的JSON函数示例，以及数组的增删改查。其实在学习过程中看一眼示例胜过许多定义的描述。
🥘 怎么增删改查？
👾 JSON对象上的操作
➡️ json_pretty
在开始之前，先介绍一个format 函数json_pretty，便于直观的查看JSON对象。
select
json_pretty('{"id":100101,"items":[{"item_id":"A01","item_name":"computer"},{"item_id":"A02","item_name":"phone"}]}')
-- output
{
"id": 100101,
"items": [
{
"item_id": "A01",
"item_name": "computer"
},
{
"item_id": "A02",
"item_name": "phone"
}
]
}
➡️ json_extract
json_extract是比较常用的内置查询函数，比如查询@v_goods中id的值：
❇️
$为路径的开头
set @v_goods = json_pretty('{"id":100101,"items":[{"item_id":"A01","item_name":"computer"},{"item_id":"A02","item_name":"phone"}]}')
select json_extract(@v_goods,'$.id');
-- output
100101
但是有个小细节，就是我们知道还可以通过->或者->> 简写来查询，可以用来查询上面变量@v_goods中的元素吗？
答案是不可以，因为->或者->> 左边只能是存储json的列，不可以是表达式。
select @v_goods->'$.id';
-- output
Syntax error near '->>'$.id'' at line 1
➡️ column→path
这个就是我们前面提到的简单的查询形式，而且还有另一种形式：column->>path，区别是后者去掉了引号，继续看示例。
首先创建示例表和数据：
create table ugoods
(
uid int auto_increment primary key,
attr json
);
insert into ugoods
(attr) values('{"id":100101,"items":[{"item_id":"A01","item_name":"computer"},{"item_id":"A02","item_name":"phone"}]}')
结果
可以看到第一个操作符查询处的值带有引号，而第二个去掉了引号。
select
attr->'$.items[0].item_name' as col1,
attr->>'$.items[0].item_name' as col2
from
ugoods
-- output
|col1 |col2 |
|----------|--------|
|"computer"|computer|
其实就引出了一个函数json_unquote，在后面会介绍到。
➡️ json_set
更新存在的值或者插入不存在的键值。
第一个语句将"name": "tony"更新为"name": "zach"。
select json_set('{"name": "tony", "gender": 1}', '$.name', 'zach') as res;
-- output
|res |
|-----------------------------|
|{"name": "zach", "gender": 1}|
select json_set('{"name": "tony", "gender": 1}', '$.age', 18) as res;
-- output
|res |
|----------------------------------------|
|{"age": 18, "name": "tony", "gender": 1}|
➡️ json_insert
向json文档中插入键值对。
例如下面，我们向文档中插入"age": 18。
select json_insert('{"name": "tony", "gender": 1}', '$.age', 18) as res;
|res |
|----------------------------------------|
|{"age": 18, "name": "tony", "gender": 1}|
当然也可以插入多个键值对（此处插入age和address）
select json_insert('{"name": "tony", "gender": 1}', '$.age', 18,'$.address', 'unknow') as res;
➡️ json_replace
和json_set有点类似，但是只能更新，不可以插入新的数据。
下面更新文档中gender的值为2。
select json_replace('{"name": "tony", "gender": 1}', '$.gender', 2) as res;
|res |
|-----------------------------|
|{"name": "tony", "gender": 2}|
➡️ json_remove
删除指定的键值对；比如删除文档中的"gender": 1。
select json_remove('{"name": "tony", "gender": 1}', '$.gender') as res;
|res |
|----------------|
|{"name": "tony"}|
🎳 JSON数组上的操作
➡️ json_array_append
在指定位置追加数组元素。
第一个查询向数组位置1追加f，可以看到下标为1的子数组["b", "c"]变成了["b", "c", "f"]。
select json_array_append('["a", ["b", "c"], "d"]', '$[1]', 'f') as res;
|res |
|---------------------------|
|["a", ["b", "c", "f"], "d"]|
-- 追加之0位置，即"a"--["a", "f"]
select json_array_append('["a", ["b", "c"], "d"]', '$[0]', 'f') as res;
|res |
|-----------------------------|
|[["a", "f"], ["b", "c"], "d"]|
➡️ json_array_insert
在指定位置插入数组元素。
下面示例将f插入到0号位置，即["a", ["b", "c"], "d"]更新为["f", "a", ["b", "c"], "d"]。
select json_array_insert('["a", ["b", "c"], "d"]', '$[0]', 'f') as res;
|res |
|---------------------------|
|["f", "a", ["b", "c"], "d"]|
➡️ json_remove
删除数组元素。下面删除0位置的a。
select json_remove('["a", ["b", "c"], "d"]', '$[0]') as res;
|res |
|-----------------|
|[["b", "c"], "d"]|
🎨 其他函数解析
➡️ json_contains
在目标文档中是否包含指定的文档
-- key code 中是否包含100，为真返回1
select json_contains('{"code": 100, "name": "apple"}', '100', '$.code') col;
-- output
|col|
|---|
|1 |
-- 文档中是否包含{"code": 100}，为真返回1
select json_contains('{"code": 100, "name": "apple"}', '{"code": 100}', '$') as col;
-- output
|col|
|---|
|1 |
-- 文档中是否包含{"name": "apple"}，为真返回1
select json_contains('{"code": 100, "name": "apple"}', '{"name": "apple"}', '$') as col;
-- output
|col|
|---|
|1 |
-- 数组中是否包含4，为真返回1
select json_contains('[1, 2, 3, 4]', '4', '$') as col;
-- output
|col|
|---|
|1 |
➡️ json_unquote
前面提到过，去掉结果中的引号，类似与->>。
select
attr->'$.items[0].item_name' as col1,
attr->>'$.items[0].item_name' as col2,
json_unquote(attr->'$.items[0].item_name') as col3
from
ugoods
-- output
|col1 |col2 |col3 |
|----------|--------|--------|
|"computer"|computer|computer|
➡️ json_overlaps
如果存在重叠的对象，即交集，就返回真，否则为假。
-- items中的数组是相同的，返回1
select
json_overlaps('{"id":100101,"items":[{"item_id":"A01","item_name":"computer"},{"item_id":"A02","item_name":"phone"}]}',
'{"id":100102,"items":[{"item_id":"A01","item_name":"computer"},{"item_id":"A02","item_name":"phone"}]}') as cmp
-- output
|cmp|
|---|
|1 |
-- id和items都是相同的，返回1
select
json_overlaps('{"id":100101,"items":[{"item_id":"A01","item_name":"computer"},{"item_id":"A02","item_name":"phone"}]}',
'{"id":100101,"items":[{"item_id":"A01","item_name":"computer"},{"item_id":"A02","item_name":"phone"}]}') as cmp
-- output
|cmp|
|---|
|1 |
-- 没有交集，返回假，即0
select
json_overlaps('{"ids":100101,"items":[{"item_id":"A01","item_name":"computer"},{"item_id":"A02","item_name":"phone"}]}',
'{"id":100101,"items":[{"item_id":"A02","item_name":"computer"},{"item_id":"A02","item_name":"phone"}]}') as cmp
-- output
|cmp|
|---|
|0 |
✨ JSON数组也一样。第一查询有公共的交集3，所以返回1；第二个查询没有公共元素，所以返回0
select json_overlaps('[1, 2, 3]', '[1, 3, 3]') as cmp
-- output
|cmp|
|---|
|1 |
select json_overlaps('[1, 2, 3]', '[4, 5, 6]') as cmp
-- output
|cmp|
|---|
|0 |
补充下，开头介绍json_overlaps的那个例子中的太长了，这个比较直观一点。下面的例子中有公共的键值对"d":10。
select json_overlaps('{"a":3,"b":10,"d":10}', '{"c":3,"e":10,"f":1,"d":10}') as cmp;
-- output
|cmp|
|---|
|1 |
➡️ member of
判断元素是否包含在数组中，也是常用的函数之一。包含返回true，反之false。有一点需要补充的就是，在MySQL里面1通常表示true，0表示false
-- 107 不是数组的元素
select 107 member of('[100, "abc", "def", 101, "ab", 10, "w"]') as res;
-- output
|res|
|---|
|0 |
-- 10 包含在目标数组中
select 10 member of('[100, "abc", "def", 101, "ab", 10, "w"]') as res;
-- output
|res|
|---|
|1 |
但是下面这个结果应该是什么呢？
select '{"name": "zach"}' member of('[100, "abc", "def", 101, "ab", 10, "w", {"name": "zach"}]') as res;
返回0,因为此时数据库将{"name": "zach"}看作字符串了。所以正确的操作应该按照下面这样，首先将其转化为json类型，然后再执行 member of
select cast('{"name": "zach"}' as json) member of('[100, "abc", "def", 101, "ab", 10, "w", {"name": "zach"}]') as res;
-- output
|res|
|---|
|1 |
➡️ json_keys
获取文档中的键集合
select json_keys('{"name": "tony"}') as res
-- output
|res |
|--------|
|["name"]|
➡️ json_length
计算json长度，即其中键值对的数目。
select json_length('{"name": "tony", "age": 18}') as res
-- output
|res|
|---|
|2 |
➡️ json_object
返回json对象，常用于将成对的变量转换成json对象。
select json_object('id',1001,'code','SHDIC') as res
-- output
|res |
|-----------------------------|
|{"id": 1001, "code": "SHDIC"}|
➡️ json_valid
验证给定的变量是否是有效的json对象类型
select json_valid('{"id": 1001, "code": "SHDIC"}') as res
-- output
|res|
|---|
|1 |
select json_valid('{"id": 1001, "code"}') as res
-- output
|res|
|---|
|0 |
➡️ json_value
第一个参数为json对象，第二个参数为路径，根据路径查找对应的值。下面查询键code对应的值。
select json_value('{"id": 1001, "code": "SHDIC"}','$.code') as res
-- output
|res |
|-----|
|SHDIC|
➡️ json_search
与前面相反，根据值获取路径，需要注意的是第二参数，one代表匹配一次，all代表匹配所有满足条件的；第三个参数是所要搜索的json值
select json_search('{"id": 1001, "code": "SHDIC"}','one','SHDIC') as res
-- output
|res |
|--------|
|"$.code"|
-- 查找值是SHDIC的全部键值对对应的路径
select json_search('[{"id": 1001, "code": "SHDIC"}, "SHDIC"]','all','SHDIC') as res
-- output
|res |
|---------------------|
|["$[0].code", "$[1]"]|
需要注意的是下面的查询没有结果，为什么呢？ 因为json_search第三个参数是字符串，json对象里面id对应的1001是数值型。
select json_search('{"id": 1001, "code": "SHDIC"}','one','1001') as res
-- output
|res|
|---|
| |
➡️ json_storage_size
json对象占用的字节数，用于储存 JSON 文档二进制形式的空间。
select json_storage_size('{"id": 1001, "code": "SHDIC"}') as res
-- output
|res|
|---|
|31 |
☑️ 总结？
在本文中，我们探讨了MySQL中JSON数据类型的使用，包括如何利用各种JSON函数进行数据的增删改查，以及一些高级操作。通过丰富的示例和详细的解释，使用json存储，以提高数据库操作的效率和灵活性。
总结一下，MySQL的JSON支持为数据库操作带来了前所未有的灵活性和功能性。从基本的格式化和查询到复杂的操作和优化，JSON在MySQL中的应用显著扩展了我们处理数据的方式。正如我们看到的，无论是进行快速查询还是构建复杂的数据结构，JSON都能以高效且直观的方式满足需求。
希望这篇文章对您有所帮助，感谢您的阅读！继续关注我的博客，获取更多实用的数据库和编程技巧。下次再见！
原文始发于微信公众号（小新数据库）：掌握 MySQL 中的 JSON：第二部分 – 常用函数
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/207568.html
