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、在过滤的条件的字段上加函数会导致查询不走索引,如果有这样的需求可以在相应的字段加函数索引。

相关文章
|
7天前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
35 10
|
14天前
|
SQL 数据库 开发者
功能发布-自定义SQL查询
本期主要为大家介绍ClkLog九月上线的新功能-自定义SQL查询。
|
26天前
|
SQL 存储 数据库
SQL部分字段编码设置技巧与方法
在SQL数据库管理中,设置字段的编码对于确保数据的正确存储和检索至关重要
|
21天前
|
SQL 移动开发 Oracle
SQL语句实现查询连续六天数据的方法与技巧
在数据库查询中,有时需要筛选出符合特定时间连续性条件的数据记录
|
21天前
|
SQL 存储 Serverless
SQL语句拆分时间字段的技巧与方法
在数据库操作中,经常需要处理时间数据
|
10天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
22 0
|
21天前
|
SQL 数据可视化 BI
SQL语句及查询结果解析:技巧与方法
在数据库管理和数据分析中,SQL语句扮演着至关重要的角色
|
27天前
|
SQL 监控 关系型数据库
使用SQL语句查询操作耗时的技巧与方法
在数据库管理和优化过程中,了解SQL查询操作的耗时是至关重要的
|
存储 关系型数据库 MySQL
面试时被这样一个问:”存储MD5值应该用VARCHAR还是用CHAR?
一个5年工作经验的小伙伴,在面试的时候被这样一个问题。说”存储MD5值应该用VARCHAR还是用CHAR“,他一时间不只如何选择,感觉用VARCHAR也可以,用CHAR也行。希望我来帮忙分析一下。
114 0
|
3月前
|
存储 数据管理 数据库