圆月山庄资源网

谈谈MySQL中的隐式转换
数据库 2024/12/23 佚名
3 2
圆月山庄资源网 Design By www.vgjia.com

工作过程中会遇到比较多关于隐式转换的案例，隐式转换除了会导致慢查询，还会导致数据不准。本文通过几个生产中遇到的案例来。

基础知识

关于比较运算的原则,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)

MySQL 认为数字1 与'1','1_','1A' 相等，故无法通过索引二分查找准确定位到具体的值。

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)

直观上不相等的值，做等值判断之后竟然返回为1。这样带来2个问题不能利用索引且结果数据不准

>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)

将上面的值转换为浮点数,都是 1.9032517120236294e17，所以判断相等时为真，返回True。

in 参数包含多个类型

具体的案例参考之前的一篇文章MySQL优化案例一则 ，where 条件 in 集合里面的数据类型不一样，执行计划未利用到索引

谈谈MySQL中的隐式转换

淘宝MySQL月报(http://mysql.taobao.org/monthly/2017/12/06/ )里面有一篇正好和这个一样的案例，推荐给大家 简单说,就是在IN的入口有一个判断, 如果in中的字段类型不兼容, 则认为不可使用索引. 

谈谈MySQL中的隐式转换

而这个arg_types_compatible 的赋值逻辑是:

if (type_cnt == 1) 
    arg_types_compatible = TRUE;

也就是说,当IN列表中出现超过一个字段类型时, 就认为类型不兼容，从而不能利用索引。

字符集类型不一致

环境准备:

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');

测试结果

谈谈MySQL中的隐式转换

小结

希望通过以上案例，基础知识介绍，开发同学能少走弯路，在开发编写sql的阶段一定要明确字段的类型，尤其是看起来像数字类型的id,xxxid,xxxno 这类字段,实际上可能是字符类型。

以上就是谈谈MySQL中的隐式转换的详细内容，更多关于MySQL 隐式转换的资料请关注其它相关文章！

标签：
MySQL,转换,MySQL,隐式转换
圆月山庄资源网 Design By www.vgjia.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
圆月山庄资源网 Design By www.vgjia.com
www.vgjia.com 圆月山庄资源网
139,976互联网资源
144,792高清电影
21,817无损音乐
631,128技术资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接