MySQL隐式类型的转换陷阱和规则

数据库 2024/11/16 佚名

3 1 2

前言

相信大家都知道隐式类型转换有无法命中索引的风险，在高并发、大数据量的情况下，命不中索引带来的后果非常严重。将数据库拖死，继而整个系统崩溃，对于大规模系统损失惨重。所以下面通过本文来好好学习下MySQL隐式类型的转换陷阱和规则。

1. 隐式类型转换实例

今天生产库上突然出现MySQL线程数告警，IOPS很高，实例会话里面出现许多类似下面的sql：(修改了相关字段和值)

SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 and f_col2_id=1244378 and 
f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233)

用 explain 看了下扫描行数和索引选择情况：

mysql>explain SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 
and f_col2_id=1244378 and f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233);
+------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+
| id | select_type | table | type | possible_keys     | key   | key_len | ref | rows | Extra        |
+------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+
| 1 | SIMPLE  | t_tb1 | ref | uid_type_frid,idx_corpid_qq1id | uid_type_frid | 8   | const | 1386 | Using index condition; Using where |
+------+---------------+---------+--------+--------------------------------+---------------+------------+--------+--------+------------------------------------+
共返回 1 行记录,花费 11.52 ms.

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

mysql>explain extended SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 use index(idx_corpid_qq1id) WHERE f_col1_id=1226391 and f_col2_id=1244378 and f_qq1_id in (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233);
+------+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+
| id | select_type | table | type | possible_keys  | key    | key_len | ref  | rows  | Extra        |
+------+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+
| 1 | SIMPLE  | t_tb1 | ref | idx_corpid_qq1id | idx_corpid_qq1id | 8   | const | 2375752  | Using index condition; Using where |
+---- -+---------------+--------+--------+---------------------+------------------+------------+----------+-------------+------------------------------------+
共返回 1 行记录,花费 17.48 ms.
mysql>show warnings;
+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+
| Level   | Code   | Message                            |
+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+
| Warning   |   1739 | Cannot use range access on index 'idx_corpid_qq1id' due to type or collation conversion on field 'f_qq1_id'   |
| Note   |   1003 | /* select#1 */ select `d_dbname`.`t_tb1`.`f_col3_id` AS `f_col3_id`,`d_dbname`.`t_tb1`.`f_qq1_id` AS `f_qq1_id` from `d_dbname`.`t_tb1` USE INDEX (`idx_corpid_qq1id`) where |
|     |    | ((`d_dbname`.`t_tb1`.`f_col2_id` = 1244378) and (`d_dbname`.`t_tb1`.`f_col1_id` = 1226391) and (`d_dbname`.`t_tb1`.`f_qq1_id` in |
|     |    | (12345,23456,34567,45678,56789,67890,78901,89012,90123,901231,901232,901233)))          |
+-----------------+----------------+-----------------------------------------------------------------------------------------------------------------------+
共返回 2 行记录,花费 10.81 ms.

rows列达到200w行，但问题也发现了：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的内容写成字符串：

mysql>explain SELECT f_col3_id,f_qq1_id FROM d_dbname.t_tb1 WHERE f_col1_id=1226391 and f_col2_id=1244378 and 
f_qq1_id in ('12345','23456','34567','45678','56789','67890','78901','89012','90123','901231');
+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+
| id | select_type | table | type | possible_keys     | key    | key_len  | ref  | rows | Extra        |
+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+
| 1  | SIMPLE  | t_tb1 | range | uid_type_frid,idx_corpid_qq1id | idx_corpid_qq1id | 70   |   | 40  | Using index condition; Using where |
+-------+---------------+--------+---------+--------------------------------+------------------+-------------+---------+---------+------------------------------------+
共返回 1 行记录,花费 12.41 ms.

扫描行数从1386减少为40。

类似的还出现过一例：

SELECT count(0) FROM d_dbname.t_tb2 where f_col1_id= '1931231' AND f_phone in(098890);
| Warning | 1292 | Truncated incorrect DOUBLE value: '1512-98464356'

优化后直接从扫描rows 100w行降为1。

借这个机会，系统的来看一下mysql中的隐式类型转换。

2. mysql隐式转换规则

2.1 规则

下面来分析一下隐式转换的规则：

a. 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换

b. 两个参数都是字符串，会按照字符串来比较，不做类型转换

c. 两个参数都是整数，按照整数来比较，不做类型转换

d. 十六进制的值和非数字做比较时，会被当做二进制串

e. 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp

f. 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较

g. 所有其他情况下，两个参数都会被转换为浮点数再进行比较

