关于mysql:MySQL-的隐式转换导致诡异现象的案例一则

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

作者：刘晨

网名 bisal，具备十年以上的利用运维工作教训，目前次要从事数据库利用研发能力晋升和技术治理相干的工作，Oracle ACE（Alumni），腾讯云 TVP，领有 Oracle OCM & OCP、EXIN DevOps Master、SCJP 等国内认证，国内首批 Oracle YEP 成员，OCMU 成员，《DevOps 最佳实际》中文译者之一，CSDN & ITPub 专家博主，公众号”bisal 的集体杂货铺”，长期保持分享技术文章，屡次在线上和线下分享技术主题。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

背景

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

明明没有 "0" 值记录，却能够返回，而且有法则，这是什么景象？

select * from test where a = 0;

问题剖析

为了比对阐明，咱们别离用 MySQL、Oracle 和 SQL Server 进行模仿。

2.1 筹备测试表

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

MySQL

create table test (id int, a varchar(3000), b varchar(2000));
insert into test values(1, '测试 a', '测试 b'),(2, NULL, '测试');

Oracle

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

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 表返回的记录都应该是这样的。

咱们看下三种数据库中，都执行如下语句，失去的是什么。

select * from test where a = 0;

MySQL

执行返回如下带字符的记录，但理论逻辑上必定是错的。

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

Oracle

执行间接报错，提醒 ” 有效数字 ”，因为 a 是 VARCHAR2、0 是数字，因而报错是针对字段 a 的，须要将 a 转成数字，但字符是无奈转成数字的，所以提醒 “ 有效数字 ” 是荒诞不经的。

ORA-01722: 有效数字 

SQL Server

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

SQL 谬误 [245] [S0001]: 在将 varchar 值 '测试 a' 转换成数据类型 int 时失败。

小结

通过以上比照，能够晓得 Oracle 和 SQL Server 对 “ 字符型 = 数值型 ” 的条件，会主动将字符型类型转成数值型，如果因为值的问题不能转成数值型，就会提醒谬误，而 SQL Server 给出的提醒，比 Oracle 更具体。

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

2.3 问题剖析

MySQL 为什么在这里会给出谬误的后果？

从官网文档 的这几段内容，咱们能够失去一些线索，

MySQL 中将 VARCHAR 转成 INT，会主动截断字符串，例如 "1 测试" 会截成 "1"，通过如下判断，能够证实。

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，才会返回字段不为空的。

bisal@mysqldb 23:27:  [test]> select 0="测试 a";
+-------------+
| 0="测试 a"   |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

通过 0 和 "" 进行比拟，则能够进一步证实这个问题。

bisal@mysqldb 23:29:  [test]> select 0="";
+------+
| 0="" |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

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

总结

我不晓得这种设计是出于什么思考，但这种 ” 容错性 ” 不可取，毕竟返回了谬误的后果集。

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

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

总之，依照数据库设计开发标准的要求，"=" 号两边的数据类型保持一致，这就不会引发数据库的隐式转换。
更多技术文章，请拜访：https://opensource.actionsky.com/

对于 SQLE

爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，反对多场景审核，反对标准化上线流程，原生反对 MySQL 审核且数据库类型可扩大的 SQL 审核工具。

SQLE 获取