orcle not like不建议使用（not like所踩过的坑！）

1.情景展示

　　现在有一张表，需要将表中某字段的值不是以指定字符开头的列进行删除，如何实现？ 

2.问题分析

　　错误方案一：同事想到的是：这种方式

　　咱们来看一下，这个表总共有多少条数据

　　本来表数据总共才4万多条数据，然而使用上面的查询方式，却查出了1多个亿的数据，真不知道这数据库是怎么想的。

2024年3月20日10:01:52

（补充说明：此查询之所以会产生1亿多条数据，是因为产生了笛卡尔积表）

B表有44295条数据，S表有3972条数据，最终产生了：3972   ×   44295 =‬175,939,740‬条数据。

跟上面的总数相差不大。

　　错误方案二：既然不行，我首先想到的是：使用not like实现，实践结果如下：

　　再来看一下，使用like查询有多少条数据。

　　总共的数据为：40059+3972=44031条 数据，比总表数44295要少，也就是说：使用not like查询会遗漏数据，具体什么原因想不通。

2024年3月20日10:01:52

补充说明：使用not like不会将字段值为null的数据统计在内。

3.解决方案

　　正确方案一：not exists()，推荐使用

　　既然not like不行，我就想到用了使用exists()实现。 

　　先来查查符合条件的，使用exists()查询一下与使用like的语句是否一致。

　　结果一致，再看一下，使用not exists()的查询结果

　　算一下，与总数是否一致：40323+3972=44295，与结果一致，大功告成！

--查询表中不是以0.1.2.开头的数据（使用exists实现）
SELECT COUNT (1)
  FROM BASE_ORG_INFO T
 WHERE NOT EXISTS
 (  SELECT 1
          FROM (  SELECT ORGID   FROM BASE_ORG_INFO   WHERE ORGSEQ   LIKE '0.1.2.%' ) S
         WHERE S.ORGID = T.ORGID)

　　如何删除这些数据？

--删除表中某字段不是以0.1.2.开头的数据（使用IN+EXISTS实现）
DELETE FROM BASE_ORG_INFO T2
 WHERE T2.ORGID   IN (  SELECT T.ORGID
                      FROM BASE_ORG_INFO T
                     WHERE NOT EXISTS (  SELECT 1
                              FROM (  SELECT ORGID
                                      FROM BASE_ORG_INFO
                                     WHERE ORGSEQ   LIKE '0.1.2.%' ) S
                             WHERE S.ORGID = T.ORGID))

　　执行结果：

2024年3月20日11:33:01

代码优化：（in+exsits）

查询SQL

SELECT T1.ORGID
  FROM BASE_ORG_INFO T1
 WHERE NOT EXISTS (SELECT 1
          FROM BASE_ORG_INFO T2
         WHERE T2.ORGSEQ LIKE '0.1.2.%'
           AND T2.ORGID = T1.ORGID);

删除SQL

DELETE FROM BASE_ORG_INFO T
 WHERE T.ORGID IN (SELECT T1.ORGID
                     FROM BASE_ORG_INFO T1
                    WHERE NOT EXISTS (SELECT 1
                             FROM BASE_ORG_INFO T2
                            WHERE T2.ORGSEQ LIKE '0.1.2.%'
                              AND T2.ORGID = T1.ORGID))

当然，这需要我们确保ORGID字段的值不存在null值，否则一行也删除不了。

为了避免这种问题，我们可以对删除SQL进一步优化。

代码优化2：（exists+exists）

DELETE FROM BASE_ORG_INFO T
 WHERE EXISTS (SELECT 1
          FROM BASE_ORG_INFO T1
         WHERE NOT EXISTS (SELECT 1
                  FROM BASE_ORG_INFO T2
                 WHERE T2.ORGSEQ LIKE '0.1.2.%'
                   AND T2.ORGID = T1.ORGID)
           AND T1.ORGID = T.ORGID)

　　方案二：not in()

DELETE FROM BASE_ORG_INFO T2
 WHERE T2.ORGID   IN
       (  SELECT T.ORGID
          FROM BASE_ORG_INFO T
         WHERE T.ORGID   NOT IN
               (  SELECT ORGID   FROM BASE_ORG_INFO   WHERE ORGSEQ   LIKE '0.1.2.%' ))

　　执行结果如下：

　　20200617补充说明：

　　如文末评论区二楼所言，此时，SELECT ORGID FROM BASE_ORG_INFO WHERE ORGSEQ LIKE '0.1.2.%')，需要确保ORGID不能为空，否则可能会出现漏删

　　（ORGID为空，ORGSEQ既有以0.1.2开头的，也有不是以它开头的，存在这种可能性）！

　　但反过来说，只要确保ORGID非空，使用NOT IN()就不存在漏洞。

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

 相关推荐：

posted @ 2019-07-19 18:36  Marydon  阅读(6322)  评论(4)  编辑  收藏  举报