一个拥有JSON格式的数据表与另外的表进行关联查询

create table users
(
id INT not null comment 'ID',
createDate DATETIME not null comment '创建日期',
modifyDate DATETIME not null comment '最后修改日期',
isEnabled BIT not null comment '是否启用',
isLocked BIT not null comment '是否锁定',
lastLoginDate DATETIME comment '最后登录日期',
lastLoginIp VARCHAR(255) comment '最后登录IP',
lockDate DATETIME comment '锁定日期'
)
create table login_info
(
id INT not null comment 'ID',
createDate DATETIME not null comment '创建日期',
modifyDate DATETIME not null comment '最后修改日期',
description VARCHAR(255) comment '描述',
isSystem BIT not null comment '是否内置',
userInfo json not null comment '用户信息',
permissions LONGBLOB not null comment '权限'
)

userInfo 存储的是json格式的用户数据

{id:'1',name:'tom'}

你以为的查询SQL

select * from login_info
where exists(
  select *from users u where u.id=userInfo->'$.id'
)

结果空空如也

正确的写法如下

select * from login_info
where exists(
  select *from users u where JSON_OVERLAPS(JSON_QUOTE(u.id),userInfo->'$.id')
)

需要对数据进行格式转换
JSON_QUOTE