SQL语句中过滤条件字段使用了to_char函数导致查询结果不准确的问题

简介:

        在SQL查询数据时,对一个日期字段进行过滤,使用to_char函数可能会造成返回结果不准确的问题,下面将针对这个现象做出测试。

一、构建环境

1、连接数据库创建表

sqlplus scott/tiger
create table test_riqi(id number,rq date);

2、插入数据

image

二、测试

1、日期显示格式为DD-MON-RR

①查询日期大于2018.09.04的信息

SCOTT@vbox66in>select * from test_riqi where to_char(rq,'DD-MON-RR') > '04-9月 -18';
    ID RQ
---------- --------------
     1 10-8月 -18
     2 15-8月 -18
     3 18-8月 -18
     4 18-8月 -19
     5 05-8月 -18
     6 10-8月 -18
     7 20-8月 -18
     8 25-8月 -18
     9 25-9月 -18
    11 15-9月 -18

已选择10行。

SCOTT@vbox66in>

SCOTT@vbox66in>select * from test_riqi where rq > '04-9月 -18';

    ID RQ
---------- --------------
     4 18-8月 -19
     9 25-9月 -18
    11 15-9月 -18

SCOTT@vbox66in>

        从执行结果来看,发现比2018.09.04日期小的也显示出来了,正确的结果应该是下面的三条记录,那么造成这个的原因是什么呢?

②可以看到第一个SQL语句中过滤字段加了to_char函数,to_char将RQ字段转换为了字符类型,字符类型比较大小和日期类型是不一样的,看下字符类型是如何比较的:

SCOTT@vbox66in>create table test_zf(id number,val varchar2(30));
表已创建。
SCOTT@vbox66in>insert into test_zf values(1,'1');
已创建 1 行。
SCOTT@vbox66in>insert into test_zf values(1,'2');
已创建 1 行。
SCOTT@vbox66in>insert into test_zf values(1,'123');
已创建 1 行。
SCOTT@vbox66in>insert into test_zf values(1,'6');
已创建 1 行。
SCOTT@vbox66in>insert into test_zf values(1,'ABC');
已创建 1 行。
SCOTT@vbox66in>insert into test_zf values(1,'C');
已创建 1 行。
SCOTT@vbox66in>insert into test_zf values(1,'F');
SCOTT@vbox66in>select * from test_zf;
    ID VAL
---------- ------------------------------
     1 1
     1 2
     1 123
     1 6
     1 ABC
     1 C
     1 F
已选择7行。

SCOTT@vbox66in>select * from test_zf where val < '3';

    ID VAL
---------- ------------------------------
     1 1
     1 2
     1 123

SCOTT@vbox66in>select * from test_zf where val < 'D';

    ID VAL
---------- ------------------------------
     1 1
     1 2
     1 123
     1 6
     1 ABC
     1 C

已选择6行。

SCOTT@vbox66in>
SCOTT@vbox66in>insert into test_zf values(1,'AC');
已创建 1 行。
SCOTT@vbox66in>insert into test_zf values(1,'ACF');
已创建 1 行。
SCOTT@vbox66in>insert into test_zf values(1,'AE');
已创建 1 行。
SCOTT@vbox66in>select * from test_zf where val < 'AD';

    ID VAL
---------- ------------------------------
     1 1
     1 2
     1 123
     1 6
     1 ABC
     1 AC
     1 ACF

已选择7行。

SCOTT@vbox66in>

        从上述测试可以看到,val字段过滤小于‘3’时,结果123也显示了出来,如果是数字来讲这显然是不正常的。字符类型的比较是将字符转换为ASCII码对应的数值之后进行比较,而且是从左往右进行比较,只要有一个字符符合条件之后就不在进行比较。现在就可以解释上述日期类型比较有差错的问题了,当查询日期大于‘04-9月 -18’的日期时,由于to_char将日期转换为字符,在进行比较时只要RQ这个字段值第一个字符大于0就可以显示出来。

2、日期显示格式为YYYY-MM-DD HH24:MI:SS

        日期显示格式为YYYY-MM-DD HH24:MI:SS则不会出现上述的问题,因为是按照进制的形式增大,所以就是用to_char函数将日期转换为字符串也能查询出正常数据

3、在过滤的条件的字段上加函数会导致查询不走索引,如果有这样的需求可以在相应的字段加函数索引。

