谈谈MySQL中的隐式转换
工作过程中会遇到比较多关于隐式转换的案例,隐式转换除了会导致慢查询,还会导致数据不准。本文通过几个生产中遇到的案例来。
基础知识
关于比较运算的原则,MySQL官方文档的描述: https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html
如果 判断符号左右两边有一个为NULL,结果就是null,除非使用安全的等值判断 <=>
(none) 05:17:16 >select null = null;+-------------+| null = null |+-------------+| NULL |+-------------+1 row in set (0.00 sec)(none) 05:34:59 >select null <=> null;+---------------+| null <=> null |+---------------+| 1 |+---------------+1 row in set (0.00 sec)(none) 05:35:51 >select null != 1;+-----------+| null != 1 |+-----------+| NULL |+-----------+1 row in set (0.00 sec)
注意对于比较常见的 字符串与数字类型的比较的情况,如果字符串字段是索引字段,那么MySQL 无法通过索引进行查找数据,比如以下例子:
(none) 05:39:42 >select 1='1';+-------+| 1='1' |+-------+| 1 |+-------+1 row in set (0.00 sec)(none) 05:39:44 >select 1='1A';+--------+| 1='1A' |+--------+| 1 |+--------+1 row in set, 1 warning (0.00 sec)(none) 05:39:47 >select 1='1 '; ##1后有空格+--------+| 1='1 ' |+--------+| 1 |+--------+1 row in set (0.00 sec)
Hexadecimal(十六进制)以二进制字符串的方式进行比较。
如何判断符号左边是 timestamp 或者datetime类型的,右边是常量,在比较之前,常量会被转换为时间类型。
隐式转换
字段类型不一样
In all other cases, the arguments are compared as floating-point (real) numbers.
除了以上的其他类型的比较,系统将字段和参数转换为浮点型进行比较。使用浮点数(或转换为浮点数的值)的比较是近似的,因为这样的数字是不精确的。看下面2个例子
>select '190325171202362933' = 190325171202362931;+-------------------------------------------+| '190325171202362933' = 190325171202362931 |+-------------------------------------------+| 1 |+-------------------------------------------+1 row in set (0.00 sec)>select '190325171202362936' = 190325171202362931;+-------------------------------------------+| '190325171202362936' = 190325171202362931 |+-------------------------------------------+| 1 |+-------------------------------------------+1 row in set (0.00 sec)
>select '190325171202362931'+0.0;+--------------------------+| '190325171202362931'+0.0 |+--------------------------+| 1.9032517120236294e17 |+--------------------------+1 row in set (0.00 sec)>select '190325171202362936'+0.0;+--------------------------+| '190325171202362936'+0.0 |+--------------------------+| 1.9032517120236294e17 |+--------------------------+1 row in set (0.00 sec)
in 参数包含多个类型
具体的案例参考之前的一篇文章MySQL优化案例一则,where 条件 in 集合里面的数据类型不一样,执行计划未利用到索引
淘宝MySQL月报(http://mysql.taobao.org/monthly/2017/12/06/ )里面有一篇正好和这个一样的案例,推荐给大家简单说,就是在IN的入口有一个判断, 如果in中的字段类型不兼容, 则认为不可使用索引.
而这个arg_types_compatible 的赋值逻辑是:
if (type_cnt == 1) arg_types_compatible = TRUE;
字符集类型不一致
环境准备:
CREATE TABLE `t1` (`id` int(11) NOT NULL AUTO_INCREMENT,`c1` varchar(20) DEFAULT NULL,`c2` varchar(50) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_c1` (`c1`),KEY `idx_c2` (`c2`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;CREATE TABLE `t2` (`id` int(11) NOT NULL AUTO_INCREMENT,`c1` varchar(20) DEFAULT NULL,`c2` varchar(50) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_c1` (`c1`),KEY `idx_c2` (`c2`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;insert into t1(c1,c2) values('a','a'),('b','b'),('c','c'),('d','d'),('e','e');insert into t2(c1,c2) values('a','a'),('b','b'),('c','c'),('d','d'),('e','e');
小结
希望通过以上案例,基础知识介绍,开发同学能少走弯路,在开发编写sql的阶段一定要明确字段的类型,尤其是看起来像数字类型的id,xxxid,xxxno 这类字段,实际上可能是字符类型。
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- Linux下MYSQL 5.7 找回root密码的问题(亲测可用)
- MySQL 什么时候使用INNER JOIN 或 LEFT JOIN
- android从服务器下载文件(php+apache+win7+MySql)
- 【有图】android通过jdbc连接mysql(附文件)
- 无废话Android之smartimageview使用、android多线程下载、显式意
- Android之隐式启动通过action跳转到指定的Activity
- 2011.09.07(2)——— android 跨进程通信之隐式intent
- 2011.09.07(2)——— android 跨进程通信之隐式intent