MySQL implicit type conversion traps and rules

Foreword

I believe everyone knows that implicit type conversion has the risk of not hitting the index. In the case of high concurrency and large data volume, the consequences of missing the index are very serious. The database will be dragged to death, and then the entire system will collapse, causing heavy losses to large-scale systems. So let’s learn about MySQL implicit type conversion traps and rules through this article.

1. Implicit type conversion example

Today, a MySQL thread count alarm suddenly appeared on the production database, the IOPS was very high, and many sql similar to the following appeared in the instance session: (Relevant fields and values were modified)

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)

Copy after login

Use explain to check the number of scanned rows and index selection:

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.

Copy after login

There is an index on the

t_tb1 table uid_type_frid(f_col2_id,f_type) , idx_corp_id_qq1id(f_col1_id,f_qq1_id), and if the latter is selected, the filtering effect of f_qq1_id should be very good, but the former is chosen. When using 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.

Copy after login

The

rows column reached 2 million rows, but the problem was also discovered: select_type should be range , and key_len can be seen that only the first column of the idx_corpid_qq1id index is used. The above explanation uses extended, so show warnings; you can clearly see that f_qq1_id has implicit type conversion: f_qq1_id is varchar, and the subsequent comparison value is an integer.

The solution to this problem is to avoid the uncontrollability caused by implicit type conversion: write the content of f_qq1_id in as a string:

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.

Copy after login

The number of scan lines is reduced from 1386 to 40.

There is also a similar case:

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'

Copy after login

After optimization, the scanned rows were directly reduced from 1 million rows to 1.

Take this opportunity to take a systematic look at the implicit type conversion in mysql.

2. mysql implicit conversion rules

2.1 Rules

Let’s analyze the rules of implicit conversion:

a. When at least one of the two parameters is NULL , the comparison result is also NULL. The exception is when using <=> to compare two NULL , 1 will be returned. In both cases, 1 will be returned. Need to do type conversion

b. Both parameters are strings and will be compared according to strings without type conversion

c. Both parameters are integers and are compared as integers without type conversion

d. When comparing hexadecimal values with non-numbers, they will be treated as binary strings

e. One parameter is TIMESTAMP or DATETIME, and the other parameter is a constant, the constant will be converted to timestamp

f. One parameter is of type decimal . If the other parameter is decimal or an integer, the integer will be converted to decimal for comparison. If the other parameter is a floating point number, the decimal will be converted. Compare floating point numbers

g. In all other cases, both parameters will be converted to floating point numbers before comparison

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)

Copy after login

It can be seen from the above that 11 + 'aa', because the types of both sides of the operator are different and comply with Article G, aa needs to be converted into a floating point decimal, but the conversion fails (the letters are truncated), it can be considered to be converted into 0 , the integer 11 is converted to floating point type or itself, so 11 + 'aa' = 11.

When

0.01a is converted into double, it is also truncated to 0.01, so 11 + '0.01a' = 11.01.

Equality comparison also illustrates this point. '11a' and '11.0' are both equal to 11 after conversion. This is why the example at the beginning of the article is not indexed: varchar type f_qq1_id, converted to floating point When comparing types, there are countless situations equal to 12345, such as 12345a, 12345.b, etc. The MySQL optimizer cannot determine whether the index is more effective, so it chooses other solutions.

But it does not mean that as long as implicit type conversion occurs, it will cause similar performance problems as above. Ultimately, it depends on whether the index can be effectively selected after conversion. Like f_id = '654321' , f_mtime between '2016-05-01 00:00:00' and '2016-05-04 23:59:59' will not affect the index selection, because the former f_id It is an integer type. Even if it is compared with the subsequent string type number and converted into a double, the value of f_id can still be determined based on the double, and the index is still valid. The latter is because it complies with item e, but the constant on the right has been converted.

Developers may only have such a pitfall of implicit type conversion, but they often don't pay attention to it, so they don't need to remember so many rules and just compare the type with the type.

2.2 Security issues of implicit type conversion

Implicit type conversion may not only cause performance problems, but also security issues.

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

Copy after login

假如应用前端没有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';

Copy after login

攻击者更聪明一点： 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)

Copy after login

总结

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