故障分析 | MySQL 的隐式转换导致诡异现象的案例一则

2023-09-09 11:12:31 浏览数 (1)

1背景

同事问了个 MySQL 的问题，现象上确实诡异。大致意思是 SELECT 表的数据，WHERE 条件是 "a=0"，其中 a 字段是 VARCHAR 类型，该字段存在 NULL 以及包含字符的记录，但是并无 "0" 的记录，然后执行 SQL 返回的记录恰恰就是所有包含字符的记录。

明明没有 "0" 值记录，却可以返回，而且有规律，这是什么现象？

代码语言：javascript复制
select * from test where a = 0;

2问题分析

为了比对说明，我们分别用 MySQL、Oracle 和 SQL Server 进行模拟。

2.1 准备测试表

三种数据库建表和插入数据的语句。

MySQL

代码语言：javascript复制
create table test (id int, a varchar(3000), b varchar(2000));
insert into test values(1, '测试a', '测试b'),(2, NULL, '测试');

Oracle

代码语言：javascript复制
create table test (id NUMBER(1), a varchar2(3000), b varchar2(2000));
insert into test values(1, '测试a', '测试b');
insert into test values(2, NULL, '测试');

SQL Server

代码语言：javascript复制
create table test (id numeric(1,0), a varchar(3000), b varchar(2000));
insert into test values(1, '测试a', '测试b');
insert into test values(2, NULL, '测试');

2.2 对比查询结果

预期 test 表返回的记录都应该是这样的。

id

a

b

1

测试a

测试b

2

NULL

测试

我们看下三种数据库中，都执行如下语句，得到的是什么。

代码语言：javascript复制
select * from test where a = 0;

MySQL

执行返回如下带字符的记录，但实际逻辑上肯定是错的。

id

a

b

1

测试a

测试b

执行时，还会抛出一个 warning:Truncated incorrect DOUBLE value: '测试a'。

Oracle

执行直接报错，提示"无效数字"，因为 a 是 VARCHAR2、0 是数字，因此报错是针对字段 a 的，需要将 a 转成数字，但字符是无法转成数字的，所以提示 "无效数字" 是合情合理的。

代码语言：javascript复制
ORA-01722: 无效数字

SQL Server

执行直接报错，但是提示信息更加清晰明了，说的就是字段 a 的值 "测试a" 不能转成 INT 数值型。

代码语言：javascript复制
SQL 错误 [245] [S0001]: 在将 varchar 值 '测试a' 转换成数据类型 int 时失败。

小结

通过以上对比，可以知道 Oracle 和 SQL Server 对 "字符型=数值型" 的条件，会自动将字符型类型转成数值型，如果因为值的问题不能转成数值型，就会提示错误，而 SQL Server 给出的提示，比 Oracle 更具体。

相比之下，MySQL 针对 "字符型=数值型" 的条件，不仅能执行，而且执行是错的，这就很拉垮了。毕竟对产品来说，避免错误可能比表面上能执行更加重要，但就这个问题上，Oracle 和 SQL Server 可以说更胜一筹的。

2.3 问题分析

MySQL 为什么在这里会给出错误的结果？

从官方文档[1] 的这几段内容，我们可以得到一些线索，

MySQL 中将 VARCHAR 转成 INT，会自动截断字符串，例如 "1测试" 会截成 "1" ，通过如下判断，可以证明。

代码语言：javascript复制
bisal@mysqldb 23:26:  [test]> select 1="1测试a";
 -------------- 
| 1="1测试a"   |
 -------------- 
|            1 |
 -------------- 
1 row in set, 1 warning (0.00 sec)

上述例子中 "测试a" 会截成 ""，因此 a=0 ，才会返回字段不为空的。

代码语言：javascript复制
bisal@mysqldb 23:27:  [test]> select 0="测试a";
 ------------- 
| 0="测试a"   |
 ------------- 
|           1 |
 ------------- 
1 row in set, 1 warning (0.00 sec)

通过 0 和 "" 进行比较，则可以进一步证明这个问题。

代码语言：javascript复制
bisal@mysqldb 23:29:  [test]> select 0="";
 ------ 
| 0="" |
 ------ 
|    1 |
 ------ 
1 row in set (0.00 sec)

因此，正是因为 MySQL 对字符串进行隐式转换时会截断再转，而不是像 Oracle、SQL Server 这些数据库针对这种问题直接报错，所以才出现了这个诡异的问题。

3总结

我不知道这种设计是出于什么考虑，但这种"容错性"不可取，毕竟返回了错误的结果集。

当然，这个问题也和数据类型的使用有关，SQL 条件中 "a=0" 实际上是 "varchar=int"。两边类型不一致，所以才导致了数据库的隐式转换。

有可能是数据库设计的问题，比如，字段应该是 INT，但是定义成了 VARCHAR；还可能使开发人员的问题（SQL 条件右值应该用字符类型，例如 "0"，但实际上用了 INT 数值类型的 0）。

总之，按照数据库设计开发规范的要求，"=" 号两边的数据类型保持一致，这就不会引发数据库的隐式转换。

参考资料

[1]

type-conversion: https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

本文关键字：#MySQL# #数据类型# #隐式转换#

阅读推荐

技术分享 | MySQL 覆盖索引优化案例一则

技术分享 | Windows 下 MySQL 源码学习环境搭建步骤【建议收藏】

故障解析 | MySQL 主从复制遇到 1590 报错

故障解析 | 生产环境遇到 MySQL 数据页损坏问题如何解决？

新特性解读 | MySQL 8.0 字段信息统计机制

故障分析 | 如何通过 blktrace 排查磁盘异常？

SQLE 获取

数据库 mysql 测试 数据 数据类型

0 人点赞