Mysql隐式类型转换

概述

在开发规范中，我们往往会要求研发避免在where条件中出现隐式类型转换，这么要求大概有以下两方面的原因：

• 隐式类型转换可能导致索引失效；
• 隐式类型转换可能产生非预期的结果。

何为隐式转换：即在where语句中条件的值和条件对应的列的数据类型不一致。如 where id=‘123’，而id的类型为bigint

数据准备

假设有表和数据如下：

CREATE TABLE `convert_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `areacode` varchar(12) NOT NULL DEFAULT '',
  `bus_date` DATE NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_areacode` (`areacode`),
  KEY `idx_bus_date` (`bus_date`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='隐式转换测试表';

insert into convert_test(areacode,bus_date) values ('001','2021-02-19');
insert into convert_test(areacode,bus_date) values ('1','2021-02-20');
insert into convert_test(areacode,bus_date) values ('1abc','2021-02-21');

隐式类型转换可能导致索引失效

假设有查询语句：
select * from convert_test where areacode=001;

执行计划如下：

---

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	convert_test	NULL	ALL	idx_areacode	NULL	NULL	NULL	3	33.33	Using where

因为上述sql存在隐式转换，导致索引失效，areacode为字符串而非整型。

select * from convert_test where areacode=‘001’;

执行计划如下：

---

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	convert_test	NULL	ref	idx_areacode	idx_areacode	38	const	1	100.00	NULL

隐式类型转换可能产生非预期的结果

select * from convert_test where id=‘1aa’;

因为id为bigint，而查询条件中为字符串，因此有隐式转换，执行结果如下：

---

id	areacode	bus_date	create_time	update_time
1	001	2021-02-19	2021-02-19 11:01:13	2021-02-19 11:01:13

按说上述条件的查询结果应该为空才对，为什么会将id为1的数据查询出来呢？那是因为将’1aa’强制转换为int时，只保留了1，而aa被抛弃掉。

再来一个例子：select * from convert_test where areacode=1;

---

id	areacode	bus_date	create_time	update_time
1	001	2021-02-19	2021-02-19 11:01:13	2021-02-19 11:01:13
2	1	2021-02-20	2021-02-19 11:01:13	2021-02-19 11:01:13
3	1abc	2021-02-21	2021-02-19 11:01:13	2021-02-19 11:01:13

因为areacode为字符串，而查询条件中为整型，因此有隐式转换。

防止隐式类型转换

1. 写sql的时候注意类型保持一致
2. 使用cast函数进行转换 如：select * from convert_test where areacode=CAST(1 AS char);

日期类型和时间类型

可以使用相关函数进行转换，也可以直接使用字符串，字符串的格式必须符合日期或时间格式。如yyyy/MM/dd 或者yyyy-MM-dd HH:mm:ss。

假设数据如下：
convert_test

id	areacode	bus_date	create_time	update_time
1	001	2021-02-19	2021-02-19 13:48:14	2021-02-19 13:48:14
2	1	2021-02-20	2021-02-19 13:48:20	2021-02-19 13:48:20
3	1abc	2021-02-21	2021-02-19 13:48:26	2021-02-19 13:48:26

时间类型的隐式转换不会使索引失效

EXPLAIN select * from convert_test WHERE create_time=‘2021-02-19 13:48:14’;

---

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	convert_test	NULL	ref	idx_create_time	idx_create_time	4	const	1	100.00	NULL

时间类型的隐式转换不会产生非预期的结果

select * from convert_test WHERE create_time=‘2021-02-19 13:48:14’;

---

id	areacode	bus_date	create_time	update_time
1	001	2021-02-19	2021-02-19 13:48:14	2021-02-19 13:48:14

日期类型的隐式转换不会使索引失效

EXPLAIN select * from convert_test WHERE bus_date=‘2021-02-20’;

---

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	convert_test	NULL	ref	idx_bus_date	idx_bus_date	3	const	1	100.00	NULL

日期类型的隐式转换不会产生非预期的结果

select * from convert_test WHERE bus_date=‘2021/02/20’;

---

id	areacode	bus_date	create_time	update_time
2	1	2021-02-20	2021-02-19 13:48:20	2021-02-19 13:48:20