mysql8如何避免隐式转换 mysql属性隐式转换

转载

技术笔耕者 2023-07-01 14:33:15

文章标签 mysql8如何避免隐式转换字段隐式转换整型 文章分类 MySQL 数据库

在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'，是唯一确定的。所以虽然需要隐式转换，但不影响使用索引，不会导致慢查询。

问题如下下所示

mysql8如何避免隐式转换 mysql属性隐式转换_隐式转换

问题描述
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 |
+---------------------+
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类型的数值可以是整型或者是字符串类型都是可以的，但是字符串类型的数据的数值必须是字符串，否则会进行全表扫描，不走索引。

郭慕荣

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。