本篇记录工作中遇到的一些小问题,分享出来大家一起学习下。
mysql中的隐式转换
今天在执行一条sql语句时候,where条件写错了,但是居然查询出来了结。(如下图所示)
第一反映是,mysql的优化器是不是做了什么操作,导致最后查询条件不是现在的72071003dsss
,而是变成了其它的值。因为之前在一篇文章看过,如果where条件两边一边是数值类型一边是字符型mysql会做隐式转换
,但是具体是怎么转换的,我就不是很了解。所以就先朝这个方向去查找结果。
首先去看下表结构,user_id字段果然是int类型,和查询条件72071003dsss
不是同一个类型。
通过查询官方文档得到一个答案:(对于今天问题的探讨,重点请看最后一条)
当操作符与不同类型的操作数一起使用时,会发生类型转换以使操作数兼容。某些转换是隐式发生的。
- 如果一个或两个参数均为NULL,则比较的结果为NULL,但NULL-safe <=> 相等比较运算符除外。对于NULL <=> NULL,结果为true。无需转换。
- 如果比较操作中的两个参数都是字符串,则将它们作为字符串进行比较。
- 如果两个参数都是整数,则将它们作为整数进行比较。
- 如果不与数字比较,则将十六进制值视为二进制字符串。
- 如果参数之一是a TIMESTAMP或 DATETIMEcolumn,而另一个参数是常量,则在执行比较之前,该常量将转换为时间戳。这样做是为了使ODBC更友好。对于的参数,此操作未完成 IN()。为了安全起见,在进行比较时请始终使用完整的日期时间,日期或时间字符串。例如,要在BETWEEN与日期或时间值一起使用时获得最佳结果 ,请使用CAST()将值显式转换为所需的数据类型。
- 一个或多个表中的单行子查询不被视为常量。例如,如果子查询返回要与DATETIME 值进行比较的整数,则比较将作为两个整数进行。整数不转换为时间值。要将操作数作为DATETIME值进行比较 ,请使用 CAST()将子查询值显式转换为DATETIME。
- 如果参数之一是十进制值,则比较取决于另一个参数。如果另一个参数是十进制或整数值,则将参数作为十进制值进行比较;如果另一个参数是浮点值,则将参数作为浮点值进行比较。
- 在所有其他情况下,将参数作为浮点数(实数)进行比较。例如,将字符串和数字操作数进行比较,将其作为浮点数的比较。
隐式转换导致查询出不符合where条件的结果
如果查询条件的由字符转为浮点时候,又是什么样的转换规则呢?
- 不以数字开头的字符串都将转换为0。如
'abc'
、'a123bc'
、'abc123'
都会转化为0
;- 以数字开头的字符串转换时会进行截取,从第一个字符截取到第一个非数字内容为止。比如
'123abc'
会转换为123
,'012abc'
会转换为012
也就是12
,'5.3a66b78c'
会转换为5.3
,其他同理。
所以我们文章开始贴出来的sql
SELECT * FROM A WHERE config_name='test111' AND user_id ='72071003dsss'
的查询条件'72071003dsss'
被转换成了72071003
,那么最后的查询语句就是
SELECT * FROM A WHERE config_name='test111' AND user_id ='72071003'
结果: 由于触发隐式转换,将我们的字符型查询条件按照一定规则转换成了浮点性,变成了另一个值,而刚好这另一个值有匹配的结果。这就是为什么查询出了不符合where条件的结果原因。
隐式转换导致索引失效
-
当操作符左右两边的数据类型不一致时,会发生隐式转换。
-
当where查询操作符左边为数值类型时发生了隐式转换,那么对效率影响不大,但还是不推荐这么做。
select* from test1 where int_column= '10000'
,对于这条sql int_column字段是整型
左边为int类型10000,转换为浮点数还是10000,右边字符串类型’10000’,转换为浮点数也是10000。两边的转换结果都是唯一确定的,所以不影响使用索引。 -
当where查询操作符左边为字符类型时发生了隐式转换,那么会导致索引失效,造成全表扫描效率极低。
select* from test1 where str_column= 10000
,对于这条sql str_column是字符型
左边是字符串类型’10000’,转浮点数为10000是唯一的,右边int类型10000转换结果也是唯一的。但是,因为左边是检索条件,'10000’转到10000虽然是唯一,但是其他字符串也可以转换为10000,比如’10000a’,‘010000’,'10000’等等都能转为浮点数10000,这样的情况下,是不能用到索引的
tips:隐式转换有可能会导致索引失效,这个我们工作中需要注意的。
当where查询操作符**左边为字符类型**时发生了隐式转换,那么会导致索引失效,造成全表扫描效率极低。