Mysql if判断 类型隐形转换注意事项

最近在做code review时,团队成员说sql中有个诡异的问题,某个登录IP对应的登录地点不不显示(该IP信息在IP地址表中已经维护),一起看下:

  • 表1: 登录日志表,sys_log_login,数据如下:
user_id login_ip login_time
0010tW8gq3q6 0:0:0:0:0:0:0:1 2019-06-24 15:30:25
100191 127.0.0.1 2019-06-25 10:20:35
  • 表2: IP地址表,data_ips,数据如下:
ip isp country region city
0:0:0:0:0:0:0:1 内网IP xx xx xx
127.0.0.1 内网IP xx xx xx
  • sql:
SELECT a.login_ip, 
	IF(b.ip, CONCAT(b.isp, ' ', b.country, '-', b.region, '-', b.city ), '') login_address
FROM sys_log_login a LEFT JOIN data_ips b on a.login_ip = b.ip 
order by login_time desc;

登录IP在IP地址表中有维护则显示“运营商-国家-省-市”,否则显示空字符串,输出结果:
0:0:0:0:0:0:0:1 ‘’
127.0.0.1 ‘内网IP XX-XX-XX’

疑问:为啥登录ip 0:0:0:0:0:0:0:1,得到的是空字符串呢?
可能很多朋友都有这个疑问,仔细分析sql,这里有1个注意点:

  • mysql if 判断 隐性类型转换

我们不妨先看下mysql if判断语法:IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

看上文sql,if (b.ip, exp2, ‘’),此处b.ip是字符串,即不是表达式也不是bool值,mysql处理的时候,会做2步:
1)会先将b.ip转换成float值;
2)根据得到的float值,非0则为true,0则为false;

0:0:0:0:0:0:0:1 转 float,得到0,所以if (b.ip, exp2, ‘’)取空字符串
127.0.0.1 转 float,得到127,所以if (b.ip, exp2, ‘’)取exp2,得到内网IP XX-XX-XX

正确sql如下:

SELECT a.login_ip, 
	IF(b.ip is not null, CONCAT(b.isp, ' ', b.country, '-', b.region, '-', b.city ), '') login_address
FROM sys_log_login a LEFT JOIN data_ips b on a.login_ip = b.ip 
order by login_time desc;
展开阅读全文

没有更多推荐了,返回首页