环境准备
mysql版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.29 |
+-----------+
1 row in set (0.00 sec)
建表
CREATE TABLE `tb_book` (
`id` int primary key AUTO_INCREMENT COMMENT '主键id',
`title` varchar(20) not null COMMENT '书名',
`persons` json not null COMMENT '主要人物列表',
`attrs` json not null COMMENT '属性列表',
`info` json not null COMMENT '详细信息'
) ENGINE=InnoDB COMMENT='图书表';
测试数据
insert into tb_book (title, persons, attrs, info) values (
'红楼梦',
'["贾宝玉", "林黛玉"]',
'[{"label": "朝代", "value": "清代"}]',
'{"author": "曹雪芹"}'
);
insert into tb_book (title, persons, attrs, info) values (
'水浒传',
'["宋江", "李逵"]',
'[{"label": "朝代", "value": "宋代"}]',
'{"author": "施耐庵"}'
);
insert into tb_book (title, persons, attrs, info) values (
'三国演义',
'["曹操", "孙权", "刘备"]',
'[{"label": "朝代", "value": "汉代"}]',
'{"author": "罗贯中"}'
);
insert into tb_book (title, persons, attrs, info) values (
'西游记',
'["孙悟空", "唐僧", "猪八戒", "沙悟净"]',
'[{"label": "朝代", "value": "明代"}]',
'{"author": "吴承恩"}'
);
查看测试数据
mysql> select * from tb_book;
+----+--------------+--------------+---------+-----------+
| id | title | persons | attrs | info |
+----+--------------+--------------+---------+-----------+
| 1 | 红楼梦 | ["贾宝玉", "林黛玉"]| [{"label": "朝代", "value": "清代"}]| {"author": "曹雪芹"}|
| 2 | 水浒传 | ["宋江", "李逵"]| [{"label": "朝代", "value": "宋代"}]| {"author": "施耐庵"}|
| 3 | 三国演义| ["曹操", "孙权", "刘备"] | [{"label": "朝代", "value": "汉代"}]| {"author": "罗贯中"}|
| 4 | 西游记 | ["孙悟空", "唐僧", "猪八戒", "沙悟净"]| [{"label": "朝代", "value": "明代"}] | {"author": "吴承恩"}|
+----+--------------+--------------+---------+-----------+
查询示例
对象查询
mysql> select id, title, info from tb_book where info->'$.author' ='吴承恩';
# 或者
mysql> select id, title, info from tb_book where JSON_EXTRACT(info, '$.author') ='吴承恩';
+----+-----------+-------------------------+
| id | title | info |
+----+-----------+-------------------------+
| 4 | 西游记 | {"author": "吴承恩"} |
+----+-----------+-------------------------+
1 row in set (0.00 sec)
普通列表查询
mysql> select id, title, persons from tb_book where '宋江' MEMBER OF(persons);
+----+-----------+----------------------+
| id | title | persons |
+----+-----------+----------------------+
| 2 | 水浒传 | ["宋江", "李逵"] |
+----+-----------+----------------------+
1 row in set (0.00 sec)
对象列表查询
mysql> select id, title, attrs from tb_book where JSON_CONTAINS(attrs, JSON_OBJECT('value', '汉代'));
+----+--------------+------------------------------------------+
| id | title | attrs |
+----+--------------+------------------------------------------+
| 3 | 三国演义 | [{"label": "朝代", "value": "汉代"}] |
+----+--------------+------------------------------------------+
1 row in set (0.00 sec)
参考
MySQL：JSON字段List成员检查
mysql json类型数据查询
————————————————
版权声明：本文为CSDN博主「彭世瑜」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
