sql?in查詢元素超過1000條的解決方案

更新時間：2023年03月04日 10:59:36 作者：梅西庫里RNG

在oracle數(shù)據(jù)庫中sql使用in時,如果in的能數(shù)超過1000就會出問題,下面這篇文章主要給大家介紹了關于sql?in查詢元素超過1000條的解決方案,文中通過實例代碼介紹的非常詳細,需要的朋友可以參考下

一、場景描述

查詢機構下的人員，要同時查詢出該機構及其所有下級機構的人員；比如查詢北京市，不僅要查詢出歸屬于北京市的人員，還要查詢歸屬于出朝陽區(qū)、西城區(qū)、海淀區(qū)等等的人員。

對于這個需求，有以下幾種解決思路：

1、之前的系統(tǒng)，他們的機構編碼本身有關聯(lián)，比如北京編碼是001的話，昌平區(qū)的編碼就是0012；這樣的話，他們查詢可以用like。這種方法的問題是like查詢太慢，尤其在數(shù)據(jù)量大的時候，即使對編碼加唯一索引，也只有查最根部機構時可以走索引（因為只有此時才符合最左前綴）。

2、我們的系統(tǒng)沒有機構編碼，唯一標識是雪花算法生成的19位id，沒有關聯(lián)；機構關系是通過一張關聯(lián)表組合的起來的（適用于多業(yè)務系統(tǒng)，多機構樹的情況）。這種情況，可以通過條件從關系表中查出所有下級機構id，然后查詢時用in查詢；為了方便查詢，我們還建了一張機構關系冗余表。
但是這種方法也有一個問題，就是Oracle數(shù)據(jù)庫in查詢中元素，必須在1000以內。

二、解決方案

1、方案一：核心思路是，將集合拆分，使用or 連接。

select * from A where id in (1, 2, …, 1000) or id in (1001, …, 1999)

用mybatis的話就是這樣

select * from test_1
<where>
 <if test="list != null and list.size > 0">
    (id IN
    <!-- 處理in的集合超過1000條時Oracle不支持的情況 -->
    <trim suffixOverrides=" OR id IN()">
        <foreach collection="list " item="Id" index="index" open="(" close=")">
            <if test="index != 0">
                <choose>
                    <when test="index % 1000 == 999">) OR id IN (</when>
                    <otherwise>,</otherwise>
                </choose>
            </if>
            #{Id}
        </foreach>
    </trim>
    )
 </if>

但是這種方法不好用，實測中3萬條左右的機構，用這種查詢查了好久都沒出來，感覺數(shù)據(jù)庫都要奔潰了。