成立公司怎么做网站,惠州网站建设技术外包,wordpress轩小程序,北京环球影城寄存柜一、前言
在一个阳光明媚的下午#xff0c;我们的测试在运行SQL是发现了一个灵异事件。 别着急#xff0c;等我慢慢说来#xff0c;是一个查询库存的SQL#xff0c;控制台打印了#xff0c;查询为0条记录。 想着不太信#xff0c;自己把SQL粘出来执行一下#xff0c;刚…一、前言
在一个阳光明媚的下午我们的测试在运行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); 2. 测试查询
我们先以int类型查询varchar作为测试
SELECT * FROM str_test WHERE str_column 123;大家是不是认为这里只能查询出一条数据答案是错误的我们后面统一说结论这里先看测试 我们在插入一条str_column位数超过18位的让转化是丢失精度从而实现多查的情况
我们看到查询的和被查询出来的是不一样的 我们在以varchar来查询int字段
SELECT * FROM str_test WHERE int_column 12A333;还是可以查询到数据 3. 结论
经过上面的测试是不是已经汗流浃背了不要慌下面我们来揭晓答案
有兴趣的可以看看官网文档MySQL5.7文档 当整数与字符串进行比较时无论数据库是int还是varchar只要类型不一致时MySQL会尝试将字符串转换为整数进行比较。 如果字符串以有效的数字开头则将其转换为相应的整数值。 解析规则从开头解析直到遇到非数字的字符结束前面的会作为比较的值非数字后面的直接抛弃。 如果字符串以非数字字符开头将被转化为0。 数值过大时回传精度损失也会出现匹配。没找到具体的临界值超过18位会出现浮点数精度损失 三、隐式转换的缺点
精度损失 隐式转换可能导致精度损失问题上面我们演示过了。
性能开销 在进行大规模数据处理时频繁的隐式转换可能会对性能产生影响。
索引失效存在隐式转换会让优化器无法使用索引进行优化查询影响响应时间。
数据安全风险如果是一个删除语句像上面演示的会出现匹配到其他行从而导致数据被误删。还有多查的问题。
四、总结
当然这个其实也是一个面试题大家是不是已经会了
之前在MySQL索引失效时就了解过隐式转换只知道会转换今天才有了更深刻的认识。
其实这个情况我们还是要避免的不能是MySQL不给我们报错我们就这样不规范的写。
这要是生产上一个删除匹配到多条和删库跑路性质一样了
大家还是要小心哈一定不要出现隐式转换
