服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - MySQL中Varchar和Int隐式转换的小秘密

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

2023-12-13 01:00未知服务器之家 Mysql

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

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

一、前言

在一个阳光明媚的下午,我们的测试在运行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不给我们报错,我们就这样不规范的写。

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

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

延伸 · 阅读

精彩推荐