MySQL数据类型隐式转换规则

现象

成都创新互联是一家专注于网站设计制作、成都网站设计与策划设计,射阳网站建设哪家好?成都创新互联做网站,专注于网站建设十余年,网设计领域的专业建站公司;建站业务涵盖:射阳等地区。射阳做网站价格咨询:13518219792

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

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

convert_test表结构如下：

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

表中数据42W以上。

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

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

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

结果如下：

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

原因

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

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

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

结果如下：

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

扩展

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

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

两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
两个参数都是字符串，会按照字符串来比较，不做类型转换
两个参数都是整数，按照整数来比较，不做类型转换
十六进制的值和非数字做比较时，会被当做二进制串
有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
所有其他情况下，两个参数都会被转换为浮点数再进行比较

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

 
 
 
 
  
  
  
  select * from convert_test where areacode=0001 and period>='20170511' and period<='20170511'; 
  
  
  
  select * from convert_test where areacode=1 and period>='20170511' and period<='20170511'; 
  
  
  
  select * from convert_test where areacode=0001.0 and period>='20170511' and period<='20170511'; 
  
  
  
  select * from convert_test where areacode=1.0 and period>='20170511' and period<='20170511';

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

文章题目：MySQL数据类型隐式转换规则
当前路径：http://www.shufengxianlan.com/qtweb/news17/523317.html

网站建设、网络推广公司-创新互联，是专注品牌与效果的网站制作，网络营销seo公司；服务项目有等

声明：本网站发布的内容（图片、视频和文字）以用户投稿、用户转载内容为主，如果涉及侵权请尽快告知，我们将会在第一时间删除。文章观点不代表本网站立场，如需处理请联系客服。电话：028-86922220；邮箱：631063699@qq.com。内容未经允许不得转载，或转载时需注明来源：创新互联

猜你还喜欢下面的内容