MariaDB(MySQL)でAUTO_INCREMENTカラムのレコード挿入時の値を取得、次の自動採番値を設定

MariaDB(MySQL)でauto_incrementが設定されているカラムの自動採番した値を取得したり、次の自動採番値を設定・確認する

・テーブルのAUTO_INCREMENT(自動採番)の次の値の取得
・テーブルのAUTO_INCREMENT(自動採番)の次の値の設定
・テーブルにレコードを挿入した時のAUTO_INCREMENT(自動採番)の値を取得する
上記の方法を具体的SQLソースコードとサンプルテーブルで解説します。

最初に

テーブル構造が以下として説明します。
CREATE TABLE t_autoinc( id BIGINT AUTO_INCREMENT, name VARCHAR(10), UNIQUE KEY user_id(id) );

MariaDB [db_test]> desc t_autoinc;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | bigint(20)  | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.013 sec)

レコードが２つ入っているとします。
INSERT INTO t_autoinc(name) values('１レコード目'),('２レコード目') ;

+----+--------------------+
| id | name               |
+----+--------------------+
|  1 | １レコード目       |
|  2 | ２レコード目       |
+----+--------------------+

AUTO_INCREMENT(自動採番)の次の値の取得

AUTO_INCREMENT(自動採番)の次の値の取得は以下のSQLで取得できます。

SELECT AUTO_INCREMENT FROM information_schema.tables
  WHERE TABLE_SCHEMA='データベース名' AND TABLE_NAME='テーブル名';

上記のテーブル「t_autoinc」の場合は以下のSQLで取得できます。

SELECT AUTO_INCREMENT FROM information_schema.tables
  WHERE TABLE_NAME='t_autoinc';

MariaDB [db_test]> SELECT AUTO_INCREMENT FROM information_schema.tables
    ->   WHERE TABLE_NAME='t_autoinc';
+----------------+
| AUTO_INCREMENT |
+----------------+
|              3 |
+----------------+
1 row in set (0.011 sec)

AUTO_INCREMENT(自動採番)の次の値の設定

AUTO_INCREMENT(自動採番)の次の値は以下のSQLで設定できます。

ALTER TABLE テーブル名 AUTO_INCREMENT = 設定したい自動採番の次の値;

上記のテーブル「t_autoinc」の自動採番の次の値を100にする場合は次のSQLで設定できます。

ALTER TABLE t_autoinc AUTO_INCREMENT = 100;

MariaDB [db_test]> ALTER TABLE t_autoinc AUTO_INCREMENT = 100;
Query OK, 0 rows affected (0.012 sec)
Records: 0  Duplicates: 0  Warnings: 0

2レコード追加してからデータを確認してみます。

INSERT INTO t_autoinc(name) values('３レコード目'),('４レコード目') ;
SELECT * FROM t_autoinc;

MariaDB [db_test]> INSERT INTO t_autoinc(name) values('３レコード目'),('４レコード目');
Query OK, 2 rows affected (0.009 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [db_test]> SELECT * FROM t_autoinc;
+-----+--------------------+
| id  | name               |
+-----+--------------------+
|   1 | １レコード目       |
|   2 | ２レコード目       |
| 100 | ３レコード目       |
| 101 | ４レコード目       |
+-----+--------------------+
4 rows in set (0.001 sec)

テーブルにレコードを挿入した時のAUTO_INCREMENT(自動採番)の値を取得する

自動採番カラムのあるテーブルにレコードを挿入したときの自動採番値は「SELECT LAST_INSERT_ID();」で取得できます。

SELECT LAST_INSERT_ID();

上記のテーブル「t_autoinc」にレコードを１つ挿入して自動採番値を取得してみます。

INSERT INTO t_autoinc(name) values('５レコード目');
SELECT LAST_INSERT_ID();

MariaDB [db_test]> INSERT INTO t_autoinc(name) values('５レコード目');
Query OK, 1 row affected (0.008 sec)

MariaDB [db_test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              102 |
+------------------+
1 row in set (0.001 sec)

上記のテーブル「t_autoinc」にレコードを２つ挿入して自動採番値を取得してみます。
複数のレコードを挿入した場合LAST_INSERT_ID()は最初の挿入レコードの採番値を返してきます。

INSERT INTO t_autoinc(name) values('６レコード目'), ('７レコード目');
SELECT LAST_INSERT_ID();

MariaDB [db_test]> INSERT INTO t_autoinc(name) values('６レコード目'), ('７レコード目');
Query OK, 2 rows affected (0.008 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [db_test]> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|              103 |
+------------------+
1 row in set (0.000 sec)

SELECT文で確認してみます。

MariaDB [db_test]> SELECT * FROM t_autoinc;
+-----+--------------------+
| id  | name               |
+-----+--------------------+
|   1 | １レコード目       |
|   2 | ２レコード目       |
| 100 | ３レコード目       |
| 101 | ４レコード目       |
| 102 | ５レコード目       |
| 103 | ６レコード目       |
| 104 | ７レコード目       |
+-----+--------------------+
7 rows in set (0.001 sec)