MySQL数据类型隐式转换规则

数据库 MySQL
今天遇到一个慢查询,乍一看,明明创建了一个唯一索引,正常来说,上面的查询语句应该正好命中idx_areacode_period这个索引的,不应该是慢查询的。MySQ在查询的时候,会将areacode转换成浮点型进行比较。

 

[[213994]]

现象

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

  1. select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511'

convert_test表结构如下:

  1. CREATE TABLE `convert_test` ( 
  2.     `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
  3.     `areacode` char(12) NOT NULL DEFAULT ''
  4.     `period` int(6) unsigned NOT NULL DEFAULT 0, 
  5.     `mid_price` int(10) unsigned NOT NULL DEFAULT 0, 
  6.     `mid_change` float NOT NULL DEFAULT 0, 
  7.     `updated_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  8.     PRIMARY KEY (`id`), 
  9.     UNIQUE KEY `idx_areacode_period` (`areacode`,`period`) 
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='隐式转换测试表'

表中数据42W以上。

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

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

  1. mysql> explain select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511'

结果如下:

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

原因

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

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

  1. mysql> explain select * from convert_test where areacode='0001' and period>='20170511' and period<='20170511'

结果如下: 

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

扩展

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

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

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

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

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

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

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

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

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

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

  1. select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511'
  2. select * from convert_test where areacode=1 and period>='20170511' and period<='20170511'
  3. select * from convert_test where areacode=0001.0 and period>='20170511' and period<='20170511'
  4. select * from convert_test where areacode=1.0 and period>='20170511' and period<='20170511'

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

责任编辑:庞桂玉 来源: segmentfault
相关推荐

2009-07-02 15:59:55

JSP数据类型

2019-09-28 22:41:18

OracleMySQL隐式数据

2017-09-05 16:17:35

JavaScript运算转换

2017-09-13 10:58:51

JavaScript转换规则

2023-08-14 08:35:36

2010-09-06 16:25:46

SQL函数

2022-10-27 20:42:04

JavaScripJava编程语言

2023-12-12 08:50:22

MySQL隐式转换varchar

2017-07-10 13:38:07

MySQL数据类型整数类型

2010-09-17 14:57:34

JAVA数据类型

2011-07-01 15:32:58

Qt 数据类型

2022-02-23 21:24:21

索引SQL字符

2009-08-04 14:56:34

ASP.NET数据类型

2010-10-15 13:28:34

MySql数据类型

2009-09-01 16:35:55

C#操作String数

2010-09-06 17:35:43

SQL函数

2009-09-04 10:49:19

C#隐式转换

2009-08-12 16:26:27

C#数据类型转换

2010-03-30 16:33:55

Oracle数据类型

2010-06-13 18:00:56

MySQL数据类型
点赞
收藏

51CTO技术栈公众号