小心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 规则

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

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


				
时间： 2024-10-18 09:22:58

		
		


		


	

	
	

		


		
小心MySQL的隐式类型转换陷阱的相关文章


								

		

			

                MySQL 的隐式类型转换
			

		

		

									

				换 十六进制的值和非数字做比较时,会被当做二进制串,和数字做比较时会按下面的规则处理 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较 所有其他情况下,两个参数都会被转换为浮点数再进行比较 注意一个安全问题:假如 password 类型			

		

	

				

		

			

                C#的隐式类型转换
			

		

		

									

				在C#语言中,一些预定义的数据类型之间存在着预定义的转换.比如,从int类型转换到long类型.C#语言中数据类型的转换可以用分为两类:隐式转换(implicit conversions)和显式转换(explicit conversions).本章我们将详细介绍这两类转换. 6.1 隐式类型转换 隐式转换就是系统默认的.不需要加以声明就可以进行的转换.在隐式转换过程中,编译器无需对转换进行详细检查就能够安全地执行转换.比如从int类型转换到long类型就是一种隐式转换.隐式转换一般不会失败,转换			

		

	

				

		

			

                C++中通过重载避免隐式类型转换
			

		

		

									

				以下是一段代码,如果没有什么不寻常的原因,实在看不出什么东西: class UPInt { // unlimited precision public: // integers 类 UPInt(); UPInt(int value); ...};//有关为什么返回值是const的解释,参见Effective C++ 条款21const UPInt operator+(const UPInt& lhs, const UPInt& rhs);UPInt upi1, upi2;...UPInt			

		

	

				

		

			

                浅析JavaScript中的隐式类型转换
			

		

		

									

				        这篇文章主要是对JavaScript中的隐式类型转换进行了详细分析介绍,需要的朋友可以过来参考下,希望对大家有所帮助 如果把通过函数或方法调用,明确的将某种类型转换成另一种类型称为显示转换 ,相反则称为隐式类型转换 .google和维基百科中没有找到"显示类型转换","隐式类型转换"的字眼.暂且这么称呼.   一. 运算中存在的隐式类型转换    1, "+"运算符     代码如下: var a = 11, b = '22'; 			

		

	

				

		

			

                JavaScript运算符规则与隐式类型转换详解
			

		

		

									

				本文中涉及的参考资料全部声明在了JavaScript 数据结构学习与实践资料索引 . 隐式类型转换 在 JavaScript 中,当我们进行比较操作或者加减乘除四则运算操作时,常常会触发 JavaScript 的隐式类型转换机制;而这部分也往往是令人迷惑的地方.譬如浏览器中的 console.log 操作常常会将任何值都转化为字符串然后展示,而数学运算则会首先将值转化为数值类型(除了 Date 类型对象)然后进行操作. 我们首先来看几组典型的 JavaScript 中运算符操作结果,希望阅读完本			

		

	

				

		

			

                JavaScript隐式类型转换_javascript技巧
			

		

		

									

				JavaScript的数据类型是非常弱的(不然不会叫它做弱类型语言了)!在使用算术运算符时,运算符两边的数据类型可以是任意的,比如,一个字符串可以和数字相加.之所以不同的数据类型之间可以做运算,是因为JavaScript引擎在运算之前会悄悄的把他们进行了隐式类型转换的,如下是数值类型和布尔类型的相加: 复制代码 代码如下: 3 + true; // 4 结果是一个数值型!如果是在C或者Java环境的话,上面的运算肯定会因为运算符两边的数据类型不一致而导致报错的!但是,在JavaScript中,只			

		

	

				

		

			

                总结Javascript中的隐式类型转换_javascript技巧
			

		

		

									

				JavaScript的数据类型分为六种,分别为null,undefined,boolean,string,number,object.object是引用类型,其它的五种是基本类型或者是原始类型. 比如像是Number() ,还是parseInt() .parseFloat()都属于显示类型转换(强制类型转换): 这一节我们来看一下隐式类型转换(自动转换). 数值自动转换为字符串 var a = 123; alert(a+'456'); // 输出 123456 "+"号为连接符 字符串			

		

	

				

		

			

                简单介绍JavaScript数据类型之隐式类型转换_javascript技巧
			

		

		

									

				JavaScript的数据类型分为六种,分别为null,undefined,boolean,string,number,object.object是引用类型,其它的五种是基本类型或者是原始类型.我们可以用typeof方法打印来某个是属于哪个类型的.不同类型的变量比较要先转类型,叫做类型转换,类型转换也叫隐式转换.隐式转换通常发生在运算符加减乘除,等于,还有小于,大于等.. typeof '11' //string typeof(11) //number '11' < 4 //false 本章节单			

		

	

				

		

			

                浅析JavaScript中的隐式类型转换_javascript技巧
			

		

		

									

				如果把通过函数或方法调用,明确的将某种类型转换成另一种类型称为显示转换 ,相反则称为隐式类型转换 .google和维基百科中没有找到"显示类型转换","隐式类型转换"的字眼.暂且这么称呼. 一. 运算中存在的隐式类型转换 1, "+"运算符 复制代码 代码如下: var a = 11, b = '22'; var c = a + b; 这里引擎将会先把a变成字符串"11"再与b进行连接,变成了"1122".