自転車を発明するか、MySQLテーブルで欠落しているID値を見つける

sqlベースのシステムの開発者と管理者は、特定の場合、タスクに直面しています-テーブルの多数のIDレコードで欠損値（欠損値）を取得します。 たとえば、契約番号、ドキュメントのシリアル番号、電話番号、IPアドレスなど。 MySQLを使用する場合、この些細なタスクは不均衡にリソースを消費します。



たとえば、2001年から2999年までの会社の内部電話番号のプールと、従業員用に発行された番号の表があります。

• t1.phone
• 2001
• 2002
• 2003
• 2004
• 2005年
• 2009
• 2015
• 2016年

次の従業員に次の番号を割り当てるために、最初の無料の値（この場合は2006）を見つける必要があります。 空き値がない場合は、範囲から次を選択する必要があります。 おなじみのタスクですか？ インターネットにあふれているソリューションは、2つの原則に基づいています。



1）ループで列挙を行います。たとえば、SQLで、2001年から2999年までカーソルCUR i + 1を作成し、クエリを作成します。

SELECT t1.phone FROM t1 WHERE phone = i
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

空の値に。 サイクルは、外部ソフトウェアで実行できますが、原則の意味は変わりません。



2）2番目の原則は、テーブルt1でLEFT（OUTER）JOINシーケンス2001 ... 2009を使用することです（もちろん、WHERE t1.phone IS NULL）、またはテーブルt1をそれ自体で1ステップシフトします：

 SELECT MIN(t1.phone)+1 FROM t1 LEFT JOIN t1 AS diff ON (t1.phone = diff.phone+1) WHERE diff.phone IS NULL
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

INを使用する別のオプション

 SELECT ... WHERE phone NOT IN (....)
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

かさばるので、まったく考慮しません。



少量のデータでは、両方のソリューション（およびINを含む）が正常に機能し、大量のレコードがある場合、これらのソリューションはリソースを大量に消費するか、時間を消費します。

サーバーの容量とデータベース設定に依存しますが、いずれにせよ、100万件のレコードを並べ替えたり、強力なサーバーであってもそのようなテーブルに参加すると、実行にかなりの時間がかかります。



サーバーに負担をかけることなく、できれば1回のリクエストで問題を迅速に解決したかったのです。 1つではなく、1つではなく、ここで何が起こったか：

 /*  */ select 2000,2999 into @num,@maxid; select min(f.id) /*     ""  union */ from (select s.num, min(s.num) /*    */ id from ( select /*  ,    .,      ,   */ @num:=@num+1 num, /*    */ r.id from t1 as r order by id ) as s where /*     */ s.id != s.num /*       ,         - min */ /*    ,           null,    */ union select @num+1 num, if(@num+1<@maxid,@num+1,null) id ) as f where /*    union */ f.id is not null limit 1;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結合と比較して、単純な選択は数百倍高速です。



そのような解決策が知られていることは明らかですが、インターネット上では、奇妙なことに、最初からそれを見つけることができなかったので、これらの単純な「自転車」を共有したいと思います。



UPD。

個人的なstepmexパーティーで提案された完璧なソリューション

追加の番号付けとシリーズ比較なしで、（SELECT 1 .....）IS NULLを通じて問題を優雅に解決しました

素晴らしい発見、私は見つけます：

 SELECT (`t1`.`phone`+1) as `empty_phone` FROM `t1` WHERE ( SELECT 1 FROM `t1` as `st` WHERE `st`.`phone` = (`t1`.`phone` + 1) ) IS NULL ORDER BY `t1`.`phone` LIMIT 1