抜けのない連番を振る

SQL

パフォーマンスは無視する方向で。あとSQL Serverでしか試してない。

  • no_misssing_seqに抜けのない連番を格納することにする
  • DBに挿入された相対時間を判定するために、自動採番されるidを使用
  • INSERT時には未確定データと言うことで、-1を入れておく
  • INSERTしたデータが確定したら、no_missing_seqに連番を設定する
  • DELETEは考えない(途中のレコードがDELETEされるとその番号は抜けになる)
UPDATE SomeTable SET
    no_missing_seq =
      (SELECT COALESCE(MAX(no_missing_seq), 0) FROM SomeTable) +
        (SELECT
            COUNT(*)
        FROM
            SomeTable C
        WHERE
            C.id < SomeTable.id AND
              C.no_missing_seq = -1)
WHERE
    no_missing_seq = -1


あまりきれいなやり方じゃないけど、DELETEと同じトランザクションで以下のUPDATE文を実行すればno_missing_seqが振り直される。

UPDATE SomeTable SET
    no_missing_seq =
      (SELECT COUNT(*) FROM SomeTable C WHERE C.id < SomeTable.id)

ただ、no_missing_seqがどこからも参照されていないようにしないと、矛盾が起こるかパフォーマンスがやばいことになるかの二択になる。