MySQL 隐式转换

背景

平时在使用 MySQL 的时候发现即使字段类型是 int，但是拿字符串去查询也能查到。那这是为什么呢？背后自然是 MySQL 帮我们自动转换了。

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

隐式转换规则

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

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

其他转化规则：

  • 如果字段是字符，条件是整型，那么会把表中字段全都转换为整型，转化规则如下：

    1. 字符开头的一律为 0。
    2. 数字开头的，直接截取到第一个不是字符的位置。

  • 如果字段是整型，条件是字符，那么会把条件转换为整型，但转化为整型是唯一确定的。所以虽然需要隐式转换，但不影响使用索引，不会导致慢查询。转化规则同上。

  • 时间类型转换

    1. date 转 datetime 或者 timestamp 追加 00:00:00。
    2. date 转 time 无意义，直接为 00:00:00。
    3. datetime 或者 timestamp 转 date 直接截取date字段。
    4. datetime 或者 timestamp 转 time 直接截取time字段。
    5. time 转 datetime 或者 timestamp 按照字符串进行截取 23:12:13 -> 2023-12-13。
    6. cast 函数只能转 datetime，不能转 timestamp。
      如果按照 timestamp 来理解，因为 timestamp 是有范围的(‘1970-01-01 00:00:01.000000’ to’2038-01-19 03:14:07.999999’)，所以只能是 2023 年，而不能是 1923 年。
      对于不符合的时间值，如 10:12:32 等，会变为 0000-00-00 或为 空。
    7. time 和 datetime 转换为数字时，会变为双精度，加上 ms（版本不同不一样）。

示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
mysql> select '55aaa' = 55;
+--------------+
| '55aaa' = 55 |
+--------------+
| 1 |
+--------------+
1 row in set, 1 warning (0.00 sec)
 
mysql> select 'a' + '55';
+------------+
| 'a' + '55' |
+------------+
| 55 |
+------------+
1 row in set, 1 warning (0.00 sec)


-- 发生隐式转换
-- 如果条件写0或者1，会进行全表扫面，需要把所有的name字段由字符全都转换为整型，再和0或者1去比较。由于都是字母开头的字符，会全都转为为0，返回的结果就是所有行。
mysql> select * from t1 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，匹配上了条件，全部返回
mysql> desc select * from t1 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)

-- 走索引，没返回
mysql>  select * from t3 where name = '1';
Empty set (0.00 sec)

-- 加上单引号后，是走name索引的，非全表扫描
mysql> desc select * from t1 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)

总结

  1. 当操作符左右两边的数据类型不一致时，会发生隐式转换。
  2. 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
  3. 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
  4. 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

Reference

MySQL
#MySQL
MySQL 隐式转换
https://flepeng.github.io/041-MySQL-41-底层原理-MySQL-隐式转换/
作者
Lepeng
发布于
2020年8月8日
许可协议