mysql> select 11 + '11', 11 + 'aa', 'a1' + 'bb', 11 + '0.01a'; 
+-----------+-----------+-------------+--------------+
| 11 + '11' | 11 + 'aa' | 'a1' + 'bb' | 11 + '0.01a' |
+-----------+-----------+-------------+--------------+
|  22 |  11 |   0 |  11.01 |
+-----------+-----------+-------------+--------------+
1 row in set, 4 warnings (0.00 sec)
mysql> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message         |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a1' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'bb' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '0.01a' |
+---------+------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> select '11a' = 11, '11.0' = 11, '11.0' = '11', NULL = 1;
+------------+-------------+---------------+----------+
| '11a' = 11 | '11.0' = 11 | '11.0' = '11' | NULL = 1 |
+------------+-------------+---------------+----------+
|   1 |   1 |    0 |  NULL |
+------------+-------------+---------------+----------+
1 row in set, 1 warning (0.01 sec)

上面可以看出11 + 'aa'，由于操作符两边的类型不一样且符合第g条，aa要被转换成浮点型小数，然而转换失败（字母被截断），可以认为转成了 0，整数11被转成浮点型还是它自己，所以11 + 'aa' = 11。

0.01a转成double型也是被截断成0.01，所以11 + '0.01a' = 11.01。

等式比较也说明了这一点，'11a'和'11.0'转换后都等于 11，这也正是文章开头实例为什么没走索引的原因： varchar型的f_qq1_id，转换成浮点型比较时，等于 12345 的情况有无数种如12345a、12345.b等待，MySQL优化器无法确定索引是否更有效，所以选择了其它方案。

但并不是只要出现隐式类型转换，就会引起上面类似的性能问题，最终是要看转换后能否有效选择索引。像f_id = '654321' 、f_mtime between '2016-05-01 00:00:00' and '2016-05-04 23:59:59'就不会影响索引选择，因为前者f_id是整型，即使与后面的字符串型数字转换成double比较，依然能根据double确定f_id的值，索引依然有效。后者是因为符合第e条，只是右边的常量做了转换。

开发人员可能都只要存在这么一个隐式类型转换的坑，但却又经常不注意，所以干脆无需记住那么多规则，该什么类型就与什么类型比较。

2.2 隐式类型转换的安全问题

implicit type conversion 不仅可能引起性能问题，还有可能产生安全问题。

mysql> desc t_account;
+-----------+-------------+------+-----+---------+----------------+
| Field  | Type  | Null | Key | Default | Extra   |
+-----------+-------------+------+-----+---------+----------------+
| fid  | int(11)  | NO | PRI | NULL | auto_increment |
| fname  | varchar(20) | YES |  | NULL |    |
| fpassword | varchar(50) | YES |  | NULL |    |
+-----------+-------------+------+-----+---------+----------------+
mysql> select * from t_account;
+-----+-----------+-------------+
| fid | fname  | fpassword |
+-----+-----------+-------------+
| 1 | xiaoming | p_xiaoming |
| 2 | xiaoming1 | p_xiaoming1 |
+-----+-----------+-------------+

假如应用前端没有WAF防护，那么下面的sql很容易注入：

mysql> select * from t_account where fname='A' ;
fname传入 A' OR 1='1 
mysql> select * from t_account where fname='A' OR 1='1';

攻击者更聪明一点： fname传入 A'+'B ，fpassword传入 ccc'+0 ：

mysql> select * from t_account where fname='A'+'B' and fpassword='ccc'+0;
+-----+-----------+-------------+
| fid | fname  | fpassword |
+-----+-----------+-------------+
| 1 | xiaoming | p_xiaoming |
| 2 | xiaoming1 | p_xiaoming1 |
+-----+-----------+-------------+
2 rows in set, 7 warnings (0.00 sec)

总结

以上就是为大家总结的MySQL隐式类型的转换陷阱和规则，希望这篇文章对大家学习或者mysql能有所帮助，如果有疑问大家可以留言交流，谢谢大家对的支持。

mysql隐式类型转换,mysql隐式转换,隐式类型转换规则

华山资源网 Design By www.eoogi.com

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

华山资源网 Design By www.eoogi.com

评论“MySQL隐式类型的转换陷阱和规则”

暂无评论...

www.eoogi.com 华山资源网

120,135影音资源

344,641技术资源

22,817软件资源

435,032站长资源

MySQL隐式类型的转换陷阱和规则

MySQL清空数据表的方法实例与分析

浅谈sql连接查询的区别 inner,left,right,full

评论“MySQL隐式类型的转换陷阱和规则”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

MySQL隐式类型的转换陷阱和规则

MySQL清空数据表的方法实例与分析

浅谈sql连接查询的区别 inner,left,right,full

评论“MySQL隐式类型的转换陷阱和规则”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存