SQLiteでのSQL文について質問です。


INTEGER PRIMARY KEYと設定した項目の抜け番を
INSERTで自動的に埋めていくにはどうしたらいいでしょうか?

【例】
id name
------------
1 山田
2 鈴木
4 佐藤

とあるときに、
INSERT INTO hoge VALUES(※ここの記述をどうするか…?)
で、idが3として新しく行が追加されるようにしたいのです。

回答の条件
  • 1人1回まで
  • 登録:2008/05/13 14:15:19
  • 終了:2008/05/15 17:05:33

回答(3件)

id:JULY No.1

JULY回答回数966ベストアンサー獲得回数2472008/05/13 14:55:41

ポイント45pt

INSERT INTO hoge SELECT MIN(id) + 1, 'ジュリアン' FROM hoge WHERE id + 1 NOT IN (SELECT id FROM hoge)

但し、この場合、「1番からの連番で1番が抜けている状態」の場合、id = 1 となるレコードは挿入されません。

id:sutara_lumpur

現在作成中のテーブルでは

id=1のレコードが削除されることはありませんので

これで十分です。

まずはしのぐことができます。


ありがとうございました!

2008/05/13 17:25:31
id:chuken_kenkou No.2

chuken_kenkou回答回数722ベストアンサー獲得回数542008/05/13 15:49:56

ポイント70pt

空いている番号の内、最小の番号を見つけるには、例えば以下のようなSQLを書く必要があります。


insert into hoge
select
  coalesce(min(rownum),(select max(id)+1 from hoge),1) as empty_id,'田中'
 from(
      select
        (select count(*) from hoge where id<=x.id) as rownum,
        id
       from hoge as x
     ) as y
       where rownum<id

</pre>
id:sutara_lumpur

すみませんが、教えていただいたSQL文は

僕には理解不能です。

where id<=x.id

の部分が…。

これは結局、

where id=id

ということになるんじゃないでしょうか?

2008/05/14 11:34:29
id:y-kawaz No.3

y-kawaz回答回数1420ベストアンサー獲得回数2252008/05/13 17:52:29

SQLite では INTERGER PRIMARY KEY のカラムは指定しなければ勝手にユニークIDが付きますよ?

sqlite> create table t (id INTEGER PRIMARY KEY, value TEXT);
sqlite> INSERT INTO t (value) VALUES ('abc');
sqlite> INSERT INTO t (value) VALUES ('123');
sqlite> INSERT INTO t (value) VALUES ('hogefuga');
sqlite> SELECT * FROM t;
1|abc
2|123
3|hogefuga
id:sutara_lumpur

確かにその通りなのですが、

途中で抜け番があった場合、

それを埋めることなく最後の番号から

追加されていってしまいます。


1、2、4とある場合、次のレコードは

3ではなく5になってしまいます。


そこで、入れ替わりの激しいユーザーリストなどで

抜け番を再利用するために、ここで質問したわけです。

2008/05/13 18:12:14
  • id:y-kawaz
    すみません問題の意図を間違えて解凍してしまいました。オープンしなくて結構です。
  • id:chuken_kenkou
    JULYさんのSQLですと、例えば1~3が空となっていた場合、それらは再利用されません。
    私の提示したSQLですと、再利用できます。

    「現状、id=1の行が削除されることはない」とのことですが、より汎用的なSQLを提示したのに、コメントがないことが残念で、念のため書き込みました。
  • id:sutara_lumpur
    >>chuken_kenkouさん
    すみません、返信が遅くなりました。
    上にもあるように、教えていただいたSQLを
    理解しようと頭を抱えていました(w

    たしかに、JULYさんのSQLでは最初から
    抜け番が続いているとそれらは再利用されません。

    chuken_kenkouさんのSQLは、
    『抜け番があればその最小値を、
    抜け番がなければ通常通り番号を追加する』
    という仕組みのようですが…、
    上にも書いているように、
    where id<=x.idの部分が理解できませんでした。
    これはどういう意味なんでしょう?
  • id:chuken_kenkou
    (select count(*) from hoge where id<=x.id)
    の部分は、hoge表を「相関サブクエリ」と呼ばれる方法で、自己結合しています。
    サブクエリ内で、外側のクエリのid以下の件数を求めており、これは結果的に「行に対し、1からの
    通番を付与」していることになります。
    この通番を活かし、空番号の中で最小値を見つけています。
  • id:chuken_kenkou
    追記します。

    行に通番を付けることで、idに欠番があると、「通番<id」の条件が成り立ちます。
    この時、通番の最小値が、求めたい最小の欠番になります。

    id=3~4が欠番になっている場合

    通番 id
    1 1
    2 2
    3 5 →3を得る
    4 6

    id=1~2が欠番になっている場合

    通番 id
    1 3 →1を得る
    2 4
    3 5
    4 6

    もし、欠番がなければ、「通番<id」が成り立たず、min(通番)はnullになります。
    この時、max(通番)+1を採用します。

    もし1件もデータが格納されていなければ、min(通番)、max(通番)+1がnullになるので、この場合は、1を
    採用します。


  • id:sutara_lumpur
    >>chuken_kenkouさん
    丁寧に説明してくださってありがとうございます。
    今、外出先から帰ったところですので、
    これからまた理解できるように挑戦してみます。
  • id:sutara_lumpur
    >>chuken_kenkouさん
    すみません…。
    現在、相関サブクエリを理解しようと
    勉強中です。

    この質問をするときは、
    『型を指定するときに、INTEGER PRIMARY KEYに
    もうひとつ何かを追加すればいいんだろう』
    と軽く考えていました。

    とりあえず、翔泳社の『SQLの絵本』なぞを読んでおりますが、
    このレベルですら、とても一日二日では理解できそうにないので、
    ここで質問を締め切っておきます。

    基礎からじっくり勉強した後で、
    教えていただいたクエリの理解にとりかかります。

    chuken_kenkouさん、JULYさん、ありがとうございました。
  • id:sutara_lumpur
    現在も相関サブクエリが理解できずに悩んでいます…orz

    翔泳社『SQLite入門』に載っていたのですが、
    SQLiteでは、INTEGER PRIMARY KEYの最大値がセットされている
    行が存在する場合、それ以降にnullで追加された行は
    抜け番が割り振られるそうです。

    ※SQLite2の最大値は『2の31乗-1』
    ※SQLite3の最大値は『2の63乗-1』

    ただし、小さい順に抜け番が埋められていくのではなく、
    ランダムで選ばれるのだそうですが…。

    とりあえず
    INSERT INTO hoge(id) VALUES(POW(2,31)-1);
    として、強引に『抜け番再利用』を実現させます。

    でも、相関サブクエリの理解もあきらめません…!
  • id:sutara_lumpur
    ふと思ったのですが…、
    INTEGER PRIMARY KEYの最大値まで到達したら
    その後は抜け番が埋められていくというのなら、
    人間側は抜け番の再利用を意識せずに
    INTEGER PRIMARY KEYのフィールドはnullのまま
    追加し続ければいいということですね。

    抜け番も全て埋まり、これ以上レコードを
    追加できない状態かどうかをチェックすれば
    いいだけで。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません