MySQL中Varchar和Int隐式转换的小秘密

2023-12-12 微信公众号小王博客基地

一、前言

在一个阳光明媚的下午，我们的测试在运行SQL时发现了一个灵异事件。别着急，等我慢慢说来，是一个查询库存的SQL，控制台打印了，查询为0条记录。想着不太信，自己把SQL粘出来执行一下，「刚好有个varchar类型的字段，查询的是一堆数字，忘记加引号了。」结果查询出来了一条！

从头看到结尾，发现我们查询条件的字段值为231120103，把数据库中231120103-1的查询出来了！

经过半天的探索，「发现这是MySQL优化器中，判断数据类型不匹配的比较时，MySQL 优化器会进行隐式类型转换！」

下面我们一起来看看这个隐式转换，到底是怎么转换的！

要知其然，知其所以然。

二、实践出真知

1、建表

CREATE TABLE `str_test`  (
  `id` int(0) NOT NULL,
  `str_column` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `int_column` int(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

我们新建一个表，里面有varchar和int类型，插入几条方便测试！

INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (1, '123', 123);
INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (2, '123-1---1122', 12);
INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (3, 'abc', 1);
INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (4, '783221667772672728', 2147483647);
INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (5, '783221667772672798', 2147483647);
INSERT INTO `test`.`str_test`(`id`, `str_column`, `int_column`) VALUES (6, '0', 0);

MySQL中Varchar和Int隐式转换的小秘密

2、测试查询

我们先以int类型查询varchar作为测试：

SELECT * FROM `str_test` WHERE str_column = 123;

大家是不是认为这里只能查询出一条数据，答案是错误的！我们后面统一说结论，这里先看测试！

MySQL中Varchar和Int隐式转换的小秘密

我们在插入一条str_column位数超过18位的！让转化时丢失精度，从而实现多查的情况！

我们看到查询的和被查询出来的是不一样的！

MySQL中Varchar和Int隐式转换的小秘密

我们在以varchar来查询int字段：

SELECT * FROM `str_test` WHERE int_column = '12A333';

还是可以查询到数据！

MySQL中Varchar和Int隐式转换的小秘密

3、结论

经过上面的测试是不是已经汗流浃背了！不要慌，下面我们来揭晓答案！

有兴趣的可以看看官网文档：MySQL5.7文档：https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

❝

当整数与字符串进行比较时，无论数据库是int还是varchar，只要类型不一致时，MySQL会尝试将字符串转换为整数进行比较。

如果字符串以有效的数字开头，则将其转换为相应的整数值。

解析规则：从开头解析直到遇到非数字的字符结束，前面的会作为比较的值，非数字后面的直接抛弃。

如果字符串以非数字字符开头，将被转化为0。

数值过大时，回传精度损失，也会出现匹配。没找到具体的临界值，超过18位会出现浮点数精度损失！

❞

三、隐式转换的缺点

精度损失：隐式转换可能导致精度损失问题，上面我们演示过了。
性能开销：在进行大规模数据处理时，频繁的隐式转换可能会对性能产生影响。
索引失效：存在隐式转换会让优化器无法使用索引进行优化查询，影响响应时间。
数据安全风险：如果是一个删除语句，像上面演示的会出现匹配到其他行，从而导致数据被误删。还有多查的问题。

四、总结

当然这个其实也是一个面试题，大家是不是已经会了！

之前在MySQL索引失效时，就了解过隐式转换，只知道会转换，今天才有了更深刻的认识。

其实这个情况我们还是要避免的，不能是MySQL不给我们报错，我们就这样不规范的写。

这要是生产上，一个删除匹配到多条，和删库跑路性质一样了！

大家还是要小心哈，一定不要出现隐式转换！