Mysql条件查询json数据

Mysql8条件查询JSON

1、Mysql数据库对应数据

2、查询条件

#模糊查询JSON
select * from test where json_value->'$.name' LIKE '%是%'
#不等于空
SELECT * FROM test WHERE json_value->'$.name' !=""
#不等于空，当前属性不为空
SELECT * FROM test WHERE json_value->'$.name' IS NOT NULL
#等于空 
SELECT * FROM test WHERE json_value->'$.name' =""
#等于空，当前属性为空
SELECT * FROM test WHERE json_value->'$.name' IS NULL
#大于等于
SELECT * FROM test WHERE json_value->'$.age' >='25'
#小于等于
SELECT * FROM test WHERE json_value->'$.age' <='24' AND json_value->'$.name' LIKE '%是%' LIMIT 0,10
#等于
SELECT * FROM test WHERE json_value->'$.age' ='24'
#排序、条件
SELECT * FROM test WHERE business_code='1' AND json_value->'$.age' >='24' ORDER BY create_time DESC LIMIT 0,20
#IN查询
SELECT * FROM test WHERE json_value->'$.age' in ("24")
#左Like查询  没找到使用方式
SELECT * FROM test WHERE json_value->'$.name' LIKE '%四' 
#右Like查询  没找到使用方式
SELECT * FROM test WHERE json_value->'$.name' LIKE '是%'

3、创建索引

#创建普通索引
alter table `sys_defined_form_data` add index text_name(text_name)
#唯一索引
ALTER TABLE `sys_defined_form_data` ADD UNIQUE (`id`)
#Text字段添加索引
ALTER TABLE `sys_defined_form_data` ADD FULLTEXT(data_json)
#添加虚拟列
alter table `sys_defined_form_data`  add username varchar(20) generated always as (data_json->'$.username'),add birthday varchar(20) generated always as (data_json->'$.birthday')
ALTER TABLE `sys_defined_form_data`  ADD username_virtual  varchar (32) GENERATED ALWAYS  AS (json_extract(data_json,  '$.username' )) VIRTUAL;
#虚拟列添加索引
alter table `sys_defined_form_data` add index username_virtual(username_virtual);

4、数组参数

查询条件

如果你JSON一面存的是数组的话，就要用对应的函数了。

SELECT * FROM sys_defined_form_data WHERE JSON_CONTAINS(data_json,JSON_OBJECT('id','1'))

Mysql相关JSON函数