[SQLite] insertした直後に登録したデータを取得する方法

最近仕事でSQLiteを使っているので、その時の備忘録です。 SQLでinsertしたデータを取得する時に、該当になるnameやカラムデータを指定して取得してもいいですが、それらがユニークでなければならないので、やはり登録後にid(unique)を取得したいです。そのため、rowidという登録順に採番されるカラムデータとは別のIDを使って、最新データを取得するのが推奨されています。

last_insert_rowid()またはmax()で最新登録レコードのデータ取得ができる

Testテーブル

今回の検証用に新規にテーブルを作成します。

# sqlデータベースにアクセス（ファイルが無くてもアクセスできます）
$ sqlite3 test.sql

# テーブルを作成
sqlite > CREATE TABLE test(id INTEGER PRIMARY KEY, name TEXT);

# 登録されたテーブルの確認
sqlite > SELECT * FROM sqlite_master WHERE type="table";
table|test|test|2|CREATE TABLE test(id INTEGER PRIMARY KEY, name TEXT)

# テストデータの登録
sqlite > INSERT INTO test (name) VALUES ("aaa");
sqlite > INSERT INTO test (name) VALUES ("bbb");
sqlite > INSERT INTO test (name) VALUES ("ccc");

# 登録されたデータ一覧の確認
sqlite > SELECT * FROM test
1|aaa
2|bbb
3|ccc

last_insert_rowid()

sqlite >SELECT * FROM test WHERE rowid=last_insert_rowid();
3|ccc

max()

sqlite >SELECT max(rowid),* FROM test;
3|3|ccc

rowidが取得出来ない確認ポイント

上記のようにコマンドで打ち込んでいると、どれも問題なく結果が出ると思いますが、phpなどでsqliteを使う時に、last_insert_rowid()を使って正常に登録したデータが取得できないケースがありました。 phpでのsqlite登録データの取得方法サンプルを見て、理解してください。

ダメパターン

<?php
add();
$data = inserted_data();
print_r($data);

function add(){
  $sqlite = new \SQLite3('test.sql');
  $res = $sqlite->exec('INSERT INTO test (name) VALUES ("ddd")');

  $sqlite->close();
}

function inserted_data(){
  $datas = [];
  $sqlite = new \SQLite3('test.sql');
  $res = $sqlite->query('SELECT * FROM test WHERE rowid=last_insert_rowid()');
  while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
    array_push($datas , $row);
  }
  $sqlite->close();
  return $datas;
}

データは登録出来ていましたが、結果は取得できていません。

成功パターン

<?php
$data = add();
print_r($data);

function add(){
  $datas = [];
  $sqlite = new \SQLite3('test.sql');
  $sqlite->exec('INSERT INTO test (name) VALUES ("ddd:'.date('YmdHis').'")');
  $res = $sqlite->query('SELECT * FROM test WHERE rowid=last_insert_rowid()');
  while ($row = $res->fetchArray(SQLITE3_ASSOC)) {
    array_push($datas , $row);
  }
  $sqlite->close();
  return $datas;
}

これで正常に登録データを取得することが出来るようになりました。 SQLite3で作成したインスタンスをclose()する前じゃないと、last_insert_rowid()が取得できないようでした。

あとがき

関数を個別に分けてmax()を使ってデータを取得すればいいかと思いがちですが、 insertが頻繁に行われるシステムの場合に、複数人が同時アクセスをして、データを同時にinsertした場合、自分が登録したデータではない、最新データが返ってしまう可能性があります。いわゆる排他処理をするために、last_insert_rowid()を使ったほうが問題が起きにくくなるという事が理解できました。そもそも、sqlのinsertで、rowidを返してくれる仕様にしてくれればいいのに、なんともめんどくさい限りです。