MySQL之隐式转换，回头有时间了看下

MySQL之隐式转换 inexplicit conversion

 

之前也总给业务优化SQL，隐式转换也非常常见，但是下面这个图，一下子还真解释不清楚。

下面的内容，最终就是为了解释下面这个图。

 

问题描述

• where 条件语句里，字段属性和赋给的条件，当数据类型不一样，这时候是没法直接比较的，需要进行一致转换
• 默认转换规则是：
  • 不同类型全都转换为浮点型（下文都说成整型了，一个意思）
  • 如果字段是字符，条件是整型，那么会把表中字段全都转换为整型（也就是上面图中的问题，下面有详细解释）

转换总结

1. 字符转整型
   • 字符开头的一律为0
   • 数字开头的，直接截取到第一个不是字符的位置
2. 时间类型转换
   • date 转 datetime 或者 timestamp
     • 追加 00:00:00
   • date 转 time
     • 无意义，直接为 00:00:00
   • datetime 或者 timestamp 转 date
     • 直接截取date字段
   • datetime 或者 timestamp 转 time
     • 直接截取time字段
   • time 转 datetime 或者 timestamp
     • 按照字符串进行截取
     • 23:12:13 -> 2023-12-13（这个后文有讨论）
       • 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 或为 空
   • time和datetime转换为数字时，会变为双精度，加上ms（版本不同不一样）

案例分析

• 表结构，name字段有索引

-- 注意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=latin1
1 row in set (0.00 sec)

-- 模拟线上一个隐式转换带来的全表扫面慢查询

-- 发生隐式转换
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)

解释

• 如果条件写0或者1，会进行全表扫面，需要把所有的name字段由字符全都转换为整型，再和0或者1去比较。由于都是字母开头的字符，会全都转为为0，返回的结果就是所有行。
• 那有人问了，为什么不把条件里的 0 自动改成 '0' ？见下文。

转换举例

-- 字符开头，直接是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)

为什么不把 where name = 0 中的 0 转换为 '0' ？

• 如果是数字往字符去转换，如 0 转'0'，这样查询出来的结果只能是字段等于 '0'，而实际上，表里的数据，如'a0'，'00'，这其实都是用户想要的0，毕竟是用户指定了数字0，所以MySQL还是以用户发出的需求为准，否则，'00'这些都不会返回给用户。

总结

• 有了上面的内容，开头的问题是可以解释了。
• 上图的例子，是不是可以用来绕过身份验证？

补充

-- 上面遗留的问题，跟系统时间并没有关系。怀疑虽然指定的是datetime，但是内部还是按照timestamp去做的。
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 1999-08-03 14:16:50 |
+---------------------+
1 row in set (0.00 sec)

mysql> select cast('23:12:13' as datetime) as test ;
+---------------------+
| test                |
+---------------------+
| 2023-12-13 00:00:00 |
+---------------------+

作者：JackpGao
链接：https://www.jianshu.com/p/6f34e9708a80
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

MySQL之隐式转换 inexplicit conversion

 

之前也总给业务优化SQL，隐式转换也非常常见，但是下面这个图，一下子还真解释不清楚。

下面的内容，最终就是为了解释下面这个图。

 

 

问题描述

• where 条件语句里，字段属性和赋给的条件，当数据类型不一样，这时候是没法直接比较的，需要进行一致转换
• 默认转换规则是：
  • 不同类型全都转换为浮点型（下文都说成整型了，一个意思）
  • 如果字段是字符，条件是整型，那么会把表中字段全都转换为整型（也就是上面图中的问题，下面有详细解释）

转换总结

1. 字符转整型
   • 字符开头的一律为0
   • 数字开头的，直接截取到第一个不是字符的位置
2. 时间类型转换
   • date 转 datetime 或者 timestamp
     • 追加 00:00:00
   • date 转 time
     • 无意义，直接为 00:00:00
   • datetime 或者 timestamp 转 date
     • 直接截取date字段
   • datetime 或者 timestamp 转 time
     • 直接截取time字段
   • time 转 datetime 或者 timestamp
     • 按照字符串进行截取
     • 23:12:13 -> 2023-12-13（这个后文有讨论）
       • 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 或为 空
   • time和datetime转换为数字时，会变为双精度，加上ms（版本不同不一样）

案例分析

• 表结构，name字段有索引

-- 注意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=latin1
1 row in set (0.00 sec)

-- 模拟线上一个隐式转换带来的全表扫面慢查询

-- 发生隐式转换
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)

解释

• 如果条件写0或者1，会进行全表扫面，需要把所有的name字段由字符全都转换为整型，再和0或者1去比较。由于都是字母开头的字符，会全都转为为0，返回的结果就是所有行。
• 那有人问了，为什么不把条件里的 0 自动改成 '0' ？见下文。

转换举例

-- 字符开头，直接是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)

为什么不把 where name = 0 中的 0 转换为 '0' ？

• 如果是数字往字符去转换，如 0 转'0'，这样查询出来的结果只能是字段等于 '0'，而实际上，表里的数据，如'a0'，'00'，这其实都是用户想要的0，毕竟是用户指定了数字0，所以MySQL还是以用户发出的需求为准，否则，'00'这些都不会返回给用户。

总结

• 有了上面的内容，开头的问题是可以解释了。
• 上图的例子，是不是可以用来绕过身份验证？

补充

-- 上面遗留的问题，跟系统时间并没有关系。怀疑虽然指定的是datetime，但是内部还是按照timestamp去做的。
mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 1999-08-03 14:16:50 |
+---------------------+
1 row in set (0.00 sec)

mysql> select cast('23:12:13' as datetime) as test ;
+---------------------+
| test                |
+---------------------+
| 2023-12-13 00:00:00 |
+---------------------+

作者：JackpGao
链接：https://www.jianshu.com/p/6f34e9708a80
来源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。