<返回更多

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不给我们报错,我们就这样不规范的写。

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

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

关键词:MySQL      点击(8)
声明:本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系,我们将立即删除或处理。
▍相关推荐
更多MySQL相关>>>