做外链的博客网站,网站图片验证码出不来,微信微网站是什么情况,音乐网站界面正是因为 MySQL 对字符串进行隐式转换时会截断再转#xff0c;而不是像 Oracle、SQL Server 这些数据库针对这种问题直接报错#xff0c;所以才出现了这个诡异的问题。 作者#xff1a;刘晨 网名 bisal #xff0c;具有十年以上的应用运维工作经验#xff0c;目前主要从事…正是因为 MySQL 对字符串进行隐式转换时会截断再转而不是像 Oracle、SQL Server 这些数据库针对这种问题直接报错所以才出现了这个诡异的问题。 作者刘晨 网名 bisal 具有十年以上的应用运维工作经验目前主要从事数据库应用研发能力提升和技术管理相关的工作Oracle ACEAlumni腾讯云TVP拥有 Oracle OCM OCP 、EXIN DevOps Master 、SCJP 等国际认证国内首批 Oracle YEP 成员OCMU 成员《DevOps 最佳实践》中文译者之一CSDN ITPub 专家博主公众号”bisal的个人杂货铺”长期坚持分享技术文章多次在线上和线下分享技术主题。 本文来源原创投稿 * 爱可生开源社区出品原创内容未经授权不得随意使用转载请联系小编并注明来源。
背景
同事问了个 MySQL 的问题现象上确实诡异。大致意思是 SELECT 表的数据WHERE 条件是 a0其中 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 表返回的记录都应该是这样的。
idab1测试a测试b2NULL测试
我们看下三种数据库中都执行如下语句得到的是什么。
select * from test where a 0;
MySQL
执行返回如下带字符的记录但实际逻辑上肯定是错的。
idab1测试a测试b
执行时还会抛出一个 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 通过如下判断可以证明。
bisalmysqldb 23:26: [test] select 11测试a;
--------------
| 11测试a |
--------------
| 1 |
--------------
1 row in set, 1 warning (0.00 sec)
上述例子中 测试a 会截成 因此 a0 才会返回字段不为空的。
bisalmysqldb 23:27: [test] select 0测试a;
-------------
| 0测试a |
-------------
| 1 |
-------------
1 row in set, 1 warning (0.00 sec)
通过 0 和 进行比较则可以进一步证明这个问题。
bisalmysqldb 23:29: [test] select 0;
------
| 0 |
------
| 1 |
------
1 row in set (0.00 sec)
因此正是因为 MySQL 对字符串进行隐式转换时会截断再转而不是像 Oracle、SQL Server 这些数据库针对这种问题直接报错所以才出现了这个诡异的问题。
总结
我不知道这种设计是出于什么考虑但这种容错性不可取毕竟返回了错误的结果集。
当然这个问题也和数据类型的使用有关SQL 条件中 a0 实际上是 varcharint。两边类型不一致所以才导致了数据库的隐式转换。
有可能是数据库设计的问题比如字段应该是 INT但是定义成了 VARCHAR还可能使开发人员的问题SQL 条件右值应该用字符类型例如 0但实际上用了 INT 数值类型的 0。
总之按照数据库设计开发规范的要求 号两边的数据类型保持一致这就不会引发数据库的隐式转换。 更多技术文章请访问https://opensource.actionsky.com/
关于 SQLE
爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者支持多场景审核支持标准化上线流程原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。
SQLE 获取
类型地址版本库https://github.com/actiontech/sqle文档https://actiontech.github.io/sqle-docs/发布信息https://github.com/actiontech/sqle/releases数据审核插件开发文档https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse
