实际项目中我们有时候对内或者对外部项目交互时存储数据有时候会存json格式的数据，mysql在5.7版本后支持了json格式数据的存储。

首先查看下mysql的版本是否支持，我的是8.0.x版本

 

1.创建一个张表，使用json格式字段

CREATE TABLE `user_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `details` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

 2.插入数据，json数据可以直接以json字符串格式插入，也可以使用函数JSON_OBJECT(使用函数时数据)

JSON 数据的书写格式是：{Key:Value}、{Key:Array}

JSON 值范围：

• 数字 （整数或浮点数）
• 字符串（在双引号中）
• 逻辑值（true 或 false）
• 数组 （在方括号[]中）
• 对象 （在花括号{}中）
• null

 以字符串方式插入数据：

insert into user_test(user_name, details) values
('mack', '{"phone":"17700001111", "sex":0,"age":35,"email":"mack@qq.com", "address":{"country":"CN","province":"浙江省","city":"金华"}}');

使用函数插入数据，注意key和value之间是逗号，而不是冒号了

如：JSON_OBJECT("phone", "16600001111",  "sex", 1,"age", 30,"email", "123@qq.com")

insert into user_test(user_name, details) values
('lily', JSON_OBJECT("phone","18500001111", "sex",1,"age",46,"email","1233@qq.com", "address",JSON_OBJECT("country","CN","province","上海市","city","上海市"))),
('jack', JSON_OBJECT("phone","18600001111", "sex",0,"age",6,"email","123@163.com", "address",JSON_OBJECT("country","CN","province","上海市","city","上海市"))),
('andi', JSON_OBJECT("phone","18600001111", "sex",0,"age",15,"email","123@qq.com", "address",JSON_OBJECT("country","CN","province","浙江省","city","杭州市"))),
('curre',JSON_OBJECT("phone","118800001111", "sex",1,"age",24,"email","123@qq.com", "address",JSON_OBJECT("country","CN","province","广东省","city","广州市"))),
('clid', JSON_OBJECT("phone","115600001111", "sex",0,"age",19,"email","123@qq.com", "address",JSON_OBJECT("country","CN","province","广东省","city","深圳市"))),
('scout',JSON_OBJECT("phone","13800001111", "sex",1,"age",70,"email","123@qq.com", "address",JSON_OBJECT("country","CN","province","福建省","city","厦门市"))),
('kk', JSON_OBJECT("phone","16600001111", "sex",1,"age",30,"email","123@qq.com")),
('lulu', JSON_OBJECT("phone","16600001111", "sex",1,"age",30,"email","123@qq.com","address",JSON_OBJECT("country","CN","province","湖北省","city","武汉市")));

3.查询数据

先查看下我们插入的数据

 查询json中的数据，有两种方式，一种是使用函数JSON_EXTRACT，另一种是使用符号

JSON_EXTRACT 查询json中的字符数据

select user_name,JSON_EXTRACT(details, '$.address.city') city from user_test;

 使用符号，-> 查询出的value带有双引号，->>查询出的value值没有双引号

 可以看出如果需要查询json中其它层级的数据可以使用$符号来代替整个json值，然后再一步步的获取对应的层级的key

select user_name,details -> '$.address.city' city from user_test;

 

select user_name,details ->> '$.address.city' city from user_test;

如果查询结果需要美化一下json的格式，可以使用JSON_PRETTY函数

select user_name,JSON_PRETTY(details) details from user_test;

3.使用json中的数据作为查询条件

select user_name, details from user_test
where details -> '$.address.city' = '上海市';

 JSON_CONTAINS 用来查询json中是否有数据，如果有返回1，没有返回0

select * from user_test where JSON_CONTAINS(details,'30','$.age');

 另外一种写法

select id,user_name,details ->> '$.age',JSON_CONTAINS(details,'30','$.age') 
from user_test;

 JSON_CONTAINS_PATH 判断json的key是否存在，存在返回1，不存在返回0

参数只能是one和all，one表示至少存在一个key，all表示同时存在

select id,user_name,details ->> '$.email', JSON_CONTAINS_PATH(details, 'one', '$.email') 
from user_test;

 id为3,6,7的json数据中没有email这个key,返回0，其它有的返回1

 

select id,user_name,details ->> '$.phone', 
details ->> '$.email', JSON_CONTAINS_PATH(details, 'one', '$.phone','$.email') 
from user_test;

id为3的json数据key既没有phone也没有email，所以返回0，其它的至少有一个有就返回1

 

select id,user_name,details ->> '$.phone', 
details ->> '$.email', JSON_CONTAINS_PATH(details, 'all', '$.phone','$.email') 
from user_test;

 all同时满足，既要有phon又要有email的key数据的返回1,其中一个没有或者都没有的返回0

 4.更新数据 JSON_SET ，JSON_INSERT，JSON_REPLACE

JSON_SET 更新如果key存在则覆盖，不存在则新增

update user_test set details = JSON_SET(details, '$.email', 'hello@163.com','$.phone','15500001111') 
where id = 3;

 JSON_INSERT 只是插入数据，不会替换已经存在的值

 

 

update user_test set details = JSON_INSERT(details, '$.contractPerson', '老王', '$.email', '147258@qq.com') 
where id = 3;

JSON_REPLACE 只是替换已经存在的值，不存在的不会管

 

 

update user_test set details = JSON_REPLACE(details, '$.email', '147258@qq.com', '$.habbit','football') 
where id = 3;

 

 总结：

JSON_INSERT: 只新增，不更新
JSON_REPLACE: 只更新，不新增
JSON_SET: 既新增，也更新

 5.删除数据

update user_test set details = JSON_REMOVE(details, '$.contractPerson') 
where id = 3;

 

6.其它函数

 JSON_KEYS()：获取JSON文档中的所有键key

 

select JSON_KEYS(details), JSON_KEYS(details ->> '$.address') 
from user_test where id = 3;

 

 JSON_LENGTH()：给出JSON文档中的key的个数

select JSON_LENGTH(details), JSON_LENGTH(details ->> '$.address') 
from user_test where id = 3;

 

 JSON_MERGE 合并

select JSON_MERGE('{"name": "pure"}','{"age":30}');

 

 JSON_UNQUOTE 去除双引号

select JSON_UNQUOTE('"hello word"');

 

 还有好多其它函数没有展示，只是列举了常用的一些