• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

mysql隐式转换

开发技术 开发技术 2周前 (04-07) 9次浏览

mysql查询中,当查询条件左右两侧类型不匹配的时候会发生隐式转换,可能导致查询无法使用索引。下面分析两种隐式转换的情况。
第一种情况:索引字段是varchar类型
select * from user where index_filed=2;
因为等号两侧类型不一致,因此会发生隐式转换,cast(index_filed as signed),然后和2进行比较。因为’2’,’   2’,’2a’都会转化成2,故MySQL无法使用索引只能进行全表扫描,造成了慢查询的产生。

第二种情况:索引字段是int类型
select * from user where index_filed=’2′;
这次等号右侧是‘2’,注意带单引号哟,左侧的索引字段是int类型,因此也会发生隐式转换,但因为int类型的数字只有2能转化为’2’,是唯一确定的。所以虽然需要隐式转换,但不影响使用索引,不会导致慢查询。

问题如下下所示

 

问题描述
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=latin11 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                |+———————+| 20231213 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 | 20160506 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 | 20160506 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()               |+———————+| 19990803 14:16:50 |+———————+1 row in set (0.00 sec)

 

mysql> select cast(’23:12:13′ as datetime) as test ;+———————+| test                |+———————+| 20231213 00:00:00 |+———————+1 row in set (0.00 sec)

 

查询示例如下所示:

 

CREATE TABLE `t_user` (

  `id` bigint(100) NOT NULL AUTO_INCREMENT,

  `password` varchar(255) DEFAULT NULL,

  `name` varchar(255) DEFAULT NULL,

  `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `nick_name` varchar(255) DEFAULT NULL,

  `sex` int(255) DEFAULT NULL,

  `birthday` datetime DEFAULT NULL,

  `class_id` int(11) DEFAULT NULL COMMENT ‘班级id’,

  PRIMARY KEY (`id`),

  KEY `index_phone` (`phone`) USING BTREE,

  KEY `index_birthday` (`birthday`) USING BTREE,

  KEY `index_name` (`name`,`age`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=1588138958133 DEFAULT CHARSET=utf8;

 

 

explain  select age from t_user where age = ’32’; — 走索引

explain  select age from t_user where age = 32; — 走索引

explain  select * from t_user where name = 32; — 不走索引

explain  select * from t_user where name = ’32’; — 走索引

explain  select * from t_user where birthday = ‘2020-09-09’;  — 走索引

explain  select * from t_user where DATE_FORMAT(birthday,’yyyy-MM-dd’) = ‘2020-09-09’; —不走索引

 

隐式转换如上所示:不要再列上进行函数计算,比如使用date_format函数等,int类型的数值可以是整型或者是字符串类型都是可以的,但是字符串类型的数据的数值必须是字符串,否则会进行全表扫描,不走索引。

mysql查询中,当查询条件左右两侧类型不匹配的时候会发生隐式转换,可能导致查询无法使用索引。下面分析两种隐式转换的情况。
第一种情况:索引字段是varchar类型
select * from user where index_filed=2;
因为等号两侧类型不一致,因此会发生隐式转换,cast(index_filed as signed),然后和2进行比较。因为’2’,’   2’,’2a’都会转化成2,故MySQL无法使用索引只能进行全表扫描,造成了慢查询的产生。

第二种情况:索引字段是int类型
select * from user where index_filed=’2′;
这次等号右侧是‘2’,注意带单引号哟,左侧的索引字段是int类型,因此也会发生隐式转换,但因为int类型的数字只有2能转化为’2’,是唯一确定的。所以虽然需要隐式转换,但不影响使用索引,不会导致慢查询。

问题如下下所示

 

问题描述
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=latin11 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                |+———————+| 20231213 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 | 20160506 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 | 20160506 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()               |+———————+| 19990803 14:16:50 |+———————+1 row in set (0.00 sec)

 

mysql> select cast(’23:12:13′ as datetime) as test ;+———————+| test                |+———————+| 20231213 00:00:00 |+———————+1 row in set (0.00 sec)

 

查询示例如下所示:

 

CREATE TABLE `t_user` (

  `id` bigint(100) NOT NULL AUTO_INCREMENT,

  `password` varchar(255) DEFAULT NULL,

  `name` varchar(255) DEFAULT NULL,

  `phone` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,

  `age` int(11) DEFAULT NULL,

  `nick_name` varchar(255) DEFAULT NULL,

  `sex` int(255) DEFAULT NULL,

  `birthday` datetime DEFAULT NULL,

  `class_id` int(11) DEFAULT NULL COMMENT ‘班级id’,

  PRIMARY KEY (`id`),

  KEY `index_phone` (`phone`) USING BTREE,

  KEY `index_birthday` (`birthday`) USING BTREE,

  KEY `index_name` (`name`,`age`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=1588138958133 DEFAULT CHARSET=utf8;

 

 

explain  select age from t_user where age = ’32’; — 走索引

explain  select age from t_user where age = 32; — 走索引

explain  select * from t_user where name = 32; — 不走索引

explain  select * from t_user where name = ’32’; — 走索引

explain  select * from t_user where birthday = ‘2020-09-09’;  — 走索引

explain  select * from t_user where DATE_FORMAT(birthday,’yyyy-MM-dd’) = ‘2020-09-09’; —不走索引

 

隐式转换如上所示:不要再列上进行函数计算,比如使用date_format函数等,int类型的数值可以是整型或者是字符串类型都是可以的,但是字符串类型的数据的数值必须是字符串,否则会进行全表扫描,不走索引。

 


程序员灯塔
转载请注明原文链接:mysql隐式转换
喜欢 (0)