MySQL8操作JSON

MySQL是一种关系型数据库，适合存储结构化数据，而JSON是一种非结构化格式，通常使用mongoDB等存储。但是在5.7之后，MySQL也开始支持JSON操作。此文章使用的版本是8.0.30

使用JSON数据类型的优点

不用预创建字段: 字段可以无限扩展，更加灵活；
处理稀疏字段: 避免了某些很少使用字段的NULL值，避免了冗余存储；
支持索引。支持JSON之前，可以使用VARCHAR存储，但是不支持索引等查询优化

实战

创建测试表

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  `details` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

插入数据

insert into user(name, details) values ('jack', '{"phone": "13100000000", "age": 30, "address": {"country": "CN", "province": "浙江省", "city": "杭州市"}}');
insert into user(name, details) values ('Linda', '{"phone": "music", "age": 25, "address": {"country": "CN", "province": "河北省"}}');
insert into user(name, details) values ('Lily', '{"age": 30, "address": {"country": "CN", "province": "广东省", "city": "广州市"}}');
insert into user(name, details) values ('Lisa', '{"likes": ["music", "basketball"], "likes2": ["music"]}');
insert into user(name, details) values ('Kobe', '{"likes": ["art"]}');

完成数据准备之后的数据如图:

查询

使用JSON_EXTRACT查询JSON中的某个字段

1	select name, JSON_EXTRACT(details, '$.address.city') city from user;

使用符号，同样可以替代JSON_EXTRACT函数的作用。->查询出的带有双引号，->>查询出的没有双引号

使用JSON中的字段作为查询条件

1	select name, details from user where details -> '$.address.city' = '杭州市';

JSON_CONTAINS函数，查询JSON文档是否在指定path包含指定的数据，包含则返回1，否则返回0；如果有参数为NULL或者path不存在，则返回NULL

使用方式：JSON_CONTAINS(json_doc, val [,path]), 第二个参数'val'表示val为整型，'"val"'表示val为字符串类型，第二个参数最外层必须使用''

例子2中的查询可以等同于：

1	select name, details from user where JSON_CONTAINS(details, '"杭州市"', '$.address.city');

查询爱好包含音乐的用户信息

1	select name, details from user where JSON_CONTAINS(details, '"music"', '$.likes');

1	select JSON_CONTAINS(details, '"杭州市"', '$.address.city') from user;

JSON_SEARCH函数，返回字符串匹配的路径

使用方式: JSON_SEARCH(json_doc, one_or_all, search_str[, escape_char[, path] ...])

one: The search terminates after the first match and returns one path string. It is undefined which match is considered first. 搜索在第一次匹配后终止，并返回一个路径字符串
all: The search returns all matching path strings such that no duplicate paths are included. If there are multiple strings, they are autowrapped as an array. The order of the array elements is undefined. 搜索返回所有匹配的路径字符串

1	select json_search(details, 'all', 'music') from user;

1	select json_search(details, 'one', 'music') from user;

更新

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

1 2	select * from user where id = 1; update user set details = JSON_SET(details, '$.phone', '13127366399', '$.email', 'wwyknight@163.com') where id = 1;