MySQL数据类型隐式转换规则

2017-12-21 10:42:31来源:http://database.51cto.com/art/201712/561494.htm作者:51CTO人点击

分享


现象

今天遇到一个慢查询,查询日志找到慢查询语句是这样的:


select*fromconvert_testwhereareacode=0001andperiod>='20170511'andperiod<='20170511';

convert_test 表结构如下:


CREATETABLE`convert_test`(
`id`bigint(20)unsignedNOTNULLAUTO_INCREMENT,
`areacode`char(12)NOTNULLDEFAULT'',
`period`int(6)unsignedNOTNULLDEFAULT0,
`mid_price`int(10)unsignedNOTNULLDEFAULT0,
`mid_change`floatNOTNULLDEFAULT0,
`updated_datetime`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
PRIMARYKEY(`id`),
UNIQUEKEY`idx_areacode_period`(`areacode`,`period`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='隐式转换测试表';

表中数据42W以上。


乍一看,明明创建了一个唯一索引,正常来说,上面的查询语句应该正好命中idx_areacode_period这个索引的,不应该是慢查询的。


为了查看这个语句是怎么查询的,我们在测试库中explain一下:


mysql>explainselect*fromconvert_testwhereareacode=0001andperiod>='20170511'andperiod<='20170511';

结果如下:



可以看到,这里是没有用到索引的。


原因

定义表的时候,areacode字段是字符串类型的,查询的时候传入的是0001,这里0001被Mysql当做了整数处理为1,Mysql检测到areacode这个字段的查询类型是整型,就会全表扫描,将所有行的areacode转换成整型,然后在做查询处理。


找原因了,就很好解决了,上面的sql语句修改如下:


mysql>explainselect*fromconvert_testwhereareacode='0001'andperiod>='20170511'andperiod<='20170511';

结果如下:



可以看到完全命中了idx_areacode_period 这个索引。


扩展

上面的period定义的时候是整型,但是查询传入的是字符串类型,那为什么会命中索引的呢?


看一下 官方的隐试转 换说明:


两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换


两个参数都是字符串,会按照字符串来比较,不做类型转换


两个参数都是整数,按照整数来比较,不做类型转换


十六进制的值和非数字做比较时,会被当做二进制串


有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp


有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较


所有其他情况下,两个参数都会被转换为浮点数再进行比较

所以,下面的几个sql语句有相同的效果:


select*fromconvert_testwhereareacode=0001andperiod>='20170511'andperiod<='20170511';
select*fromconvert_testwhereareacode=1andperiod>='20170511'andperiod<='20170511';
select*fromconvert_testwhereareacode=0001.0andperiod>='20170511'andperiod<='20170511';
select*fromconvert_testwhereareacode=1.0andperiod>='20170511'andperiod<='20170511';

mysql 在查询的时候,会将areacode转换成浮点型进行比较


微信扫一扫

第七城市微信公众平台