MySQLでAUTO INCREMENTの値を取得したい。

• facebook

オフィス狛 技術部のJoeです。

INSERT後にAUTO INCREMENTで自動採番された値を使用したいことがあるかと思いますが、
MySQLでは前回INSERTのAUTO INCREMENT値を「LAST_INSERT_ID()」関数で取得できます。

テーブルを作成して、1件INSERTすると「1」が取れました。

mysql> CREATE TABLE test (
    -> id   INT         NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(10) NOT NULL );
mysql> INSERT INTO test ( name ) VALUES ( '111' );
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

AUTO INCREMENTは一意であることを保証してくれますので、各セッション内で最後にINSERTした値が取れました。

【セッションA】INSERT INTO test ( name ) VALUES ( '111' );
【セッションB】INSERT INTO test ( name ) VALUES ( '222' );
【セッションB】SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
【セッションA】SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

と、便利な関数ですが、想定する値が取れないパターンも併せてをご紹介します。

●AUTO INCREMENTのカラムに値を指定してINSERTした場合
値を指定してしまうと取得できません。同一セッションで前回自動採番された値が取れるようです。

mysql> INSERT INTO test ( name ) VALUES ( '111' ); -- 値を指定しない（自動採番）
mysql> INSERT INTO test ( id, name ) VALUES ( 10, '222' ); -- 値を指定する
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

●単一のINSERT文で複数データをINSERTした場合
３件INSERTしたので「3」が欲しいのですが、「1」が取れました。

mysql> INSERT INTO test ( name ) VALUES ( '111' ), ( '222' ), ( '333' );
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+

●別セッションの場合
「当然！」と言われそうですが、別セッションでは取得できません。
下記のようなDB接続から開始するAUTO INCREMENT値の取得処理みたいなものを作ってしまうと、想定した値が取れませんでした。
（MySQLへの接続は「MySql.Data.MySqlClient」を使っています）

[ C#でMySQL接続してAUTO INCREMENT値を取得（ダメな例） ]

string conString= "Database=test;Data Source=localhost;User Id=test;Password=test";
public void Index()
{
    using (MySqlConnection con = new MySqlConnection(conString))
    {
        // DB接続→INSERT
        con.Open();
        MySqlCommand cmd = con.CreateCommand();
        MySqlTransaction tran;
        tran = con.BeginTransaction();
        cmd.Connection = con;
        cmd.Transaction = tran;
        try
        {
            cmd.CommandText = "INSERT INTO test (name) VALUES ('1111')";
            cmd.ExecuteNonQuery();
            tran.Commit();
            ShowAutoIncrementId();  // AutoIncrementの値を表示
        }
        catch (Exception)
        {
            tran.Rollback();
        }
    }
}

public void ShowAutoIncrementId()
{
    using (MySqlConnection con = new MySqlConnection(conString))
    {
        // DB接続→AutoIncrementIdを取得して表示する
        con.Open();
        MySqlCommand cmd = con.CreateCommand();
        cmd.Connection = con;
        cmd.CommandText = "SELECT LAST_INSERT_ID()";
        object id = cmd.ExecuteScalar();
        System.Diagnostics.Debug.WriteLine("***** ID : " + id);
        return;
    }
}

【１回目の実行】
INSERTとSELECTが別セッション（428と429）になってしまったので取得できませんでした。
■結果
***** ID : 0
■MySQLログ
2019-03-06T07:39:31.579862Z 428 Query INSERT INTO test (name) VALUES ('111')
2019-03-06T07:39:31.582475Z 428 Query COMMIT
2019-03-06T07:39:31.660854Z 429 Connect test@localhost on test using SSL/TLS
2019-03-06T07:39:31.668533Z 429 Init DB test
2019-03-06T07:39:31.668751Z 429 Query SELECT LAST_INSERT_ID()

【２回目の実行】
なぜか「1」が取れました！？
コネクションプールで、１回目のINSERTと２回目のSELECTのセッションIDが同じ（428）になると、前回INSERTのIDが取得できてしまうようです。
■結果
***** ID : 1
■MySQLログ
2019-03-06T07:39:40.390591Z 429 Query INSERT INTO test (name) VALUES ('111')
2019-03-06T07:39:40.393070Z 429 Query COMMIT
2019-03-06T07:39:40.469436Z 428 Init DB test
2019-03-06T07:39:40.469689Z 428 Query SELECT LAST_INSERT_ID()


AUTO INCREMENTの値は、主キーや外部キーなどで使用することがあるかと思いますので、
「LAST_INSERT_ID()」関数を使用される際はご注意ください。

MySQL