キリウ君が読まないノート SQLiteのsqlite_sequence (AUTOINCREMENT) の挙動
- テーブルに初めて連番が振られたとき、sqlite_sequence にレコードが作成される。
- sqlite_sequence のレコードは、各テーブルに最後に振られた連番を保持する。
- sqlite_sequence のレコードを消すと、次の連番は現存するデータの最大値 + 1から再開する。
- sqlite_sequence の値が巻き戻っても、次の連番が重複しないように調整してくれる。
SQLiteは、AUTOINCREMENTの情報を sqlite_sequence というテーブルで管理してるみたいです。これの挙動を調べてみました。
基本
まず、PKが連番のテーブルを適当に作ります。
sqlite> CREATE TABLE my_table (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, code TEXT NULL);この時、まだデータベース内に sqlite_sequence テーブルが存在しなければ作成されます。
sqlite_master を覗いて定義を見てみると、sqlite_sequence テーブルは name と seq という名前のカラムを持ってることが分かります。
-- コマンドラインで見やすくなるように
.headers on
.mode column
-- 確認
sqlite> SELECT * FROM sqlite_master;
type name tbl_name rootpage sql
---------- ---------- ---------- ---------- -------------------------------------------------------------------------------------
table my_table my_table 2 CREATE TABLE my_table (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, code TEXT NULL)
table sqlite_seq sqlite_seq 3 CREATE TABLE sqlite_sequence(name,seq)次に、先程作成したテーブル (PKが連番) にレコードを追加します。
-- レコード追加
sqlite> INSERT INTO my_table (code) VALUES ('aaa');
sqlite> SELECT * FROM my_table;
id code
---------- ----------
1 aaa
-- sqlite_sequence を確認
sqlite> SELECT * FROM sqlite_sequence;
name seq
---------- ----------
my_table 1すると sqlite_sequence に、テーブル名と連番を記録するレコードが追加されました。
ここでは my_table に1を振った状態になっています。sqlite_sequence の seq は、最後にレコードを追加した時に振られた連番です。
以後はレコードを追加すると、この数字が増えていきます。
-- レコード追加
sqlite> INSERT INTO my_table (code) VALUES ('bbb');
sqlite> SELECT * FROM my_table;
id code
---------- ----------
1 aaa
2 bbb
-- 2になった
sqlite> SELECT * FROM sqlite_sequence;
name seq
---------- ----------
my_table 2テーブルを DROP すると、sqlite_sequence の当該テーブルのレコードも消えます。これで sqlite_sequence がカラになった場合は、sqlite_sequence ごと消えるみたいです。
sqlite> DROP TABLE my_table;
sqlite> SELECT * FROM sqlite_sequence;
-- ※結果無しsqlite_sequence を消すと
意地悪してみます。sqlite_sequence を勝手に DELETE してしまいました。(DROP はエラーになるのでできません。)
-- テーブル作成してデータ追加
sqlite> CREATE TABLE my_table (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, code TEXT NULL);
sqlite> INSERT INTO my_table (code) VALUES ('aaa');
sqlite> INSERT INTO my_table (code) VALUES ('bbb');
sqlite> SELECT * FROM my_table;
id code
---------- ----------
1 aaa
2 bbb
-- sqlite_sequence の当該レコードを削除
sqlite> DELETE FROM sqlite_sequence WHERE name = 'my_table';
sqlite> SELECT * FROM sqlite_sequence;
-- ※結果無しこの状態でさらに my_table にレコードを追加してみます。
-- レコード追加
sqlite> INSERT INTO my_table (code) VALUES ('ccc');
sqlite> SELECT * FROM my_table;
id code
---------- ----------
1 aaa
2 bbb
3 ccc
-- sqlite_sequence が3で復活
sqlite> SELECT * FROM sqlite_sequence;
name seq
---------- ----------
my_table 3すると、自動的に sqlite_sequence が復活しました。値は3です。
復活するときの値は、レコード追加前に my_table に存在した連番の最大値から決定されるみたいです。ここまでの結果に続けて、以下の操作を行うと確認できます。
-- 連番が4となるレコードを追加し、削除する
sqlite> INSERT INTO my_table (code) VALUES ('ddd');
sqlite> DELETE FROM my_table WHERE id = 4;
-- 連番が2のレコードを削除し、歯抜けにする
sqlite> DELETE FROM my_table WHERE id = 2;
-- 現在の状態を確認
sqlite> SELECT * FROM my_table;
id code
---------- ----------
1 aaa
3 ccc
sqlite> SELECT * FROM sqlite_sequence;
name seq
---------- ----------
my_table 4
-- sqlite_sequence の当該レコードを削除
sqlite> DELETE FROM sqlite_sequence WHERE name = 'my_table';
-- この状態で my_table にレコード追加
sqlite> INSERT INTO my_table (code) VALUES ('eee');
-- 結果を確認
sqlite> SELECT * FROM my_table;
id code
---------- ----------
1 aaa
3 ccc
4 eee
sqlite> SELECT * FROM sqlite_sequence;
name seq
---------- ----------
my_table 4my_table にかつて存在したはずの4番が居ないので、復活時にもう一度4番が回ってきました。
sqlite_sequence を巻き戻すと
もっと意地悪してみます。今度は sqlite_sequence を勝手に UPDATE して、過去の番号に戻してしまいます。
-- ややこしいのでここまでのテーブルは削除して作り直し
sqlite> DROP TABLE my_table;
sqlite> CREATE TABLE my_table (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, code TEXT NULL);
-- 1, 2, 3番目のレコード追加
sqlite> INSERT INTO my_table (code) VALUES ('aaa');
sqlite> INSERT INTO my_table (code) VALUES ('bbb');
sqlite> INSERT INTO my_table (code) VALUES ('ccc');
-- 2番目のレコードを削除して歯抜けにする
sqlite> DELETE FROM my_table WHERE id = 2;
-- sqlite_sequence を 1 でUPDATE
sqlite> UPDATE sqlite_sequence SET seq = 1 WHERE name = 'my_table';
-- 現在の状態を確認
sqlite> SELECT * FROM my_table;
id code
---------- ----------
1 aaa
3 ccc
sqlite> SELECT * FROM sqlite_sequence;
name seq
---------- ----------
my_table 1sqlite_sequence の seq が、実際に存在しているレコードより巻き戻った状態になりました。このまま4番目のレコードを追加してみます。
-- 4番目のレコード追加
sqlite> INSERT INTO my_table (code) VALUES ('ddd');
-- 結果を確認
sqlite> SELECT * FROM my_table;
id code
---------- ----------
1 aaa
3 ccc
4 ddd
sqlite> SELECT * FROM sqlite_sequence;
name seq
---------- ----------
my_table 4追加されたレコードに振られた連番は4でした。ということは、レコード追加時に連番が重複した場合も、テーブルに現存する連番の最大値で自動的に調整されるようです。上述した sqlite_sequence のレコードを消した場合のパターンと同じですね。
sqlite_sequence を進めると
逆のパターンもやっておきます。sqlite_sequence を勝手に UPDATE して、もっと先の番号に進めてしまいます。
-- ややこしいのでここまでのテーブルは削除して作り直し
sqlite> DROP TABLE my_table;
sqlite> CREATE TABLE my_table (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, code TEXT NULL);
-- 1番目のレコード追加
sqlite> INSERT INTO my_table (code) VALUES ('aaa');
-- sqlite_sequence を 9 でUPDATE
sqlite> UPDATE sqlite_sequence SET seq = 9 WHERE name = 'my_table';
-- 現在の状態を確認
sqlite> SELECT * FROM my_table;
id code
---------- ----------
1 aaa
sqlite> SELECT * FROM sqlite_sequence;
name seq
---------- ----------
my_table 1sqlite_sequence の seq が、実際に存在しているレコードより先に進んだ状態になりました。このまま2番目のレコードを追加します。
-- 2番目のレコード追加
sqlite> INSERT INTO my_table (code) VALUES ('bbb');
-- 結果を確認
sqlite> SELECT * FROM my_table;
id code
---------- ----------
1 aaa
10 bbb
sqlite> SELECT * FROM sqlite_sequence;
name seq
---------- ----------
my_table 10追加されたレコードに振られた連番は10でした。先に進める分には、変更した結果がそのまま反映されます。
というか普通に使ってても、物理削除したレコードの連番を勝手に詰められたら困るから、これは当然でしたね。