MySQL 5.7 版本后已支持 JSON 格式，这虽是 MySQL 的一小步，但可以说是程序开发的一大步，再也不用将 JSON 内容塞到 VARCHAR 类型字段了，程序设计也会变得更加灵活。网上大多只针对JSONObject 对象类型，本文也将详解 JSONArray 数组类型。
1 定义
JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，它以键值对的方式存储数据，并以大括号和方括号进行标记，MySQL 的 JSON 格式字段可以存储 JSON 对象和数组。
JSONObject：对象
{
"name": "yinyu",
"age": "18"
}JSONArray：数组
["yinyu", "tom", "jack"]而且对象和数组都可以嵌套其他对象或数组，从而形成复杂的数据结构，比如 👇
[
{
"name": "yinyu",
"age": [18, 28, 38]
},
"tom",
"jack"
]对于对象来说，它的键只能为字符串，值类型支持 null，string，boolean，number，object，array 等。
建表 SQL
在创建表时，可以指定字段类型为 JSON ，不过无需指定 JSON 类型的长度，因为默认值只能为 null 。此外，JSON 字段类型可以根据实际存储的数据自动推断是对象还是数组结构，因此无需显式指定。
create table `test_json_tb` (
`id` bigint(20) not null auto_increment,
`json_obj` json default null comment 'json 对象字段',
`json_arr` json default null comment 'json 数组字段',
primary key (`id`)
) engine=innodb default charset=utf8mb4;插入数据 SQL
insert into test_json_tb(json_obj, json_arr) values
('{"name":"yinyu", "age":18, "tags":["rap", "dance"]}', '["yinyu", "yinyu1", "yinyu2"]'),
('{"name":"tom", "age":19, "tags":["rap"]}', '["tom"]'),
('{"name":"jack", "age":20, "tags":["dance"]}', '[{"name":"jack"}, {"age":"20"}]');建表如图 👇，接下来将使用该数据表来教学 MYSQL 的 JSON 常用函数。
2 原生查询
原生查询规则
针对 JSONObject 对象的规则：json对象字段名->’$.json属性名’
针对 JSONArray 数组的规则：json数组字段名->’$[数组索引]’ or json对象字段名->’$数组的键名[数组索引]’
示例：
select
json_obj->'$.name' name,
json_obj->'$.tags[0]' tags0,
json_arr->'$[0]' arr0
from test_json_tb;
可以看到查询到的字段字符串类型有个双引号，那么可以通过将 -> 替换成 ->> 去除，转义符同时也会去除。
条件查询
那么我们使用原生查询规则来试下条件查询，比如 👇
select *
from test_json_tb
where json_obj->'$.name' = 'yinyu';由于 JSON 字段的模糊搜索仅支持 %str% 格式，因此它的模糊搜索时索引无效：
select *
from test_json_tb
where json_obj->'$.name' like '%yin%';对于联表查询、多条件查询均是支持的~
3 常用函数
JSONObject 对象类型和 JSONArray 数组类型基本上都可以使用以下函数，而网上攻略大多只针对JSONObject 对象类型，因此我补充了 JSONArray 数组类型的相关规则和示例。
① JSON_EXTRACT() 提取
规则和原生查询类型~
针对 JSONObject 对象类型，规则：json_extract(对象字段名, '$.属性名')
针对 JSONArray 数组类型，规则：json_extract(数组字段名, '$[数组索引]') or json_extract(对象字段名, '$.数组的键名[数组索引]')
select id,
json_extract(json_obj,'$.name') as name,
json_extract(json_obj,'$.tags[1]') as tags1,
json_extract(json_arr,'$[0]') as arr0
from test_json_tb;
若想去除双引号，可以使用 JSON_UNQUOTE(JSON_EXTRACT())。
② JSON_SET() 更新
将数据插入到 JSON 格式字段中，如果是 JSONObject 对象类型，则有 key 则替换，无 key 则新增；如果是 JSONArray 数组类型，则根据索引进行替换或新增，规则以 JSONObject 对象类型为例。
规则：JSON_SET(json数据, '$.属性名', '更新/插入的值/数组', '$.数组的键名[数组索引]', '更新/插入的值'......)
示例：比如修改第2条数据，将他的 age 修改为 20，并且 tags 增加一个 “dance”：
update test_json_tb t1
set json_obj = json_set(t1.json_obj,'$.age',20, '$.tags[1]', 'dance')
where id=2;更新后的记录 👇
③ JSON_INSERT() 插入
和 JSON_SET() 类似，但 JSON_INSERT() 只插入不更新（有 key 保持原样）。
规则：JSON_INSERT(json数据, '$.属性名', '更新/插入的值/数组', '$.数组的键名[数组索引]', '更新/插入的值'......)
示例：比如给第3条数据新增一个 age 属性和 from 属性，但是执行 sql 后会发现 age 属性插入是不生效的，这是因为 age 属性已存在，而 from 属性新增成功。
update test_json_tb t1
set json_obj = json_insert(t1.json_obj,'$.age', 21,'$.from', 'china')
where id=3;
④ JSON_REPLACE() 替换
JSON_REPLACE() 的作用就是只替换/更新，不插入，针对JSONObject 对象类型，则有 key 则替换，无 key 则保持原样；如果是 JSONArray 数组类型，则根据索引进行替换，规则以 JSONObject 对象类型为例。
规则：JSON_REPLACE(json数据, '$.属性名', '替换的值/数组', '$.数组的键名[数组索引]', '替换的值'......)
示例：接着上边第3条记录，给 age 属性的值替换成 21，tags 数组的第一个值替换成 “rap”
update test_json_tb t1
set json_obj = json_replace(t1.json_obj,'$.age', 21,'$.tags[0]', 'rap')
where id=3;
注意：如果该属性或数组索引不存在，那么是不会进行替换的，和 JSON_INSERT() 正好是反着来，而 JSON_SET() 集合了这两者。
⑤ JSON_REMOVE() 移除
顾名思义，该函数的作用是从删除 JSON 数据。
规则：JSON_REMOVE(json数据, '$.属性名', '$.数组的键名[数组索引]')
示例：之前不是给第3条记录增加了 from 属性么，我们来移除它，并且移除 json_arr 字段的第二个属性。
update test_json_tb t1
set json_obj = json_remove(t1.json_obj,'$.from'),
json_arr = json_remove(t1.json_arr,'$[1]')
where id=3;
⑥ JSON_OBJECT()、JSON_ARRAY()
这两个函数分别对应 JSON 对象和 JSON 数组，分别用来创建 JSON 对象和 JSON 数组，可以搭配 JSON_SET()、JSON_INSERT()、JSON_REPLACE() 等函数，也可用于查询等操作。
规则：JSON_OBJECT(键名, 值, 键名, 值......)、JSON_ARRAY(元素、元素、元素......)
接下来以插表 SQL 作为示例：
insert into test_json_tb(json_obj, json_arr) values
(json_object('age',22,'name','mike','tags',json_array('sing')),
json_array(11,22,33));
⑦ JSON_CONTAINS() 是否包含
校验 JSON 数据中是否包含特定值（可以是属性、对象、数组、数组索引等）。
规则：JSON_CONTAINS(target, candidate[, path])，详情看示例
示例1：查询出 json_obj 字段中包含 age = 18 的记录
select * from test_json_tb
where json_contains(json_obj, json_object('age',18))
示例2：查询出 json_arr 字段中包含 11 的记录
select * from test_json_tb
where json_contains(json_arr, json_array(11))示例3：查询出 json_obj 字段中 tags 属性中同时包含 “rap” 和 “dance” 的记录
select * from test_json_tb
where json_contains(json_obj, json_array('rap','dance'), '$.tags')⑧ JSON_TYPE(）类型
查询某个 JSON 字段属性类型。
规则：JSON_TYPE(原生查询或 JSON_EXTRACT())
示例：查看 json_obj 字段中 tags 属性的类型
select json_obj->'$.tags' ,json_type(json_obj->'$.tags') as type
from test_json_tb
-- or
select json_extract(json_obj,'$.tags') ,
json_type(json_extract(json_obj,'$.tags')) as type
from test_json_tb 
⑨ JSON_KEYS() 键名
查询 JSON 数据中的所有键/key，返回列表，针对 JSON 对象，因为数组是没有键的。
规则：JSON_KEYS(json_value)
select json_keys(json_obj)
from test_json_tb 
⑩ JSON_SEARCH() 深度查询
JSON_SEARCH() 函数，简单来说就是加强版查询，其实 JSON_EXTRACT() + limit 等 SQL也能达到效果。
规则：JSON_SEARCH(json数据，one/all，json 查询规则)
one 是返回1条记录，all 是返回符合条件的所有记录
示例1：查询路径--以 json 对象为例
select json_search(json_obj,'all','yinyu', null)
from test_json_tb
他会返回符合条件的记录里的路径，若是路径编写有困难，那么可以试下这个深度查询！
null -- 若搜索内容不存在，则返回 NULL，第三个参数为值。
示例2：条件查询--针对 json 对象（第五个参数为路径，条件查询时搭配 is not null）
查询 json_obj 字段中 name 属性为 “yinyu” 的记录
select *
from test_json_tb
where json_search(json_obj,'all','yinyu', null,'$.name') is not null
示例3：条件查询--针对 json 数组
查询 json_arr 字段中第一个元素为 “yinyu” 的记录
select *
from test_json_tb
where json_search(json_arr,'all','yinyu', null,'$[0]') is not null
示例4：模糊查询
select *
from test_json_tb
where json_search(json_obj,'all','%t%', null,'$.name') is not null
文章来源：https://www.toymoban.com/news/detail-662940.html
总结
本文完善了 JSON 数组的相关操作，大家如果有疑问都可以评论提出，有不足之处请大家批评指正，希望能多结识这方面的朋友，共同学习、共同进步。文章来源地址https://www.toymoban.com/news/detail-662940.html
到了这里，关于【MySQL】JSON 格式字段处理的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
