类型隐式转换导致的?No,并不是
疑似类型隐式转换一例
有群友提了下面这样的问题
请教个隐式转换的问题:
SELECT count(*) FROM test WHERE time >= 2019-05-17;
time列是datetime类型,
这条SQL的执行结果是相当于 where 1, 这个是什么原因呢?
SQL执行有个warnings:
Warning | 1292 | Incorrect datetime value: '1997' for column 'time' at row
从告警信息来看,是把 2019-05-17 做了数学减法运算,得到常量 1997,
再把常量1997转换为 datetime 类型,再跟time字段做比较。
但用函数 cast(1997 as datetime),也是同样的告警信息,但结果是 NULL
那么该SQL是否可以等价为:
SELECT count(*) FROM test WHERE time >= NULL
这个SQL结果会是0,因为跟NULL值比较的结果是NULL。
虽然WHERE条件错写成一个算式,但执行时没有报错,只有一个告警信息,
感觉还是因为发生了类型隐式转换,用不到索引,否则不会是全表扫描。
这是我的第一次回复内容
事实上,条件 WHERE time >= 2019-05-17,
的意思是:time >= 1997,这是表达式 2019-05-17 的结算结果。
这个不是类型隐式转换,是你SQL没写对。
我们看到SQL的执行计划是这样的
对于第二个疑问:为什么会走全表扫描计划呢?
我的看法是这样的:首先,上面的SQL条件相当于 WHERE time >= 1997。其次,MySQL认为"1997"不是合法的日期时间类型数据,看到执行计划中有告警
Incorrect datetime value: '1997' for column
因此,time >= 1997 这个条件,就会被当做一个逻辑表达式,因为不是 0(FALSE),也不是 NULL,所以就会被认为是永远为真(TRUE)。也就是说,time列中所有不是FALSE或NULL的值都符合条件。
我们可以测试确认这个说法:
# 表中dt列是datetime类型,但允许为NULL
[root@yejr.me]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int NOT NULL,
...
`dt` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`c1`),
KEY `k2` (`dt`)
) ENGINE=InnoDB;
# 查看所有数据
[root@yejr.me]> select * from t1;
+-----+-----+---------------------+
| c1 | ... | dt |
+-----+-----+---------------------+
| 2 | ... | NULL |
| 3 | ... | 2017-11-01 15:44:27 |
| 5 | ... | 2020-02-13 16:02:55 |
+-----+-----+---------------------+
3 rows in set (0.00 sec)
# c1=2的记录中,dt列值为NULL,不符合条件
[root@yejr.me]> select * from t1 where dt >= 1997;
+-----+-----+---------------------+
| c1 | ... | dt |
+-----+-----+---------------------+
| 3 | ... | 2017-11-01 15:44:27 |
| 5 | ... | 2020-02-13 16:02:55 |
+-----+-----+---------------------+
2 rows in set (0.00 sec)
很明显,只要表中dt列值不为NULL、不为0(符合日期时间格式的数据也肯定不会是0)的数据都会被读取到。
这种情况下,即便dt列有索引,也会因为需要扫描的数据太多,从而优化器认为直接走全表扫描的效率要更好,所以也无法使用索引。
还有个疑问,WHERE条件写成 time >= cast(1997 as datetime) 时会怎样?这种情况下,因为 cast(1997 as datetime) 的结果是 NULL, 所以WHERE条件等同于 time>= NULL,对NULL的运算是不能这么写的, 而应该写成 dt IS NULL 或 t IS NOT NULL才对。所以,这么写的话,这个查询是不会有任何结果的,包括列值为NULL的数据。
最后的小结:
-
写SQL时,WHERE条件值记得总是带上引号,避免发生意想不到的情况。
-
对NULL值的判断,必须是用 IS NULL 或 IS NOT NULL,不能是大小值的判断。另外,WHERE条件中的NULL其实是可以用到索引的,例如:
[root@yejr.me]> desc select * from t1 where dt is NULL\G
************************ 1. row ************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: k2
key: k2
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: Using where
-
除了防范类型隐式转换,还要注意防范字符集隐式转换,具体参考MySQL手册12.2 Type Conversion in Expression Evaluation。
全文完
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
几点PostgreSQL读书笔记
前言 几条PG读书笔记,并谈谈个人浅见,欢迎讨论。 我去年出差略多,于是在路上把目前主要的两本PostgreSQL书大概翻了翻,做了些笔记,谈点个人看法。 以下简称PG,反正都懂。文内对PG有误解或说错的地方还请批评指正。 第一部分笔记,基于《PostgreSQL修炼之道》一书为主。该书唐成著,2015年出版。此时PG的最新版本应该是9.4。本文亦有基于其他资料。 注:以下“唐老师”指唐成老师,“张老师”指张文升老师。 1. AUTOCOMMIT关键字 事务自动提交模式关键字AUTOCOMMIT,只能大写,小写不行,大小写混合也不行。 唐老师: AUTOCOMMIT,是指psql默认autocommit是on的,我见过的多数人喜欢自动提交。如果觉得这样不安全,可以在.psqlrc中一次性配置好,就不同改了。 点评:这个是小事,但用起来稍微有点不太方便。 2. 什么时候开始表分区 建议当表大小超过PG可用的物理内存时,就开始做表分区。不太了解这个建议是怎么得来的... 唐老师:这个表具体多大该建分区,不同人有不同的认识。通常如果超过内存大小,cache的作用就很弱了。所以超过了物理内存...
- 下一篇
iBoxDB 2.23/2.17 发布,环保型嵌入式数据库
iBoxDB是一个高能效的 Java .NET 嵌入式 NoSQL 数据库,与应用程序形成现代产品流行的一体化设计。 安全稳健,从语法解析上去掉了SQL注入的可能。 使用编程语言的逻辑操作数据,并且每段代码有独立的数据空间,无需担心外部注入数据破坏一致性。 安装简单,维护容易。 支持平台: Java .NET Xamarin Mono Unity3D Android Windows Linux Blazor WASM 更新内容: 支持使用弱类型查询 使用内置类型查询 box.select(Member.class,"from Member where Type > ?", 1L); 使用弱类型查询,通过Variant自动转换对应类型 box.select(Member.class,"from Member where Type > ?", new Variant("1")); 更多更新说明,示例,下载。 能效对比 执行相同量的数据处理,显示 iBoxDB 有更高的能效比,绿色环保。 与 MySQL8 性能对比 Java11 与 SQLite 性能对比 C#8 连续...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- Docker快速安装Oracle11G,搭建oracle11g学习环境
- Linux系统CentOS6、CentOS7手动修改IP地址
- CentOS8,CentOS7,CentOS6编译安装Redis5.0.7
- SpringBoot2整合Thymeleaf,官方推荐html解决方案
- CentOS关闭SELinux安全模块
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长
- SpringBoot2整合Redis,开启缓存,提高访问速度
- CentOS8安装MyCat,轻松搞定数据库的读写分离、垂直分库、水平分库
- Hadoop3单机部署,实现最简伪集群