站酷网官网进入,crm客户管理系统多少钱,网页版whatsapp,wordpress游戏代练主题写在前面 需要注意#xff0c;5.7以上版本才支持#xff0c;但如果是生产环境需要使用的话#xff0c;尽量使用8.0版本#xff0c;因为8.0版本对json处理做了比较大的性能优化。你你可以使用select version();来查看版本信息。 本文看下MySQL的json处理。在正式开始让我们先…写在前面 需要注意5.7以上版本才支持但如果是生产环境需要使用的话尽量使用8.0版本因为8.0版本对json处理做了比较大的性能优化。你你可以使用select version();来查看版本信息。 本文看下MySQL的json处理。在正式开始让我们先来准备一些测试数据
CREATE TABLE dept (id int(11) NOT NULL,dept varchar(255) DEFAULT NULL,json_value json DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8;insert into dept VALUES(1,部门1,{deptName: 部门1, deptId: 1, deptLeaderId: 3});
insert into dept VALUES(2,部门2,{deptName: 部门2, deptId: 2, deptLeaderId: 4});
insert into dept VALUES(3,部门3,{deptName: 部门3, deptId: 3, deptLeaderId: 5});
insert into dept VALUES(4,部门4,{deptName: 部门4, deptId: 4, deptLeaderId: 5});
insert into dept VALUES(5,部门5,{deptName: 部门5, deptId: 5, deptLeaderId: 5});1json字段名-’$.json属性’
通过json字段名-’$.json属性’语法格式可以访问到json中某个key的值以如下查询方式看下。
1.1用在DQL查询结果中
mysql select id,json_value-$.deptLeaderId from dept where id2;
----------------------------------
| id | json_value-$.deptLeaderId |
----------------------------------
| 2 | 4 |
----------------------------------
1 row in set (0.01 sec)1.2用在DQL条件中
单条件
mysql select id from dept where json_value-$.deptLeaderId4;
----
| id |
----
| 2 |
----
1 row in set (0.01 sec)和普通字段组合查询
mysql select id from dept where json_value-$.deptLeaderId4 and id1;
----
| id |
----
| 2 |
----
1 row in set (0.01 sec)json多条件
mysql select id from dept where json_value-$.deptLeaderId4 and json_value-$.deptName like %部门%;
----
| id |
----
| 2 |
----
1 row in set (0.00 sec)1.3用在DQL关联查询中
先准备数据
CREATE TABLE dept_leader (id int(11) NOT NULL,leaderName varchar(255) DEFAULT NULL,json_value json DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB DEFAULT CHARSETutf8;insert into dept_leader VALUES(1,leader1,{name: 王一, id: 1, leaderId: 1});
insert into dept_leader VALUES(2,leader2,{name: 王二, id: 2, leaderId: 3});
insert into dept_leader VALUES(3,leader3,{name: 王三, id: 3, leaderId: 4});
insert into dept_leader VALUES(4,leader4,{name: 王四, id: 4, leaderId: 5});
insert into dept_leader VALUES(5,leader5,{name: 王五, id: 5, leaderId: 5});关联查询
mysql SELECT dept.id,dept_leader.id from dept,dept_leader WHERE dept.json_value-$.deptLeaderIddept_leader.json_value-$.id ;
--------
| id | id |
--------
| 1 | 3 |
| 2 | 4 |
| 3 | 5 |
| 4 | 5 |
| 5 | 5 |
| 6 | 5 |
| 7 | 5 |
| 8 | 5 |
--------
8 rows in set (0.00 sec)2json_extract
语法格式json_extract(字段名,$.json字段名),用来从json字段中提取值如下
mysql select id,json_extract(json_value,$.deptName) as deptName from dept;
----------------------------------
| id | deptName |
----------------------------------
| 1 | 部门1 |
...mysql SELECT * FROM dept WHERE JSON_EXTRACT(json_value,$.deptName) like %部门%\G
*************************** 1. row ***************************id: 1dept: 部门1
json_value: {deptId: 1, deptName: 部门1, deptLeaderId: 3}
*************************** 2. row ***************************id: 2dept: 部门2
json_value: {deptId: 2, newData: 新增的数据, deptName: 新增的部门1111, deptLeaderId: 4}
...cx 3JSON_OBJECT()
语法格式JSON_OBJECT(k,v[,k,v]),用来转换指定数据为json object,如下
mysql select json_object(name, 张三, age, 99);
------------------------------------------
| json_object(name, 张三, age, 99) |
------------------------------------------
| {age: 99, name: 张三} |
------------------------------------------
1 row in set (0.01 sec)4JSON_CONTAINS()
语法JSON_CONTAINS(target, candidate[, path])用来判断json格式中是否包含指定子对象其中子对象需要通过json_object()生成如下
mysql select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT(deptName,部门5))- ;
--------------------------------------------------------------------------
| id | dept | json_value |
--------------------------------------------------------------------------
| 5 | 部门5 | {deptId: 5, deptName: 部门5, deptLeaderId: 5} |
--------------------------------------------------------------------------
1 row in set (0.01 sec)注意需要使用json_object转换为object。 添加如下数据看一个嵌套json对象的例子
insert into dept VALUES(6,部门9,{deptName: {dept:de,depp:dd}, deptId: 5, deptLeaderId: 5});判断嵌套对象是否存在
mysql SELECT * from dept WHERE JSON_CONTAINS(json_value-$.deptName,JSON_OBJECT(depp,dd));
---------------------------------------------------------------------------------------------
| id | dept | json_value |
---------------------------------------------------------------------------------------------
| 6 | 部门9 | {deptId: 5, deptName: {depp: dd, dept: de}, deptLeaderId: 5} |
---------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql SELECT * from ( SELECT *,json_value-$.deptName as deptName FROM dept ) t WHERE JSON_CONTAINS(deptName,JSON_OBJECT(depp,dd));
...5JSON_ARRAY()
创建json数组如下
mysql select json_array(pingpong,羽毛球,篮球);
---------------------------------------------
| json_array(pingpong,羽毛球,篮球) |
---------------------------------------------
| [pingpong, 羽毛球, 篮球] |
---------------------------------------------mysql select json_array(json_object(name, 张三), 篮球, json_object(name, 李四));
------------------------------------------------------------------------------------
| json_array(json_object(name, 张三), 篮球, json_object(name, 李四)) |
------------------------------------------------------------------------------------
| [{name: 张三}, 篮球, {name: 李四}] |
------------------------------------------------------------------------------------
1 row in set (0.00 sec)json格式化看下
5.1判断数组是否是否包含某子数组
准备数据
insert into dept VALUES(7,部门9,{deptName: [1,2,3], deptId: 5, deptLeaderId: 5});
insert into dept VALUES(7,部门9,{deptName: [5,6,7], deptId: 5, deptLeaderId: 5});mysql SELECT * from dept WHERE JSON_CONTAINS(json_value-$.deptName,JSON_ARRAY(1));
--------------------------------------------------------------------------------
| id | dept | json_value |
--------------------------------------------------------------------------------
| 7 | 部门9 | {deptId: 5, deptName: [1, 2, 3], deptLeaderId: 5} |
--------------------------------------------------------------------------------
1 row in set (0.05 sec)
6JSON_TYPE()
获取类型
mysql SELECT json_value-$.deptName ,JSON_TYPE(json_value-$.deptName) as type from dept;
--------------------------------------
| json_value-$.deptName | type |
--------------------------------------
| 部门1 | STRING |
| 新增的部门1111 | STRING |
| 部门3 | STRING |
| 部门4 | STRING |
| 部门5 | STRING |
| {depp: dd, dept: de} | OBJECT |
| [1, 2, 3] | ARRAY |
| [5, 6, 7] | ARRAY |
--------------------------------------7JSON_KEYS()
获取文档中所有的key如下
mysql SELECT JSON_KEYS(json_value),json_value FROM dept where id2\G
*************************** 1. row ***************************
JSON_KEYS(json_value): [deptId, newData, deptName, deptLeaderId]json_value: {deptId: 2, newData: 新增的数据, deptName: 新增的部门1111, deptLeaderId: 4}
1 row in set (0.01 sec)8JSON_SET()
更新或者是添加kv无责插入有则更新语法格式JSON_SET(json_doc, path, val[, path, val] …),如下
key存在时更新
mysql select * from dept where id1;
--------------------------------------------------------------------------
| id | dept | json_value |
--------------------------------------------------------------------------
| 1 | 部门1 | {deptId: 1, deptName: 部门1, deptLeaderId: 3} |
--------------------------------------------------------------------------
1 row in set (0.00 sec)mysql update dept set json_valueJSON_SET(json_value, $.deptName, 部门1_更新后) where id1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from dept where id1;
------------------------------------------------------------------------------------
| id | dept | json_value |
------------------------------------------------------------------------------------
| 1 | 部门1 | {deptId: 1, deptName: 部门1_更新后, deptLeaderId: 3} |
------------------------------------------------------------------------------------
1 row in set (0.00 sec)key不存在时插入
mysql select * from dept where id1;
------------------------------------------------------------------------------------
| id | dept | json_value |
------------------------------------------------------------------------------------
| 1 | 部门1 | {deptId: 1, deptName: 部门1_更新后, deptLeaderId: 3} |
------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql update dept set json_valueJSON_SET(json_value, $.deptAddr, 赢创动力4楼) where id1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from dept where id1;
--------------------------------------------------------------------------------------------------------------------
| id | dept | json_value |
--------------------------------------------------------------------------------------------------------------------
| 1 | 部门1 | {deptId: 1, deptAddr: 赢创动力4楼, deptName: 部门1_更新后, deptLeaderId: 3} |
--------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)一个key不存在一个key存在
mysql select * from dept where id1;
--------------------------------------------------------------------------------------------------------------------
| id | dept | json_value |
--------------------------------------------------------------------------------------------------------------------
| 1 | 部门1 | {deptId: 1, deptAddr: 赢创动力4楼, deptName: 部门1_更新后, deptLeaderId: 3} |
--------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)mysql update dept set json_valueJSON_SET(json_value, $.deptAddr, 八维学院, $.newKey, newKey的value) where id1;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from dept where id1; --------------------------------------------------------------------------------------------------------------------------------------------
| id | dept | json_value |
--------------------------------------------------------------------------------------------------------------------------------------------
| 1 | 部门1 | {deptId: 1, newKey: newKey的value, deptAddr: 八维学院, deptName: 部门1_更新后, deptLeaderId: 3} |
--------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)9JSON_INSERT()
语法格式JSON_INSERT(json_doc, path, val[, path, val] …),插入文档当值已经存在时忽略如下
mysql select * from dept where id2;
--------------------------------------------------------------------------
| id | dept | json_value |
--------------------------------------------------------------------------
| 2 | 部门2 | {deptId: 2, deptName: 部门2, deptLeaderId: 4} |
--------------------------------------------------------------------------
1 row in set (0.01 sec)mysql update dept set json_valueJSON_INSERT(json_value, $.deptId, 已存在不更新, $.addr, 西二旗) where id2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from dept where id2; -----------------------------------------------------------------------------------------------
| id | dept | json_value |
-----------------------------------------------------------------------------------------------
| 2 | 部门2 | {addr: 西二旗, deptId: 2, deptName: 部门2, deptLeaderId: 4} |
-----------------------------------------------------------------------------------------------
1 row in set (0.00 sec)10JSON_REPLACE()
语法格式JSON_REPLACE(json_doc, path, val[, path, val] …)更新而不新增文档如下
mysql update dept set json_valueJSON_REPLACE(json_value, $.deptName, 部门2_新名称, $.notExitsKey, 不会替换因为不存在) where id2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from dept where id2; ---------------------------------------------------------------------------------------------------------
| id | dept | json_value |
---------------------------------------------------------------------------------------------------------
| 2 | 部门2 | {addr: 西二旗, deptId: 2, deptName: 部门2_新名称, deptLeaderId: 4} |
---------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)11JSON_REMOVE()
语法格式JSON_REMOVE(json_doc, path[, path] …),删除文档
mysql select * from dept where id3;
--------------------------------------------------------------------------
| id | dept | json_value |
--------------------------------------------------------------------------
| 3 | 部门3 | {deptId: 3, deptName: 部门3, deptLeaderId: 5} |
--------------------------------------------------------------------------
1 row in set (0.00 sec)mysql update dept set json_valueJSON_REMOVE(json_value, $.depeName, $.deptLeaderId) where id3;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql select * from dept where id3;
-----------------------------------------------------
| id | dept | json_value |
-----------------------------------------------------
| 3 | 部门3 | {deptId: 3, deptName: 部门3} |
-----------------------------------------------------
1 row in set (0.00 sec)12JSON_SEARCH 语法格式JSON_SEARCH(json_docone_or_allsearch_str [escape_char [path] ...]), 获取指定文档出现的位置如果没有则返回NULL。
12.1官方例子
准备数据
mysql SET j [abc, [{k: 10}, def], {x:abc}, {y:bcd}];
Query OK, 0 rows affected (0.00 sec)mysql select j;
---------------------------------------------------------
| j |
---------------------------------------------------------
| [abc, [{k: 10}, def], {x:abc}, {y:bcd}] |
---------------------------------------------------------
1 row in set (0.01 sec)查询
mysql SELECT JSON_SEARCH(j, one, abc); // 第二参数查询终止条件设置为one即只查询一个
-------------------------------
| JSON_SEARCH(j, one, abc) |
-------------------------------
| $[0] |
-------------------------------
1 row in set (0.01 sec)mysql SELECT JSON_SEARCH(j, all, abc); // 第二个参数查询终止条件设置为all即查询所有匹配的
-------------------------------
| JSON_SEARCH(j, all, abc) |
-------------------------------
| [$[0], $[2].x] |
-------------------------------
1 row in set (0.01 sec)mysql SELECT JSON_SEARCH(j, all, ghi); // 全局匹配不存在的会返回NULL
-------------------------------
| JSON_SEARCH(j, all, ghi) |
-------------------------------
| NULL |
-------------------------------
1 row in set (0.00 sec)mysql SELECT JSON_SEARCH(j, all, 10); // 全局匹配10返回具体位置的具体key
------------------------------
| JSON_SEARCH(j, all, 10) |
------------------------------
| $[1][0].k |
------------------------------
1 row in set (0.01 sec)指定路径搜索
mysql SELECT JSON_SEARCH(j, all, 10, NULL, $);
-----------------------------------------
| JSON_SEARCH(j, all, 10, NULL, $) |
-----------------------------------------
| $[1][0].k |
-----------------------------------------mysql SELECT JSON_SEARCH(j, all, 10, NULL, $[*]);
--------------------------------------------
| JSON_SEARCH(j, all, 10, NULL, $[*]) |
--------------------------------------------
| $[1][0].k |
--------------------------------------------mysql SELECT JSON_SEARCH(j, all, 10, NULL, $**.k);
---------------------------------------------
| JSON_SEARCH(j, all, 10, NULL, $**.k) |
---------------------------------------------
| $[1][0].k |
---------------------------------------------mysql SELECT JSON_SEARCH(j, all, 10, NULL, $[*][0].k);
-------------------------------------------------
| JSON_SEARCH(j, all, 10, NULL, $[*][0].k) |
-------------------------------------------------
| $[1][0].k |
-------------------------------------------------mysql SELECT JSON_SEARCH(j, all, 10, NULL, $[1]);
--------------------------------------------
| JSON_SEARCH(j, all, 10, NULL, $[1]) |
--------------------------------------------
| $[1][0].k |
--------------------------------------------#指定搜索路径为数组中第二个元素内的第一个元素
mysql SELECT JSON_SEARCH(j, all, 10, NULL, $[1][0]);
-----------------------------------------------
| JSON_SEARCH(j, all, 10, NULL, $[1][0]) |
-----------------------------------------------
| $[1][0].k |
-----------------------------------------------mysql SELECT JSON_SEARCH(j, all, abc, NULL, $[2]);
---------------------------------------------
| JSON_SEARCH(j, all, abc, NULL, $[2]) |
---------------------------------------------
| $[2].x |
---------------------------------------------模糊匹配
mysql SELECT JSON_SEARCH(j, all, %a%);
-------------------------------
| JSON_SEARCH(j, all, %a%) |
-------------------------------
| [$[0], $[2].x] |
-------------------------------mysql SELECT JSON_SEARCH(j, all, %b%);
-------------------------------
| JSON_SEARCH(j, all, %b%) |
-------------------------------
| [$[0], $[2].x, $[3].y] |
-------------------------------# 指定搜索路径$[0] abc
mysql SELECT JSON_SEARCH(j, all, %b%, NULL, $[0]);
---------------------------------------------
| JSON_SEARCH(j, all, %b%, NULL, $[0]) |
---------------------------------------------
| $[0] |
---------------------------------------------# $[2] {x:abc}
mysql SELECT JSON_SEARCH(j, all, %b%, NULL, $[2]);
---------------------------------------------
| JSON_SEARCH(j, all, %b%, NULL, $[2]) |
---------------------------------------------
| $[2].x |
---------------------------------------------# $[1] [{k: 10}, def] 模糊匹配无结果
mysql SELECT JSON_SEARCH(j, all, %b%, NULL, $[1]);
---------------------------------------------
| JSON_SEARCH(j, all, %b%, NULL, $[1]) |
---------------------------------------------
| NULL |
---------------------------------------------mysql SELECT JSON_SEARCH(j, all, %b%, , $[1]);
-------------------------------------------
| JSON_SEARCH(j, all, %b%, , $[1]) |
-------------------------------------------
| NULL |
-------------------------------------------mysql SELECT JSON_SEARCH(j, all, %b%, , $[3]);
-------------------------------------------
| JSON_SEARCH(j, all, %b%, , $[3]) |
-------------------------------------------
| $[3].y |
-------------------------------------------12.2实际例子
在日常开发过程中如果需要使用JSON_SEARCH查询的话需要搭配IS NOT NULL来获取符合条件的数据如果是返回是NULL则说明不存在反之存在所以对于返回的具体位置信息我们一般是使用不到的准备数据如下
Create Table: CREATE TABLE user (id int(11) unsigned NOT NULL AUTO_INCREMENT,name varchar(64) NOT NULL COMMENT 名字,age int(4) unsigned NOT NULL COMMENT 年龄,info text COMMENT 补充信息,PRIMARY KEY (id),UNIQUE KEY name (name)
) ENGINEInnoDB DEFAULT CHARSETutf8 COMMENT用户表INSERT INTO user (name, age, info) VALUES (suhw, 23, {phone:12312123434,language:[c,java,go]});
INSERT INTO user (name, age, info) VALUES (bob, 20, {phone:18912123434,language:[c,c,go,php]});查询会java和go语言的数据
mysql select * from user where json_search(info, all, go) is not null and json_search(info, all, java) is not null;
-----------------------------------------------------------------------
| id | name | age | info |
-----------------------------------------------------------------------
| 1 | suhw | 23 | {phone:12312123434,language:[c,java,go]} |
-----------------------------------------------------------------------
1 row in set (0.00 sec)写在后面
参考文章列表
【MySQL】对JSON数据操作全网最全 。
MySQL - json_search 小结 。
