场景:
查询sql时发现查询数据与预期不符,多了几条不相干记录,经查证发现约束条件字段类型不一致;
# 1. 联合查询
SELECT
t1.id,
t2.id,
t1.qrcode_code,
t2.`code`
FROM
household_qr_code t1
INNER JOIN ddp_community_resident t2 ON t1.qrcode_code = t2.`code`
WHERE
t1.id = 92
ORDER BY
t1.create_time DESC;
# 2. 单独查询,获取到code
SELECT id,qrcode_code FROM household_qr_code WHERE id = 92;
# 3. 使用2中获取到的code单独查询
SELECT id,`code` FROM ddp_community_resident WHERE `code` = 1666284664246546433;
# 4. 使用2中获取到的code单独查询,并将参数设为字符串
SELECT id,`code` FROM ddp_community_resident WHERE `code` = '1666284664246546433';
结果:
1.:两条
2.一条
3.两条
4.两条
原因:
household_qr_code中的qrcode_code是bigint类型,ddp_community_resident中的code是varchar类型,类型不一致引起的查询错误
解决方案:
使用cast()函数将关联字段统一类型
cast( t1.qrcode_code AS CHAR ) = t2.`code`
SELECT
t1.id,
t2.id,
t1.qrcode_code,
t2.`code`
FROM
household_qr_code t1
INNER JOIN ddp_community_resident t2 ON cast( t1.qrcode_code AS CHAR ) = t2.`code`
WHERE
t1.id = 92
ORDER BY
t1.create_time DESC;