8
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

【SQL】IN句をORで結合して取得できる最大件数を検証

Posted at

はじめに

 SQLを勉強し始めたころからよく見るIN句について
 1つのIN句では最大1000件まで指定できる
 ORで結合を繰り返すことで1000件以上のデータも取得することが可能ですが

 ふとIN句の結合を繰り返すと最大何件まで取得できるのだろうかと気になったので
 以下のSQLのようにIN句を結合して検証してみた。

SELECT
 column_A
FROM
 TABLE_A
WHERE
 column_A IN ('1',・・省略・・,'999','1000')
 OR column_A IN ('1001',・・省略・・,'1999','2000')
 OR column_A IN ('2001',・・省略・・,'2999','3000')
 ・
 ・
 ・
 ORを繰り返していく
 ・
 ・
 ・

環境

 Oracle Database 19c
 MySQL 5.7.12

結論

 ■ORACLE
  IN句で指定できるデータの件数は65,535件

 ■MySQL
  MySQLではIN句の最大数は恐らく存在してない
  ※max_allowed_packetで指定しているデータサイズがSQLの最大サイズになるため
   最大サイズになるまでIN句を結合可能でした。
   max_allowed_packetのサイズを大きくしての検証はしません!許してください!

検証手順

 ■ORACLE
 65,535件まではOK
 65,536件(2の16乗)のときに
「エラー(ORA-00913: 値の個数が多すぎます)」が発生する

EXPLAIN PLAN FOR 
select 
    column_A
from 
    (select 'hoge' as column_A from dual) TABLE_A
where 
    --1~10000
    column_A in ('1',・・省略・・,'999','1000')
    OR column_A in ('1001',・・省略・・,'1999','2000')
    ・
    ・
    ・
        省略 単純にORで繰り返す
    ・
    ・
    ・
    --65001~65500
    OR column_A in ('65001',・・省略・・,'65499','65500')
    --65501~65535
    OR column_A in ('65501',・・省略・・,'65534','65535')
    --以下のコメントを外して実行すると「エラー(ORA-00913: 値の個数が多すぎます)」が発生する
    -- OR column_A in ('65536')
;

 ■MySQL
 最大件数を調べるときにIN句の結合を繰り返すと以下のエラーがでました
「SQL : #08S01Got a packet bigger than 'max_allowed_packet' bytes」
 MySQLだとIN句の最大ではなく「max_allowed_packet」の設定によって
 SQL全体のサイズに影響されるようです。

EXPLAIN 
select 
    TABLE_A.column_A 
from 
    (select 'hoge' as column_A from dual) TABLE_A
where 
    TABLE_A.column_A in ('1',・・省略・・,'999','1000')
    OR TABLE_A.column_A in ('1001',・・省略・・,'1999','2000')
    ・
    ・
    ・
    省略 単純にORで繰り返す
    ・
    ・
    ・
    実行環境のmax_allowed_packetの4MBまで繰り返して以下のエラーがでた
    「SQL   : #08S01Got a packet bigger than 'max_allowed_packet' bytes」
;

おわり

 最後まで読んで頂いてありがとうございます。
 こんなSQLを実装することなんて無いだろうから無駄な知識が1つ増えました

8
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
8
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?