位置: 编程技术 - 正文
MySQL隐式类型的转换陷阱和规则(mysql 隐式游标)
编辑：rootadmin推荐整理分享MySQL隐式类型的转换陷阱和规则(mysql 隐式游标)，希望有所帮助，仅作参考，欢迎阅读内容。
文章相关热门搜索词:mysql隐式类型转换,mysql 隐式游标,mysql隐式类型转换,mysql隐藏字段,mysql隐藏,mysql隐式转换索引失效,mysql隐式主键,mysql 隐式提交,内容如对您有帮助，希望把文章链接给更多的朋友！
前言
相信大家都知道隐式类型转换有无法命中索引的风险，在高并发、大数据量的情况下，命不中索引带来的后果非常严重。将数据库拖死，继而整个系统崩溃，对于大规模系统损失惨重。所以下面通过本文来好好学习下MySQL隐式类型的转换陷阱和规则。
1. 隐式类型转换实例
今天生产库上突然出现MySQL线程数告警，IOPS很高，实例会话里面出现许多类似下面的sql：(修改了相关字段和值)
用 explain 看了下扫描行数和索引选择情况：
t_tb1 表上有个索引uid_type_frid(f_col2_id,f_type) 、idx_corp_id_qq1id(f_col1_id,f_qq1_id) ，而且如果选择后者时，f_qq1_id的过滤效果应该很佳，但却选择了前者。当使用 hint use index(idx_corp_id_qq1id)时：
rows列达到w行，但问题也发现了：select_type应该是 range 才对，key_len看出来只用到了idx_corpid_qq1id索引的第一列。上面explain使用了 extended，所以show warnings;可以很明确的看到 f_qq1_id 出现了隐式类型转换：f_qq1_id是varchar，而后面的比较值是整型。
解决该问题就是避免出现隐式类型转换(implicit type conversion)带来的不可控：把f_qq1_id in的内容写成字符串：
扫描行数从减少为。
类似的还出现过一例：
优化后直接从扫描rows w行降为1。
借这个机会，系统的来看一下mysql中的隐式类型转换。
2. mysql隐式转换规则
2.1 规则
下面来分析一下隐式转换的规则：
a. 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
b. 两个参数都是字符串，会按照字符串来比较，不做类型转换
c. 两个参数都是整数，按照整数来比较，不做类型转换
d. 十六进制的值和非数字做比较时，会被当做二进制串
e. 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
f. 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
g. 所有其他情况下，两个参数都会被转换为浮点数再进行比较
上面可以看出 + 'aa'，由于操作符两边的类型不一样且符合第g条，aa要被转换成浮点型小数，然而转换失败（字母被截断），可以认为转成了 0，整数被转成浮点型还是它自己，所以 + 'aa' = 。
0.a转成double型也是被截断成0.，所以 + '0.a' = .。
等式比较也说明了这一点，'a'和'.0'转换后都等于 ，这也正是文章开头实例为什么没走索引的原因： varchar型的f_qq1_id，转换成浮点型比较时，等于 的情况有无数种如a、.b等待，MySQL优化器无法确定索引是否更有效，所以选择了其它方案。
但并不是只要出现隐式类型转换，就会引起上面类似的性能问题，最终是要看转换后能否有效选择索引。像f_id = '' 、f_mtime between '-- ::' and '-- ::'就不会影响索引选择，因为前者f_id是整型，即使与后面的字符串型数字转换成double比较，依然能根据double确定f_id的值，索引依然有效。后者是因为符合第e条，只是右边的常量做了转换。
开发人员可能都只要存在这么一个隐式类型转换的坑，但却又经常不注意，所以干脆无需记住那么多规则，该什么类型就与什么类型比较。
2.2 隐式类型转换的安全问题
implicit type conversion 不仅可能引起性能问题，还有可能产生安全问题。
假如应用前端没有WAF防护，那么下面的sql很容易注入：
攻击者更聪明一点： fname传入 A'+'B ，fpassword传入 ccc'+0 ：
总结
标签: mysql 隐式游标
本文链接地址:https://www.jiuchutong.com/biancheng/348625.html 转载请保留说明！上一篇：php mysql连接数据库实例(php连接mysql数据库的几种方式及区别)
下一篇：C3P0连接池+MySQL的配置及wait_timeout问题的解决方法(c3p0连接池提供了哪些方式来配置连接池的参数)
