隐秘的 MySQL 类型转换
近期工作中同事遇到的一个真实问题，稍作整理后分享给大家~
1、问题开篇
一张用户表 `users` ，其中字段 `phone` 添加了普通索引。
CREATE TABLE users (
id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
name varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '名称',
phone varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '手机',
created_at timestamp NOT NULL DEFAULT '1970-01-01 16:00:00' COMMENT '创建时间',
updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (id),
KEY idx_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';分别执行以下SQL：
1）字符串类型查询
EXPLAIN SELECT * FROM users WHERE phone = '2';执行计划如下：
2）数值型查询
EXPLAIN SELECT * FROM users WHERE phone = 2;执行计划如下：
发现问题：
当索引字段 `
phone
` 为字符串类型时，字符串查询时候使用了索引`
idx_phone
`，而数值类型查询时候竟无法使用索引`
idx_phone
`。
2、问题引申
假如索引字段为整型的话，那用字符串查询时会不会走索引呢？
实践出真知，我们来验证一下。
同样如上表，修改字段 `phone` 类型由 varchar 变更为 bigint：
ALTER TABLE users MODIFY COLUMN phone bigint(16) NOT NULL COMMENT '手机';然后，分别执行以下SQL：
1）字符串类型查询
EXPLAIN SELECT * FROM users WHERE phone = '2';执行计划如下：
2）数值型查询
EXPLAIN SELECT * FROM users WHERE phone = 2;执行计划如下：
执行后发现，无论是以字符串查询还是以数值型查询都会用到索引。
小结：
- 当索引字段是数值类型时，数值型或者字符型查询都不影响索引的使用。
- 当索引字段是字符类型时，数值型查询无法使用索引，字符型查询可正常使用索引。
3、跟进探究
为什么会是这样呢？其根源就是MySQL的 隐式类型转换 。
3.1 什么是隐式类型转换？
在MySQL中，当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容，则会发生隐式类型转换。
即 MySQL会根据需要自动将数字转换为字符串，或者将字符串转换为数字。
mysql> SELECT 1+'1';
mysql> SELECT CONCAT(2,' test');
-> '2 test'很明显，上面的SQL语句的执行过程中就出现了隐式转化。
从结果我们可以判定，SQL1中将字符串的“1”转换为数字1，而在SQL2 中，将数字2转换为字符串“2”。
3.2 如何避免隐式类型转换？
3.2.1 清楚转换规则
只有当清楚的知道隐式类型转换的规则，才能从根本上避免产生隐式类型转换。
参考MySQL文档相关描述，确定隐式类型转换规则：
1、两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换
2、两个参数都是字符串，会按照字符串来比较，不做类型转换
3、两个参数都是整数，按照整数来比较，不做类型转换
4、十六进制的值和非数字做比较时，会被当做二进制串
5、有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp
6、有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较
7、所有其他情况下，两个参数都会被转换为浮点数再进行比较验证示例：
mysql> SELECT 'aa' + 1;
-> '1'
mysql> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa' |
+---------+------+----------------------------------------+上述示例中，将字符串 'aa' 和1进行求和，因为 'aa' 和数字1的类型不同，通过上述转换规则并且经查看warnings可以确认：隐式类型转化将字符串转为了 double 类型。
由于字符串是非数字型的，所以就会被转换为0，因此计算结果：0+1=1
3.2.2 使用内置函数显示转换
MySQL对数据进行类型转换，提供了cast() 和 convert()。
相同点 ：两者都是进行数据类型转换，实现的功能基本等同
不同点 ：两者的语法不同：cast(value as type) 、 convert(value,type)
将数值型转换为字符串型，应用示例如下：
mysql> SELECT CAST(123 as char);
-> '123'