外贸自助建站哪个好,手机网站模板网,如何网站做外贸生意,运城哪家做网站的公司好MySQL 5.7 版本后已支持 JSON 格式#xff0c;这虽是 MySQL 的一小步#xff0c;但可以说是程序开发的一大步#xff0c;再也不用将 JSON 内容塞到 VARCHAR 类型字段了#xff0c;程序设计也会变得更加灵活。网上大多只针对JSONObject 对象类型#xff0c;本文也将详解 JS… MySQL 5.7 版本后已支持 JSON 格式这虽是 MySQL 的一小步但可以说是程序开发的一大步再也不用将 JSON 内容塞到 VARCHAR 类型字段了程序设计也会变得更加灵活。网上大多只针对JSONObject 对象类型本文也将详解 JSONArray 数组类型。 1 定义
JSONJavaScript Object Notation是一种轻量级的数据交换格式它以键值对的方式存储数据并以大括号和方括号进行标记MySQL 的 JSON 格式字段可以存储 JSON 对象和数组。
JSONObject对象
{name: yinyu,age: 18
}
JSONArray数组
[yinyu, tom, jack]
而且对象和数组都可以嵌套其他对象或数组从而形成复杂的数据结构比如
[{name: yinyu,age: [18, 28, 38]},tom,jack
]
对于对象来说它的键只能为字符串值类型支持 nullstringbooleannumberobjectarray 等。
建表 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)
) engineinnodb default charsetutf8mb4;
插入数据 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对象字段名-’$数组的键名[数组索引]’
示例
selectjson_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 id2;
更新后的记录 ③ 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 id3;④ JSON_REPLACE() 替换
JSON_REPLACE() 的作用就是只替换/更新不插入针对JSONObject 对象类型则有 key 则替换无 key 则保持原样如果是 JSONArray 数组类型则根据索引进行替换规则以 JSONObject 对象类型为例。
规则JSON_REPLACE(json数据, $.属性名, 替换的值/数组, $.数组的键名[数组索引], 替换的值......)
示例接着上边第3条记录给 age 属性的值替换成 21tags 数组的第一个值替换成 “rap”
update test_json_tb t1
set json_obj json_replace(t1.json_obj,$.age, 21,$.tags[0], rap)
where id3; 注意如果该属性或数组索引不存在那么是不会进行替换的和 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 id3; ⑥ 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/alljson 查询规则)
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 总结
本文完善了 JSON 数组的相关操作大家如果有疑问都可以评论提出有不足之处请大家批评指正希望能多结识这方面的朋友共同学习、共同进步。
