InnoDBで複数のレコードをテーブルに挿入したときに、それらのレコードにAUTO INCREMENTで割り振られたidの一覧を取得する方法を調べてみました。
挿入するレコードが1件の場合
挿入するレコードが1件の場合はLAST_INSERT_ID関数を使って挿入したデータのidを取得できます。
mysql> CREATE TABLE t (
-> id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
-> name VARCHAR(10) NOT NULL
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> INSERT INTO t VALUES (NULL, 'Bob');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
+----+------+
1 row in set (0.01 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
複数件の場合
複数件の場合にもLAST_INSERT_IDを使えば取得できそうですが、全部のidは取得できず、最初の1件分のidだけ取得できます。
mysql> INSERT INTO t VALUES
-> (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
| 2 | Mary |
| 3 | Jane |
| 4 | Lisa |
+----+------+
4 rows in set (0.01 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
1番目に挿入したレコードのidはわかるので、あとは挿入した件数を取得できれば計算で全部のidを出すことができそうです。
ROW_COUNT関数を使うことで挿入した件数を取得できます。
mysql> INSERT INTO t VALUES(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT ROW_COUNT();
+-------------+
| ROW_COUNT() |
+-------------+
| 3 |
+-------------+
1 row in set (0.00 sec)
LAST_INSERT_IDとROW_COUNTを組み合わせることで振られたidを計算で出すことができます。
複数レコード挿入の場合にそれらのidが連続で振られないと上記のロジックで計算できないのですが、MySQLの自動インクリメントロックモードで連続性を保証することができます。
innodb_autoinc_lock_mode が 0 (「従来」) または 1 (「連続」) に設定されている場合、テーブルレベル AUTO-INC ロックがステートメントの終了まで保持され、同時に実行できるステートメントはこのような 1 つのステートメントだけであるため、任意のステートメントによって生成される自動インクリメント値は、ギャップのない連続的なものとなります。
まとめ
前提
- innodb_autoinc_lock_modeが0または1に設定されている
方法
LAST_INSERT_IDとROW_COUNT関数を使う。
(LAST_INSERT_IDの値) 〜 (LAST_INSERT_IDの値 + ROW_COUNTの値 - 1)が目的のidになる。
mysql> INSERT INTO t VALUES (NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT LAST_INSERT_ID() as first, ROW_COUNT() as count;
+-------+-------+
| first | count |
+-------+-------+
| 2 | 3 |
+-------+-------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
| 2 | Mary |
| 3 | Jane |
| 4 | Lisa |
+----+------+
4 rows in set (0.00 sec)
上記の例の場合、first〜first + count - 1で2〜4が今回挿入したレコードのidになります。
※ 上記の前提が守られていない場合には他の方法で頑張るしかなさそうです。
[MySQL] INSERTしたデータのIDを取得したい