各種マスタやトランザクションテーブルの構造として、ユニークな主キーとなる AUTO_INCREMENT カラムを持ち、必要であれば、その他のカラムでインデックスを持ったりします。
AUTO_INCREMENT カラムは INSERT 時に指定しなくても MySQL がユニークな数値を割り振ってくれます。
そのため、データを登録する場合は、主キーの値を考えずにどんどん INSERT ができます。
ただし、紐付けするデータが無い場合は良いのですが、伝票データ等でヘッダデータと明細データのテーブルが在る場合、 最初にヘッダデータを登録し、その後でヘッダデータの主キーに紐づく様に明細データを登録することになります。
ヘッダデータの INSERT の後、主キーの値を取得する必要が出てきますが、 そこで LAST_INSERT_ID() 関数の出番となります。
この LAST_INSERT_ID() 関数ですが AUTO_INCREMENT カラムを持つテーブルに対して INSERT 後に SELECT を行えば、主キーの値が取得できます。
テスト用のテーブル生成SQL
先ずはテスト用のテーブルの生成を行います。
以下の通り主キーとして「id」カラム、さらにデータとしてVARCHAR型の「val」カラムを宣言します。
CREATE TABLE t_auto (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
val VARCHAR(32) NOT NULL
);
それではこのテーブルにデータを INSERT して LAST_INSERT_ID() を取得してみます。
レコードの追加とAUTO_INCREMENT値の取得を行うSQL
INSERT INTO t_auto(val) VALUES('test1');
SELECT LAST_INSERT_ID();
SELECT * FROM t_auto;
実行結果は以下の通りです。
MariaDB [test]> INSERT INTO t_auto(val) VALUES('test1');
Query OK, 1 row affected (0.06 sec)
MariaDB [test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
MariaDB [test]> SELECT * FROM t_auto;
+----+-------+
| id | val |
+----+-------+
| 1 | test1 |
+----+-------+
1 row in set (0.00 sec)
MariaDB [test]>
上記の結果から、自動で主キーが割り振られたことが分かります。
再度、「val」カラムに 'test2' のデータを設定してみますと、以下の様になります。
MariaDB [test]> INSERT INTO t_auto(val) VALUES('test2');
Query OK, 1 row affected (0.05 sec)
MariaDB [test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
MariaDB [test]> SELECT * FROM t_auto;
+----+-------+
| id | val |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)
MariaDB [test]>
確かに、AUTO_INCREMENT のカラムが加算されているのが分かります。
さて、ここでトランザクション処理を INSERT の前後にはさむ形にして、追加処理をロールバックしてみます。
以下の様なSQLを実行します。
トランザクション処理でINSERTをはさむSQL
BEGIN;
INSERT INTO t_auto(val) VALUES('test3');
ROLLBACK;
SELECT LAST_INSERT_ID();
SELECT * FROM t_auto;
実行結果は以下の様になります。
INSERT は確かにロールバックされてテーブルに 'test3' のデータは残っていませんが、 主キーの「id」は1個加算され「3」になりました。
この結果が大事なところで、トランザクション処理では現在の AUTO_INCREMENT のカラムの値が、他の接続先からの INSERT で値が競合しない様になっていますので、今回の様に AUTO_INCREMENT 値は「3」となります。
よって次に INSERT 処理を正常に行えば AUTO_INCREMENT 値は「4」となります。
MariaDB [test]> BEGIN;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> INSERT INTO t_auto(val) VALUES('test3');
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> ROLLBACK;
Query OK, 0 rows affected (0.01 sec)
MariaDB [test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 3 |
+------------------+
1 row in set (0.00 sec)
MariaDB [test]> SELECT * FROM t_auto;
+----+-------+
| id | val |
+----+-------+
| 1 | test1 |
| 2 | test2 |
+----+-------+
2 rows in set (0.00 sec)
MariaDB [test]>
今度はトランザクションをコミットする様にして実行すると、確かに AUTO_INCREMENT 値は「4」となっています。
MariaDB [test]> BEGIN;
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> INSERT INTO t_auto(val) VALUES('test4');
Query OK, 1 row affected (0.01 sec)
MariaDB [test]> COMMIT;
Query OK, 0 rows affected (0.03 sec)
MariaDB [test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 4 |
+------------------+
1 row in set (0.00 sec)
MariaDB [test]> SELECT * FROM t_auto;
+----+-------+
| id | val |
+----+-------+
| 1 | test1 |
| 2 | test2 |
| 4 | test4 |
+----+-------+
3 rows in set (0.00 sec)
MariaDB [test]>
コメント