Implicit conversion method in MySQL

隐式转化规则

官方文档中关于隐式转化的规则是如下描述的：

If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe <=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.

• If both arguments in a comparison operation are strings, they are compared as strings.

• If both arguments are integers, they are compared as integers.

• Hexadecimal values are treated as binary strings if not compared to a number.

• If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a constant, the constant is converted to a timestamp before the comparison is performed. This is done to be more ODBC-friendly. Note that this is not done for the arguments to IN()! To be safe, always use complete datetime, date, or time strings when doing comparisons. For example, to achieve best results when using BETWEEN with date or time values, use CAST() to explicitly convert the values to the desired data type.
  A single-row subquery from a table or tables is not considered a constant. For example, if a subquery returns an integer to be compared to a DATETIME value, the comparison is done as two integers. The integer is not converted to a temporal value. To compare the operands as DATETIME values, use CAST() to explicitly convert the subquery value to DATETIME.

• If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

• In all other cases, the arguments are compared as floating-point (real) numbers.

翻译为中文就是：

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

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

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

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

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

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

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

问题描述

• where 条件语句里，字段属性和赋给的条件，当数据类型不一样，这时候是没法直接比较的，需要进行一致转换

• 默认转换规则是：

• 不同类型全都转换为浮点型（下文都说成整型了，一个意思）

• 如果字段是字符，条件是整型，那么会把表中字段全都转换为整型（也就是上面图中的问题，下面有详细解释）

转换总结

1. 字符转整型

• 字符开头的一律为0

• 数字开头的，直接截取到第一个不是字符的位置

• 按照字符串进行截取

• 23:12:13 -> 2023-12-13（这个后文有讨论）

• 对于不符合的时间值，如10:12:32等，会变为 0000-00-00 或为 空

• cast函数只能转datetime，不能转timestamp

• 如果按照timestamp来理解，因为timestamp是有范围的('1970-01-01 00:00:01.000000' to'2038-01-19 03:14:07.999999')，所以只能是2023年，而不能是1923年

• 直接截取time字段

• 直接截取date字段

• 无意义，直接为 00:00:00

• Append 00:00:00

• ##date to datetime or timestamp

• date to time

• datetime or timestamp to date

• datetime or timestamp to time

• time to datetime or timestamp

• When time and datetime are converted to numbers, they will become double precision, plus ms (different versions)

• Table structure, the name field has an index

-- 注意name字段是有索引的CREATE TABLE `t3` (  `id` int(11) NOT NULL,  `c1` int(11) NOT NULL,  `name` varchar(100) NOT NULL DEFAULT 'fajlfjalfka',  KEY `name` (`name`),  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

Copy after login
rrree

• If the condition is written as 0 or 1, the entire table will be scanned. All name fields need to be converted from characters to integers, and then compared with 0 or 1. Since they are all characters starting with a letter, they will all be converted to 0, and the returned result is all rows.

• Then someone asked, why not automatically change

  0 in the condition to '0'? see below.

-- 模拟线上一个隐式转换带来的全表扫面慢查询-- 发生隐式转换
xxxx.test> select * from t3 where name = 0;
+----+----+-------------+
| id | c1 | name        |
+----+----+-------------+
|  1 |  2 | fajlfjalfka |
|  2 |  0 | fajlfjalfka |
|  1 |  2 | fajlfjalfka |
|  2 |  0 | fajlfjalfka |
+----+----+-------------+
4 rows in set, 4 warnings (0.00 sec)-- 上述SQL执行计划是全表扫描，扫描后，字符转整型，都是0，匹配上了条件，全部返回
xxxx.test> desc select * from t3 where name = 0;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t3    | ALL  | name          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)-- 加上单引号后，是走name索引的，非全表扫描
xxxx.test> desc select * from t3 where name = '0';
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | t3    | ref  | name          | name | 102     | const |    1 | Using index condition |
+----+-------------+-------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)-- 走索引，没返回
xxxx.test>  select * from t3 where name = '1';
Empty set (0.00 sec)

Copy after login

0 in where name = 0 to '0 '?

• If numbers are converted to characters, such as 0 to '0', the result of the query can only be that the field is equal to '0', but in fact, the data in the table, For example, 'a0', '00', these are actually the 0 that the user wants. After all, the user specified the number 0, so MySQL still takes the user's request as the standard. Otherwise, '00' will not be returned to the user. .

• With the above content, the question at the beginning can be explained.

• Can the example above be used to bypass authentication?

-- 字符开头，直接是0
xxxx.test> select cast('a1' as unsigned int) as test ;
+------+
| test |
+------+
|    0 |
+------+
1 row in set, 1 warning (0.00 sec)

xxxx.test> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'a1' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)-- 开头不是字符，一直截取到第一个不是字符的位置
xxxx.test> select cast('1a1' as unsigned int) as test ; 
+------+
| test |
+------+
|    1 |
+------+
1 row in set, 1 warning (0.00 sec)

xxxx.test> select cast('123a1' as unsigned int) as test ;
+------+
| test |
+------+
|  123 |
+------+
1 row in set, 1 warning (0.00 sec)-- 直接按照字符截取，补上了20（不能补19）
xxxx.test> select cast('23:12:13' as datetime) as test ;
+---------------------+
| test                |
+---------------------+
| 2023-12-13 00:00:00 |
+---------------------+
1 row in set (0.00 sec)-- 为什么不能转换为timestamp，没搞清楚，官方文档给的转换类型里没有timestamp。如果是这样的话，上面的datetime就不好解释为什不是1923了。难道是检测了当前的系统时间？
xxxx.test> select cast('23:12:13' as timestamp) as test ;    
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'timestamp) as test' at line 1-- 这个时间无法转换成datetime
xxxx.test> select cast('10:12:32' as datetime) as test ;         
+------+
| test |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

xxxx.test> show warnings ;
+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: '10:12:32' |
+---------+------+--------------------------------------+
1 row in set (0.00 sec)-- 5.5版本下，时间转字符，会增加ms
xxxx.(none)> select version();
+------------+
| version()  |
+------------+
| 5.5.31-log |
+------------+
1 row in set (0.00 sec)

xxxx.(none)> select CURTIME(), CURTIME()+0, NOW(), NOW()+0 ;
+-----------+---------------+---------------------+-----------------------+
| CURTIME() | CURTIME()+0   | NOW()               | NOW()+0               |
+-----------+---------------+---------------------+-----------------------+
| 15:40:01  | 154001.000000 | 2016-05-06 15:40:01 | 20160506154001.000000 |
+-----------+---------------+---------------------+-----------------------+
1 row in set (0.00 sec)-- 5.6 不会
xxxx.test> select version();
+------------+
| version()  |
+------------+
| 5.6.24-log |
+------------+
1 row in set (0.00 sec)

xxxx.test> select CURTIME(), CURTIME()+0, NOW(), NOW()+0 ;
+-----------+-------------+---------------------+----------------+
| CURTIME() | CURTIME()+0 | NOW()               | NOW()+0        |
+-----------+-------------+---------------------+----------------+
| 15:40:55  |      154055 | 2016-05-06 15:40:55 | 20160506154055 |
+-----------+-------------+---------------------+----------------+
1 row in set (0.00 sec)

Copy after login

The above is the detailed content of Implicit conversion method in MySQL. For more information, please follow other related articles on the PHP Chinese website!