MYSQLで歯抜けな連番(シーケンス)を見つける

歯抜けな連番を見つける

pgsqlには連番を生成するgenerate_seriesがあるので生成された連番と歯抜けな表を結合することで容易に歯抜けな連番を探ることができる。
しかしMYSQLには無いのでその代替方をメモ

実装

member

CREATE TABLE IF NOT EXISTS `mem` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `mem` (`id`, `name`) VALUES
    (1, 'taro'),
    (2, 'jiro'),
    (3, 'saburo'),
    (5, 'takuro');

no	name
1	taro
2	jiro
3	saburo
5	takuro

こんな表があるとする

select * from 
(
SELECT 0 sqe FROM DUAL WHERE (@num:=0 )*0
UNION ALL 
SELECT @num:=@num+1 FROM mem a join mem b on a.id <> b.id limit 5
) st left join mem mt
on st.sqe = mt.id

そこにこんなクエリを発行すると

sqe	no	name
1	1	taro
2	2	jiro
3	3	saburo
4	null	null
5	5	takuro

sqeと結合しなかったNullな行が歯抜けとして見て分かる

途中から採番したい

SELECT 0 sqe FROM DUAL WHERE (@num:=0 )*0

上記の
@num:=0で初期値を与えているので
@num:=2や
@num:=10000など
初期値を設定することで途中から採番できる

たくさん採番したいんだが

SELECT @num:=@num+1 FROM mem a join mem b on a.id <> b.id limit 5

上記のlimit 5で指定している数を増やすと良い
ただ生成に使用している"mem"表は4行程度なので(n^2)-nで12までしかシーケンスを生成出来ない。
これは小さい表ほど生成できるシーケンスが少なくなる。

その場合十分に大きな表があるなら"mem"表のかわりに指定すると良い。
大きな表がない場合は

SELECT @num:=@num+1 FROM mem a join mem b on a.id <> b.id join mem c on b.id <> c.id

のように結合する表を増やせばシーケンスも増える
ちなみにここで指定してる"mem"表はシーケンスを発番するためにしか使用していないめ
中身は問わないため何の表でも構わない。

原理

連番を作るDUAL表と歯抜けを見つけたい表とをLeft joinして歯抜けを見つけている

連番を持つDUAL表作る

連番を持つカラムを持つDUAL表を作るクエリになるが
DUAL表は十分に大きな（発行したい連番の最大数より大きな）表と結合する必要がある。

SELECT 0 sqe FROM DUAL WHERE (@num:=0 )*0
UNION ALL 
SELECT @num:=@num+1 FROM mem a join mem b on a.id <> b.id limit 5

上記では探したい表同士をNOTで結合しLimitで最大数を調整している。
表によっては巨大な作業用表が作られメモリを食いつぶしそうだが
実表から実カラムの取得や複雑な演算をしていないので、思いの外リソースは食わない。
（本番環境でやるかどうかは別だが）

歯抜けを見つける

連番を振った表と歯抜けを見つけたい表を外部結合することで求めることができる。
実際は1000万行を超える表などで一気にやると大変なことになるので最初から1万行など指定してやってみると良い。

etc

FROMにサブクエリがあるのでViweを設定出来ないのが残念