详解隐秘的 MySQL 类型转换问题详解

2022-11-13 18:11:03 转换详解隐秘

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”。

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'
mysql> SELECT CONVERT(123, char);
-> '123'

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

EXPLAIN SELECT * FROM users WHERE phone = CAST(123 AS CHAR);

结果所示：

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

3.2.3 类型保持一致

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

3.3 字符类型转换

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

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

从上面的例子可以得出：

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

4、总结

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

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

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

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

以上就是详解隐秘的 MySQL 类型转换问题详解的详细内容，更多关于MySQL 类型转换的资料请关注其它相关文章！

相关文章

详解隐秘的 MySQL 类型转换问题详解

目录

1、问题开篇

1、字符串类型查询

2、数值型查询

2、问题引申

3、跟进探究

3.1 什么是隐式类型转换？

3.2 如何避免隐式类型转换？

3.2.1 清楚转换规则

3.2.2 使用内置函数显示转换

3.2.3 类型保持一致

3.3 字符类型转换

4、总结