MySQL 类型转换的隐秘逻辑：你可能踩过的坑

最新推荐文章于 2025-08-09 20:21:24 发布

架构精进之路

最新推荐文章于 2025-08-09 20:21:24 发布

阅读量658

点赞数 21

CC 4.0 BY-SA版权

文章标签： mysql android 数据库

本文链接：https://blog.csdn.net/zhanghuan0126/article/details/149214093

在 MySQL 中，有很多看上去逻辑相同，但性能却差异巨大的 SQL 语句。对这些语句使用不当的话，就会不经意间导致整个数据库的压力变大。

今天精选真实案例与你分享，希望再遇到相似的问题时，你可以做到举一反三、快速解决问题。

1、问题开篇

一张用户表 `users` ，其中字段 `phone` 添加了普通索引。

CREATETABLEusers (idbigint(20) unsignedNOTNULL AUTO_INCREMENT COMMENT'主键ID',namevarchar(32) COLLATE utf8mb4_unicode_ci NOTNULLDEFAULT''COMMENT'名称',  phone varchar(16) COLLATE utf8mb4_unicode_ci NOTNULLDEFAULT''COMMENT'手机',  created_at timestampNOTNULLDEFAULT'1970-01-01 16:00:00'COMMENT'创建时间',  updated_at timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',  PRIMARY KEY (id),KEY idx_phone (phone)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表';

分别执行以下SQL：

1）字符串类型查询

EXPLAINSELECT * FROMusersWHERE phone = '2';

执行计划如下：

2）数值型查询

EXPLAINSELECT * FROMusersWHERE phone = 2;

执行计划如下：

发现问题：

当索引字段 `phone` 为字符串类型时，字符串查询时候使用了索引`idx_phone`，而数值类型查询时候竟无法使用索引`idx_phone`。

2、问题引申

假如索引字段为整型的话，那用字符串查询时会不会走索引呢？

实践出真知，我们来验证一下。

同样如上表，修改字段 `phone` 类型由 varchar 变更为 bigint：

ALTERTABLEusersMODIFYCOLUMN phone bigint(16) NOTNULLCOMMENT'手机';

然后，分别执行以下SQL：

1）字符串类型查询

EXPLAINSELECT * FROMusersWHERE phone = '2';

执行计划如下：

2）数值型查询

EXPLAINSELECT * FROMusersWHERE phone = 2;

执行计划如下：

执行后发现，无论是以字符串查询还是以数值型查询都会用到索引。

小结：

当索引字段是数值类型时，数值型或者字符型查询都不影响索引的使用。
当索引字段是字符类型时，数值型查询无法使用索引，字符型查询可正常使用索引。

3、跟进探究

为什么会是这样呢？其根源就是MySQL的隐式类型转换。

3.1 什么是隐式类型转换？

在MySQL中，当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容，则会发生隐式类型转换。

即 MySQL会根据需要自动将数字转换为字符串，或者将字符串转换为数字。

mysql> SELECT 1+'1';-> 2mysql> 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，并且另外一个参数是常量，常量会被转换为 timestamp6、有一个参数是 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(123aschar);-> '123'mysql> SELECT CONVERT(123, char);-> '123'

假如应用在开篇描述问题的查询中，则如下所示：

EXPLAINSELECT * FROMusersWHERE phone = CAST(123ASCHAR);

执行计划所示：

结果显示同应用字符串类型参数一样，可使用索引`idx_phone`。

3.2.3 类型保持一致

最简单的一种，保证查询应用规范，SQL参数类型与数据库中字段类型保持一致即可。

3.3 字符类型转换

另外，关于字符串类型转换的一些补充：

mysql> select '1a2b3c' = 1;-> 1mysql> select 'a1b2c3' = 0;-> 1

从上面的例子可以得出：

如果字符串的第一个字符就是非数字的字符，那么转换为数字就是0；
如果字符串以数字开头，那转换的数字就是开头的那些数字对应的值，直到遇到非数字字符才结束。

4、总结

本文主要从问题入手，继而进行问题引申，最终挖掘出问题根源：MySQL隐式类型转换。

同时也告诫我们日常在写SQL时一定要检查参数类型与数据库字段类型是否一致，否则可能造成隐式类型转换，不能正常应用索引，造成慢查询，甚至拖垮整个数据库服务集群。

如果参数不一致，也可以考虑使用CAST函数显性转换成一致类型。

数据表设计及应用绝非易事，需要考虑的因素太多了，大家应用过程注意保持敬畏心。

END

相关阅读：

专注架构技术研究，一起跨越职业瓶颈！

关注公众号，免费领学习资料

如果您觉得还不错，欢迎关注和转发~