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
表返回的记录都应该是这样的。
id | a | b |
---|---|---|
1 | 测试a | 测试b |
2 | NULL | 测试 |
我们看下三种数据库中,都执行如下语句,得到的是什么。
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
转成数字,但字符是无法转成数字的,所以提示 "无效数字" 是合情合理的。
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 获取

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
三个经典示例解释什么是「零知识证明」
原文:Zero-knowledge proofs explained in 3 examples 作者:Tian Daphne 翻译整理:开放隐私计算 & PrimiHub 零知识证明(Zero-knowledge proof)是一种在不泄露陈述内容的情况下证明陈述有效性的方法。它是一个证明系统使用户能够公开分享知识或所有权的证明,而不揭示其细节。 在密码学中,零知识证明允许用户在不透露秘密内容的情况下,说服其他用户知道某事或已经做过某事,它是迄今为止最强大的密码学工具之一。 下面,我们将深入探讨了零知识证明的概念,并通过几个通俗易懂的示例帮助读者理解它们的工作原理。 一、零知识证明简史 密码学中的零知识首次出现在 1985 年 The knowledge complexity of interactive proof systems [GMR85] 论文中,由先驱者 Shafi Goldwasser、Silvio Micali 和 Charles Rackoff 提出。他们提供了今天广泛使用的零知识证明定义: “零知识协议是一种方法,通过该方法,一方(证明者)可以向另一方(验...
- 下一篇
凹语言开源1周年,说了这些
8月24日晚,中国开源社区 Landscape 社区畅聊系列直播活动,邀请了凹语言项目联合发起人柴树杉、丁尔男,和临时决策委员会委员扈梦明、赵普明4位嘉宾做客直播间,分享凹语言开源一周年发生的事情。 首先,柴树杉介绍了凹语言项目的起源和成立原因。作为一名从业多年的码农,大学时就对编程语言有所研究,十几年后又对WebAssembly进行了技术实践,他期望设计出一款可生产用的编程语言。在项目初期,对如何进展迷茫和来自各方面的阻力让凹语言项目沉寂了3年,但是国外TinyGo、AssemblyScript和V语言等项目为项目成员提供了思路和参考,并且柴树杉、丁尔男、史斌3位联合发起人深入研究Go语言语法树部分并出版《Go语言定制指南》一书,为凹语言未来发展路线的关键技术提供了理论准备,凹语言项目从开始的想法,到经过3个联合发起人的共同实践,终于启动并在2022年7月完成了凹语言的开源工作。 至于为何取名为“凹语言”,丁尔男介绍到,因为编译目标定为Wasm平台,所以刚开始起的名字是Wa-Lang,也就是WasmLanguage的缩写,后来发现“凹”字的形状跟Wasm的图标挺相似,恰好形状读音双重...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
-
Docker使用Oracle官方镜像安装(12C,18C,19C)
- Springboot2将连接池hikari替换为druid,体验最强大的数据库连接池
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- CentOS8编译安装MySQL8.0.19
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- MySQL8.0.19开启GTID主从同步CentOS8
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- SpringBoot2编写第一个Controller,响应你的http请求并返回结果
推荐阅读
最新文章
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题
- SpringBoot2更换Tomcat为Jetty,小型站点的福音
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题
- CentOS8,CentOS7,CentOS6编译安装Redis5.0.7
- CentOS7,8上快速安装Gitea,搭建Git服务器
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- CentOS8编译安装MySQL8.0.19
- Jdk安装(Linux,MacOS,Windows),包含三大操作系统的最全安装
- MySQL8.0.19开启GTID主从同步CentOS8
- CentOS7安装Docker,走上虚拟化容器引擎之路