详解隐秘的 MySQL 类型转换问题详解
目录 1、问题开篇 1、字符串类型查询 2、数值型查询 2、问题引申 3、跟进探究 3.1 什么是隐式类型转换？ 3.2 如何避免隐式类型转换？ 3.2.1 清楚转换规则 3.2.2 使用内置函数显示转换 3
目录1、问题开篇1、字符串类型查询2、数值型查询2、问题引申3、跟进探究3.1 什么是隐式类型转换？3.2 如何避免隐式类型转换？3.2.1 清楚转换规则3.2.2 使用内置函数显示转换3.2.3 类型保持一致3.3 字符类型转换4、总结1、问题开篇
本文来自于我近期工作中遇到的一个真实问题，稍作整理后分享给大家~
一张用户表，其中 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'; -> 2 mysql> SELECT CONCAT(2,' test'); -> '2 test'
很明显，上面的 SQL 语句的执行过程中就出现了隐式转化。
从结果我们可以判定，SQL1 中将字符串的“1”转换为数字 1，而在 SQL2 中，将数字 2 转换为字符串“2”。
