likes
comments
collection
share

MySQL隐式转换还有这问题？

作者站长头像
站长
· 阅读数 12

前言

本篇文章主要内容：

1、介绍MySQL数据隐式转换是什么？

2、MySQL隐式转换带来的问题？

3、隐式转换产生问题的解决方案？

由于MySQL版本众多，本篇文章所述内容主要针对于 MySQL5.7 版本，其他版本情况可能有所不同。读者注意自行总结。

什么是MySQL隐式转换

MySQL隐式转换是指在MySQL中，当使用不同数据类型的数据进行运算或比较时，MySQL会自动将其中一个数据类型进行隐式转换，使其与另一个数据类型相兼容，以实现运算或比较操作的目的。具体来说，MySQL会将一个数据类型转换为另一个数据类型，而无需使用任何明确的CAST()函数。

例如，当将一个字符串类型的数字 1 与一个数字类型的数字 2 相加时，MySQL会自动将字符串类型的数字转换为数字类型的数字，使其与数字类型的数字相兼容，然后执行加法操作，并返回数字类型的结果 3，这就是MySQL的隐式转换。

MySQL隐式转换的目的是为了方便开发者进行数据库开发工作，使开发者不需要显式地进行数据类型转换，但是隐式转换往往会带来难以预料的错误，因此需要开发者特别注意隐式转换的使用。为了避免MySQL隐式转换带来的问题，应该在开发时尽量使用明确的CAST()函数来进行类型转换，以减少隐式转换导致的风险。

隐式转换发生情况

​ 1、字符串和数值相加

当使用“+”操作符连接数字和字符类型的数据时，MySQL会将字符串类型的数据自动转换为数字类型数据，然后执行相加操作。

例如：SELECT 1+'2'; 结果为3。2会被自动转换为数字类型的2。

​ 2、字符串和日期/时间相加

MySQL支持字符串与日期/时间的操作，如果两者相加，则MySQL将字符串转换为日期/时间，日期/时间与日期/时间相加的结果是日期/时间类型。

例如：SELECT DATE_ADD('2022-01-01', INTERVAL '1' DAY); 结果为'2022-01-02'。1会被自动转换为数字类型的1。

​ 3、MySQL自动转换

在使用一些MySQL函数时，MySQL会自动将不同类型的数据进行隐式的转换。

例如：SELECT CONVERT('2019-01-01', DATETIME); 结果为2019-01-01 00:00:00，字符串类型的日期被隐式转换为DATETIME类型。

​ 4、数据类型不一致

当在表中混合使用不同类型的数据时，MySQL会自动将其中一个类型转换为另一个类型来进行比较或操作。

例如：SELECT '2' < 1; 结果为0，'2'会被隐式转换为数字类型的2再与1进行比较。

需要注意的是，隐式类型转换可能会导致不希望的行为和结果，因此在开发和使用MySQL时，应该尽可能避免隐式转换。可以使用CAST()和CONVERT()等函数进行显示类型转换，也可以在表定义时设置字段的数据类型，来尽量规避隐式转换问题。

关于MySQL官网针对于MySQL5.7版本隐式转换相关介绍链接如下🔗：dev.mysql.com/doc/refman/…

数据隐式转换带来的问题

准备数据

准备以下字段的表，并为age列建立索引。

CREATE TABLE `table_student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '学生姓名',
  `age` int(11) DEFAULT NULL COMMENT '学生年龄',
  PRIMARY KEY (`id`),
  KEY `idx_age` (`age`) USING BTREE,
  KEY `idx_name_normal` (`name`) USING BTREE,
  FULLTEXT KEY `idx_name_fulltext` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

注意：此处为了让测试结果更明显，我们给name列建立了两种类型的索引：BTREE（NORMAL）、FULLTEXT

在数据库中插入以下数据：

INSERT INTO `table_student` ( `name`, `age` )
VALUES
	( '小明', 20 );
INSERT INTO `table_student` ( `name`, `age` )
VALUES
	( '小红', 21 );
INSERT INTO `table_student` ( `name`, `age` )
VALUES
	( '小刚', 22 );
INSERT INTO `table_student` ( `name`, `age` )
VALUES
	( '1001', 20 );
INSERT INTO `table_student` ( `name`, `age` )
VALUES
	( '1002', 21 );

MySQL隐式转换还有这问题？

查询数据

情况一：Number（数值） 查 String（字符串）
select * from `table_student` where `name` = 1001

MySQL隐式转换还有这问题？ 可以看到是能查出来数据的，这是因为MySQL隐式转换会把数值类型转换成字符串类型，那么我们加上EXPALIN分析一下。

explain select * from `table_student` where `name` = 1001

MySQL隐式转换还有这问题？

可以看到这条SQL语句并没有走索引

有人会问如果我让他强制走呢

explain select * from `table_student` force index(`idx_name_normal`) where `name` = 1001

explain select * from `table_student` force index(`idx_name_fulltext`) where `name` = 1001

MySQL隐式转换还有这问题？

MySQL隐式转换还有这问题？

可以看到结果也是一样的，MySQL并没有走索引。

情况二：String（字符串）查 Number（数值）
select * from `table_student` where `age` = '20'

MySQL隐式转换还有这问题？

可以看到是能查出来数据的，这是因为MySQL隐式转换会把字符串类型转换成数值类型，那么我们加上EXPALIN分析一下。

explain select * from `table_student` where `age` = '20'

MySQL隐式转换还有这问题？

可以看到，我们并没有让MySQL强制走索引，但MySQL自己也会走age列的索引。

相信有不少人都用过MyBtis框架，在其xml标签内有一个jdbcType属性，那么这个属性能帮我们解决隐式转换问题吗？

MySQL隐式转换还有这问题？

可以看出这个属性并不能帮我们解决隐式转换问题，所以大家要避开这个坑！

解决方案

结论

  • 当操作符左右两边的数据类型不一致时，会发生隐式转换。
  • 当 where 查询操作符左边为数值类型时发生了隐式转换，那么对效率影响不大，但还是不推荐这么做。
  • 当 where 查询操作符左边为字符类型时发生了隐式转换，那么会导致索引失效，造成全表扫描效率极低。
  • 字符串转换为数值类型时，非数字开头的字符串会转化为0，以数字开头的字符串会截取从第一个字符到第一个非数字内容为止的值为转化结果。

所以，最好的解决方案就是我们一定要养成良好的SQL习惯，where条件的列是什么类型我们就应该传递什么类型，尽量不让MySQL来做隐式转换，就如同上述的情况一，由于隐式转换导致了MySQL不会走索引，导致索引失效触发全表扫描的问题。

转载自:https://juejin.cn/post/7222558002909265980
评论
请登录