[日期:2017-10-10] | [字体:大 中 小] |
隐式转化规则
官方文档中关于隐式转化的规则是如下描述的:
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.
翻译为中文就是:
- -- 注意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
?见下文。
- '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'
- -- 上面遗留的问题,跟系统时间并没有关系。怀疑虽然指定的是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)
来源: http://www.linuxidc.com/Linux/2017-10/147406.htm