MySQL中的隐式转换造成的索引失效

在mysql查询中,当查询条件左右两侧类型不匹配的时候会发生隐式转换,可能导致查询无法使用索引。

官方的隐试转换说明:

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

常见情况:

id为int

SELECT * FROM `test` WHERE id= '10000'; 左边为int类型10000,转换为浮点数还是10000,右边字符串类型'10000',转换为浮点数也是10000。两边的转换结果都是唯一确定的,没有破坏索引规则。所以不影响使用索引。

id为varchar

SELECT * FROM `test` WHERE id = 10000; 左边是字符串类型'10000',转浮点数为10000是唯一的,右边int类型10000转换结果也是唯一的。但是,因为左边是检索条件,'10000'转到10000虽然是唯一,但是其他字符串也可以转换为10000,比如'10000a''010000''10000'等等都能转为浮点数10000,这样的情况下,索引规则被破坏,是不能用到索引的。

结论

  1. 当我们使用的字段是数值类型时,加引号或者不加引号(sql中单引号和双引号实现相同效果)都不影响索引的使用
  2. 当我们的字段是字符串类型时,不加引号的查询无法使用索引,加引号的查询才可正常使用索引

综上所述,我认为以后写sql的时候注意最好都加上引号,避免这种字符串类型的不走索引的情况发生

 

 

发布了7 篇原创文章 · 获赞 6 · 访问量 1万+
展开阅读全文

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

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览