相关文章
|
1月前
|
SQL 监控 关系型数据库
一键开启百倍加速!RDS DuckDB 黑科技让SQL查询速度最高提升200倍
RDS MySQL DuckDB分析实例结合事务处理与实时分析能力,显著提升SQL查询性能,最高可达200倍,兼容MySQL语法,无需额外学习成本。
|
1月前
|
SQL 存储 关系型数据库
MySQL体系结构详解:一条SQL查询的旅程
本文深入解析MySQL内部架构,从SQL查询的执行流程到性能优化技巧,涵盖连接建立、查询处理、执行阶段及存储引擎工作机制,帮助开发者理解MySQL运行原理并提升数据库性能。
|
1月前
|
SQL 监控 关系型数据库
SQL优化技巧:让MySQL查询快人一步
本文深入解析了MySQL查询优化的核心技巧,涵盖索引设计、查询重写、分页优化、批量操作、数据类型优化及性能监控等方面,帮助开发者显著提升数据库性能,解决慢查询问题,适用于高并发与大数据场景。
|
11天前
|
SQL 关系型数据库 MySQL
(SQL)SQL语言中的查询语句整理
查询语句在sql中占了挺大一部分篇幅,因为在数据库中使用查询语句的次数远多于更新与删除命令。而查询语句比起其他语句要更加的复杂,可因为sql是数据库不可或缺的一部分,所以即使不懂,也必须得弄懂,以上。
93 0
|
2月前
|
SQL XML Java
通过MyBatis的XML配置实现灵活的动态SQL查询
总结而言,通过MyBatis的XML配置实现灵活的动态SQL查询,可以让开发者以声明式的方式构建SQL语句,既保证了SQL操作的灵活性,又简化了代码的复杂度。这种方式可以显著提高数据库操作的效率和代码的可维护性。
205 18
|
2月前
|
SQL 人工智能 数据挖掘
如何在`score`表中正确使用`COUNT`和`AVG`函数?SQL聚合函数COUNT与AVG使用指南
本文三桥君通过score表实例解析SQL聚合函数COUNT和AVG的常见用法。详解COUNT(studentNo)、COUNT(score)、COUNT()的区别,以及AVG函数对数值/字符型字段的不同处理,特别指出AVG()是无效语法。实战部分提供6个典型查询案例及结果,包含创建表、插入数据的完整SQL代码。产品专家三桥君强调正确理解函数特性(如空值处理、字段类型限制)对数据分析的重要性,帮助开发者避免常见误区,提升查询效率。
164 0
|
2月前
|
SQL 人工智能 数据库
【三桥君】如何正确使用SQL查询语句:避免常见错误?
三桥君解析了SQL查询中的常见错误和正确用法。AI产品专家三桥君通过三个典型案例:1)属性重复比较错误,应使用IN而非AND;2)WHERE子句中非法使用聚合函数的错误,应改用HAVING;3)正确的分组查询示例。三桥君还介绍了学生、课程和选课三个关系模式,并分析了SQL查询中的属性比较、聚合函数使用和分组查询等关键概念。最后通过实战练习帮助读者巩固知识,强调掌握这些技巧对提升数据库查询效率的重要性。
106 0
|
3月前
|
SQL
SQL中如何删除指定查询出来的数据
SQL中如何删除指定查询出来的数据
|
8月前
|
SQL
【YashanDB 知识库】通过触发器复制 varchar(4000 char) 列的数据导致乱码
**问题简介:** 客户在使用触发器将表 A 中 varchar(4000 char) 列的数据复制到表 B 时,发现表 B 中的数据出现乱码且与表 A 不一致。此问题影响所有 YashanDB 版本,原因是 YashanDB 在处理触发器场景下的大长度 varchar 数据复制时存在机制错误。为避免该问题,建议将列长度修改为 varchar(2000 char) 或更小。数据正确性无法保证,需谨慎处理。 **验证方法:** 可通过创建表 A 和 B 及相应触发器进行测试。
【YashanDB 知识库】通过触发器复制 varchar(4000 char) 列的数据导致乱码
|
7月前
|
SQL
【YashanDB知识库】通过触发器复制varchar(4000 char)列的数据导致乱码
【YashanDB知识库】通过触发器复制varchar(4000 char)列的数据导致乱码

热门文章

最新文章