MySQL之隐式转换 inexplicit conversion
之前也总给业务优化SQL，隐式转换也非常常见，但是下面这个图，一下子还真解释不清楚。
下面的内容，最终就是为了解释下面这个图。
问题描述
- where 条件语句里，字段属性和赋给的条件，当数据类型不一样，这时候是没法直接比较的，需要进行一致转换
- 默认转换规则是：
- 不同类型全都转换为浮点型（下文都说成整型了，一个意思）
- 如果字段是字符，条件是整型，那么会把表中字段全都转换为整型（也就是上面图中的问题，下面有详细解释）
转换总结
- 字符转整型
- 字符开头的一律为0
- 数字开头的，直接截取到第一个不是字符的位置
- 时间类型转换
- 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（版本不同不一样）
- date 转 datetime 或者 timestamp
案例分析
- 表结构，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 |
+---------------------+
1 row in set (0.00 sec)
