1. 创建带json的表
create table jsontest(id int(11) auto_increment,name varchar(40) not null default '',chars json,primary key(id));
BLOB, TEXT, GEOMETRY,JSON字段不能有默认值
2. 插入数据
Mysql 的json分为两种json_array 和json_object.
json_array 在索引数据时用从0开始的下标进行索引，$表示整个json对象，例如：$[0]、$[1]
__json_object__在索引数据时用key进行索引，含有特殊字符的key要用"“括起来，比如$.“key”
//直接插入json对象
insert into jsontest (name,chars) values ("vilay",'{"age":100,"sex":"male"}');
//使用聚合函数-插入json_object
insert into jsontest (name,chars) values("jz",json_object("age",110,"sex","female"));
//使用聚合函数-插入json_array
insert into jsontest (name,chars) values("vvv",json_array(50,"male"));
3. 查询/获取
3.1 全部查询
mysql> select * from jsontest;
+----+-------+-------------------------------+
| id | name | chars |
+----+-------+-------------------------------+
| 1 | vilay | {"age": 100, "sex": "male"} |
| 2 | jz | {"age": 110, "sex": "female"} |
| 3 | vvv | [50, "male"] |
+----+-------+-------------------------------+
3.2 查询json某个key值
mysql> select name,chars->"$.sex" from jsontest;
+-------+----------------+
| name | chars->"$.sex" |
+-------+----------------+
| vilay | "male" |
| jz | "female" |
| vvv | NULL |
+-------+----------------+
3.3 去掉字段的引号(json_unquote/-»)
mysql> select name,json_unquote(chars->"$.sex") from jsontest;
+-------+------------------------------+
| name | json_unquote(chars->"$.sex") |
+-------+------------------------------+
| vilay | male |
| jz | female |
| vvv | NULL |
+-------+------------------------------+
3 rows in set (0.03 sec)
mysql> select name,chars->>"$.sex" from jsontest;
+-------+-----------------+
| name | chars->>"$.sex" |
+-------+-----------------+
| vilay | male |
| jz | female |
| vvv | NULL |
+-------+-----------------+
mysql> select name,chars->>"$[1]" as chars_sex from jsontest;
+-------+-----------+
| name | chars_sex |
+-------+-----------+
| vilay | NULL |
| jz | NULL |
| vvv | male |
+-------+-----------+
3 rows in set (0.03 sec)
3.4 json 字段作为查询条件
mysql> select id,json_extract(chars,"$.sex") from jsontest where json_extract(chars,'$.age') > 20;
+----+-----------------------------+
| id | json_extract(chars,"$.sex") |
+----+-----------------------------+
| 1 | "male" |
| 2 | "female" |
+----+-----------------------------+
3.5 json字段字符串比较
json字段与字符串不同，不能直接比较，需要通过cast
mysql> select * from jsontest where chars='{"age": 100, "sex": "male"}';
Empty set (0.03 sec)
mysql> select * from jsontest where chars=cast('{"age": 100, "sex": "male"}' as json);
+----+-------+-----------------------------+
| id | name | chars |
+----+-------+-----------------------------+
| 1 | vilay | {"age": 100, "sex": "male"} |
+----+-------+-----------------------------+
4. 索引
json字段本身不支持索引，但是有变通方式来支持索引，建立一个虚拟列，然后在虚拟列上建立索引。
alter table jsontest add column age tinyint(4) generated always as (chars->>'$.age') virtual;
mysql> select * from jsontest; +----+-------+-------------------------------+------+
| id | name | chars | age |
+----+-------+-------------------------------+------+
| 1 | vilay | {"age": 100, "sex": "male"} | 100 |
| 2 | jz | {"age": 110, "sex": "female"} | 110 |
| 4 | vilay | {"age": 80, "sex": "male"} | 80 |
+----+-------+-------------------------------+------+
show create table 查看是这样的
CREATE TABLE `jsontest` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(40) NOT NULL DEFAULT '',
`chars` json DEFAULT NULL,
`age` tinyint GENERATED ALWAYS AS (json_unquote(json_extract(`chars`,_utf8mb3'$.age'))) VIRTUAL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
5. 修改
5.1 json_insert
为json字段新增key，无法修改原来的
update jsontest set chars=json_insert(chars,'$.name','vilay');
mysql> select * from jsontest;
+----+-------+------------------------------------------------+------+
| id | name | chars | age |
+----+-------+------------------------------------------------+------+
| 1 | vilay | {"age": 100, "sex": "male", "name": "vilay"} | 100 |
| 2 | jz | {"age": 110, "sex": "female", "name": "vilay"} | 110 |
| 4 | vilay | {"age": 80, "sex": "male", "name": "vilay"} | 80 |
+----+-------+------------------------------------------------+------+
5.2 json_set
存在key则覆盖，不存在则新增
5.3 json_replace
修改字段某个key的值
update jsontest set chars=json_replace(chars,'$.info','you are right') where id=1;
5.4. json_remove
移除key
update jsontest set chars=json_remove(chars,'$.info');