こんにちは。カミナシで「カミナシレポート」の開発を担当しているソフトウェアエンジニアの佐藤です。
カミナシレポートのバックエンドは Go で開発しており、データベースには Amazon Aurora MySQL を使用しています。また、データベースアクセスには ORM ライブラリの GORM を採用しています。
ほとんどのテーブルでは、プライマリキー（ID列）に AUTO INCREMENT を利用しています。これらのテーブルに GORM の Create メソッドなどを使って新しいレコードを挿入すると、AUTO INCREMENT で採番された値が自動的に対応する Struct のフィールドに反映されます。
AUTO INCREMENT による値の採番は MySQL 側で実行されているため、Go 側の Struct のフィールドに反映させるためには、Go アプリケーション側が何らかの方法で、この値を取得してくる必要があります。
この仕組みがどのように実現されているかに興味を持ち、調査を行いました。本記事では、その結果を共有します。
（💡注意）
- MySQL のストレージエンジンは InnoDB を前提としています。
- MySQL のパケット（主に OK_Packet） についてのドキュメントへのリンクは、最新バージョン(latest)へのリンクとしています。本来、検証バージョン(8.x) へのリンクを貼りたかったのですが、latest 以外のリンクだと 404 になる事態が観測されたため、やむなくこのようにしました。
TL;DR
調査結果を以下にまとめます。
💡「LastInsertId」「OK_Packet」「auto_increment_increment」「Mixed-mode inserts」など馴染みのない言葉が並んでいますが、それぞれ本文で解説していますのでご安心ください。
sql.Resultの LastInsertIdメソッド を使用して、挿入されたレコードの AUTO INCREMENT で採番された値を取得。- MySQL では、INSERT 後に返される OK_Packet から
LastInsertIdの値を取得。 - 複数行を1つの INSERT ステートメントで挿入する場合（以下「複数行 INSERT 」）、GORM や Bun は
LastInsertIdを基に、Go 側で Struct の該当フィールドにセットする値を計算。 - 複数行 INSERT において、 auto_increment_increment がデフォルト値でない場合に対処可能かは、ORM ライブラリ次第。
- ORM ライブラリで複数行 INSERT を行う際は、"Mixed-mode inserts" の使用は避けるのが無難。Go 側で算出した IDs と MySQL 側で実際に採番された IDs の整合性が取れなくなる可能性が高いため。
ORM ライブラリにおける AUTO INCREMENT
以降、本記事では、Go の ORM ライブラリである GORM と Bun を対象に、AUTO INCREMENT の挙動を調査していきます。
GORM や Bun を使用して INSERT 操作を行うと Struct の AUTO INCREMENT 列に対応するフィールドに値が自動的にセットされます。
以下の例のように、INSERT された行に対応する Struct の ID フィールドに AUTO INCREMENT で採番された値がセットされます。
（テーブル定義）
CREATE TABLE organizations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL );
（GORM の例）
type Organization struct { ID int64 `gorm:"primaryKey"` Name string } ..... org := Organization{Name: "Org 1"} // INSERT INTO `organizations` (`name`) VALUES ('Org 1') err := db.Create(&org).Error ..... print(org)
{ ID: 1, Name: "Org 1" } # MySQL 側で採番された値が自動でセットされている
（Bun の例)
type Organization struct { ID int64 `bun:",pk,autoincrement"` Name string } ..... org := Organization{Name: "Org 1"} // INSERT INTO `organizations` (`id`, `name`) VALUES (DEFAULT, 'Org 1') _, err := db.NewInsert().Model(&org).Exec(ctx) ..... print(org)
{ ID: 1, Name: "Org 1" } # MySQL 側で採番された値が自動でセットされている
AUTO INCREMENT 列の値は MySQL 側で採番されるため、この振る舞いを実現するには、MySQL 側で採番された値を、Go アプリケーションが何らかの方法で取得する必要があります。
では、ORM ライブラリは、どのようにしてこの値を取得し、Struct に反映しているのでしょうか？
次のセクションで詳しく見ていきます。
OK_Packet から LastInsertId を取得している
調査の結果、GORM と Bun の両方で、sql.Result の LastInsertId メソッド が呼び出されており、LastInsertId メソッドは、MySQL の OK_Packet の「last_insert_id」から値を取得していることがわかりました。(MySQL の OK_Packet は馴染みない方も多いかもしれませんが、後ほど説明するので、しばしお付き合いください）
まずは、sql.Result の LastInsertId メソッドから見ていきます。
insertID, err := result.LastInsertId()
id, err := res.LastInsertId()
LastInsertId メソッドのドキュメントによると、このメソッドは新しい行を挿入した際の AUTO INCREMENT 列の値を返します。ただし、値の取得方法はデータベースによって異なるとされています。
LastInsertId returns the integer generated by the database in response to a command. Typically this will be from an "auto increment" column when inserting a new row. Not all databases support this feature, and the syntax of such statements varies.
（機械翻訳）
LastInsertIdは、コマンドに応じてデータベースが生成した整数を返します。通常、これは新しい行を挿入する際の「自動インクリメント」列から取得されます。ただし、すべてのデータベースがこの機能をサポートしているわけではなく、そのようなステートメントの構文はデータベースによって異なります。
では、MySQLではどのようにして LastInsertId の値を取得しているのでしょうか？
さらに調査を進めたところ、LastInsertId の値は go-sql-driver/mysql の以下の箇所で取得されていることがわかりました。この処理では、MySQL が送信する OK_Packet からLastInsertId を抽出しています。
go-sql-driver/mysql の packets.go
insertId, _, m = readLengthEncodedInteger(data[1+n:])
OK_Packet は、INSERT などのコマンドが完了した後に MySQL から送信されるパケットで、以下のような構造を持っています。
（引用元: https://dev.mysql.com/doc/dev/mysql-server/latest/page_protocol_basic_ok_packet.html ）
実際にパケットキャプチャをしてみると以下のような TCP パケットが得られました。
Transmission Control Protocol, Src Port: 43306, Dst Port: 54806, Seq: 256, Ack: 375, Len: 50
Source Port: 43306
Destination Port: 54806
（中略）
0000 2e 00 00 01 00 05 07 02 00 00 00 26 52 65 63 6f ...........&Reco
0010 72 64 73 3a 20 35 20 20 44 75 70 6c 69 63 61 74 rds: 5 Duplicat
0020 65 73 3a 20 30 20 20 57 61 72 6e 69 6e 67 73 3a es: 0 Warnings:
0030 20 30 0
2e 00 00 01 以降の 00 05 07 02 00..... が OK_Packet のペイロード部分です。
この例では、ペイロード内の「07」が 「last_insert_id 」の値です。
（補足）
2e 00 00 01 の部分は、MySQL のパケット において、「payload_length」や 「sequence_id」 を表している部分です
ORM で複数行を一括 INSERT する場合の AUTO INCREMENT 注意点
以下のように VALUES にカンマ区切りで複数の行を指定することで、1つのステートメントで複数行を INSERT することが可能です。
INSERT INTO `organizations` (`name`) VALUES ('Org 1'),('Org 2')
GORM・Bun 両方ともこれらの形式をサポートしており、さらに、挿入された各行に対応する Struct の AUTO INCREMENT 列に該当するフィールドに値がセットされます。
これらの処理を実現する上でも OK_Packet の last_insert_id が利用されていますが、複数行の AUTO INCREMENT 値が正しく得られることを保証するためにはいくつか注意点があります。
まずは、GORM・Bun で、１ステートメントで複数行を INSERT する例を見ていきます。
以下の例のように、AUTO INCREMENT で採番された値が、各行に対応する Struct の ID フィールドにセットされます。
（GORM の例）
orgs := []Organization{
{Name: "Org 1"},
{Name: "Org 2"},
{Name: "Org 3"},
}
// INSERT INTO `organizations` (`name`) VALUES ('Org 1'),('Org 2'),('Org 3')
err := db.CreateInBatches(&orgs, 100).Error
.....
for _, org := range orgs {
print(org)
}
# MySQL 側で採番された値が自動でセットされている
{ ID: 1, Name: "Org 1" }
{ ID: 2, Name: "Org 2" }
{ ID: 3, Name: "Org 3" }
（Bun の例）
orgs := []Organization{
{Name: "Org 1"},
{Name: "Org 2"},
{Name: "Org 3"},
}
// INSERT INTO `organizations` (`id`, `name`) VALUES (DEFAULT, 'Org 1'), (DEFAULT, 'Org 2'), (DEFAULT, 'Org 3')
_, err := db.NewInsert().Model(&orgs).Exec(ctx)
.....
for _, org := range orgs {
print(org)
}
# MySQL 側で採番された値が自動でセットされている
{ ID: 1, Name: "Org 1" }
{ ID: 2, Name: "Org 2" }
{ ID: 3, Name: "Org 3" }
この処理において、GORM・Bun どちらも、MySQL 側で採番されたすべての ID の値を直接取得しているわけではありません。
代わりに、last_insert_id の値を基に、Go 側で次のようなロジックで ID を生成しています。
for i := 0; i < db.Statement.ReflectValue.Len(); i++ { rv := db.Statement.ReflectValue.Index(i) if reflect.Indirect(rv).Kind() != reflect.Struct { break } if _, isZero := pkField.ValueOf(db.Statement.Context, rv); isZero { db.AddError(pkField.Set(db.Statement.Context, rv, insertID)) insertID += pkField.AutoIncrementIncrement } }
for i := 0; i < sliceLen; i++ { strct := indirect(model.slice.Index(i)) if err := pk.ScanValue(strct, id); err != nil { return err } id++ }
Go 側の処理で ID が計算されていることが見て取れると思います。
このような処理であるがゆえに、いくつか留意点があるので以降で見ていきます。
（💡注意）
複数行 INSERT した際の last_insert_id の値は、INSERT ステートメントに対して最初に採番された ID となります。
例えば、以下の INSERT 文によって、記載のような 2レコードが生成された場合、last_insert_id は「7」となります。（そのため、上記 GORM・Bun のコードではデクリメントではなくインクリメントされています）
INSERT INTO `organizations` (`name`) VALUES ('Org 1'),('Org 2')
id name 7 Org 1 8 Org 2
📝 auto_increment_increment
MySQL には、auto_increment_increment というシステム変数があります。この変数は「AUTO INCREMENT の増加幅」を制御するもので、デフォルト値は「1」です。
たとえば、auto_increment_increment = 5と設定した場合、ID は以下のように「5」飛ばしで採番されます。
INSERT INTO `organizations` (`name`) VALUES ('Org 1'),('Org 2'),('Org 3')
id name 4 Org 1 9 Org 2 14 Org 3
この設定に関する GORM・Bun の挙動を以下にまとめます。
- GORM： Struct の該当フィールドのタグに
AUTOINCREMENTINCREMENTを正しく指定する必要がある。指定がない場合、デフォルト値の「1」とみなして計算が行われ、（auto_increment_incrementがデフォルト値でない場合）誤った値が生成される。 - Bun： デフォルト値の「1」の場合のみ対応。
GORM では以下のように AUTOINCREMENTINCREMENT を指定することで MySQL 側と整合性をとることができました。
type Organization struct { ID int64 `gorm:"primaryKey;AUTOINCREMENTINCREMENT:5"` Name string ...... }
一方、Bun は、「1」ずつインクリメントする実装となっており、デフォルト値「1」以外、対応していないようでした。
📝 Mixed-mode inserts
Mixed-mode inserts とは、一部の行に対して AUTO INCREMENT 列の値を指定し、一部は指定しない形式の INSERT を指します。
以下は Mixed-mode inserts の例です：
INSERT INTO `organizations` (`id`,`name`) VALUES (DEFAULT,'Org 1'),(100,'Org 2'),(DEFAULT,'Org 3')
id name 1 Org 1 100 Org 2 101 Org 3
GORM・Bun いずれも Mixed-mode inserts では正しい計算が行われませんでした。
（GORM の例）
orgs := []Organization{
{Name: "Org 1"},
{ID: 100, Name: "Org 2"},
{Name: "Org 3"},
}
// INSERT INTO `organizations` (`name`,`id`) VALUES ('Org 1',DEFAULT),('Org 2',100),('Org 3',DEFAULT)
err := db.CreateInBatches(&orgs, 100).Error
.....
for _, org := range orgs {
print(org)
}
{ ID: 1, Name: "Org 1" }
{ ID: 100, Name: "Org 2" }
{ ID: 2, Name: "Org 3" } -> !! 実際に挿入されるレコードの ID は「101」
（Bun の例）
orgs := []Organization{
{Name: "Org 1"},
{ID: 100, Name: "Org 2"},
{Name: "Org 3"},
}
// INSERT INTO `organizations` (`id`, `name`) VALUES (DEFAULT, 'Org 1'), (100, 'Org 2'), (DEFAULT, 'Org 3')
_, err := db.NewInsert().Model(&orgs).Exec(ctx)
.....
for _, org := range orgs {
print(org)
}
{ ID: 1, Name: "Org 1" }
{ ID: 2, Name: "Org 2" } -> !! 実際に挿入されるレコードの ID は「100」
{ ID: 3, Name: "Org 3" } -> !! 実際に挿入されるレコードの ID は「101」
MySQL 側で実際に生成された ID と、Struct の ID フィールドの値に相違が出てしまっています。
さらに、これに加え innodb_autoinc_lock_mode の設定にも注意しなければなりません。
innodb_autoinc_lock_mode が「2」（MySQL 8 以降のデフォルト値）の場合、Mixed-mode inserts において AUTO INCREMENT で採番される値は、同時に実行されるステートメント次第で変わる可能性があります。
（innodb_autoinc_lock_modeについては次のセクションで補足します）
以上の通り、Mixed-mode inserts には、
- ORM ライブラリが、AUTO INCREMENT 列の値を正しく採番できない可能性がある。
innodb_autoinc_lock_modeが「2」の場合には、last_insert_id などの値を元に、アプリケーション側で正しい値を導き出すことは、そもそも困難である。
といった問題があると言えます。
これらの理由から、ORM ライブラリの仕組みを用いた Mixed-mode inserts は避けるのが無難と思います。
📝 補足 - innodb_autoinc_lock_mode について
innodb_autoinc_lock_mode とは AUTO-INC Lock に使用されるアルゴリズムを制御するためのシステム変数です。
AUTO-INC Lock とは、AUTO INCREMENT 列が定義されたテーブルに対して INSERT 操作を行う際に使用される特別なテーブルレベルのロックです。
このロックは、複数のトランザクションが同じテーブルに挿入を試みる場合でも、生成される値の順序に一定の予測可能性を確保するための仕組みです
（AUTO-INC Locks の部分を意訳）。
今回取り上げた複数行の INSERT は、 “Simple inserts” に該当します。
Simple inserts においては、Mixed-mode inserts でない場合に限り、innodb_autoinc_lock_mode が 0,1,2 のどれであっても、AUTO INCREMENT 列の値は連続した値になることが保証されるようです。
以下、MySQL ドキュメントの該当箇所を引用します。（日本語部分は、機械翻訳版から引用しています）
（ innodb_autoinc_lock_mode = 0 ）
In this lock mode, all “INSERT-like” statements obtain a special table-level
AUTO-INClock for inserts into tables withAUTO_INCREMENTcolumns. This lock is normally held to the end of the statement (not to the end of the transaction) to ensure that auto-increment values are assigned in a predictable and repeatable order for a given sequence ofINSERTstatements, and to ensure that auto-increment values assigned by any given statement are consecutive.このロックモードでは、すべての 「INSERT-like」 ステートメントは、
AUTO_INCREMENTカラムを含むテーブルに挿入するための特別なテーブルレベルのAUTO-INCロックを取得します。 通常、このロックは (トランザクションの最後ではなく) ステートメントの最後に保持され、特定の一連のINSERTステートメントに対して予測可能で繰返し可能な順序で自動増分値が割り当てられ、特定のステートメントによって割り当てられた自動増分値が連続していることを確認します。
（ innodb_autoinc_lock_mode = 1 ）
This lock mode ensures that, in the presence of
INSERTstatements where the number of rows is not known in advance (and where auto-increment numbers are assigned as the statement progresses), all auto-increment values assigned by any “INSERT-like” statement are consecutive, and operations are safe for statement-based replication.このロックモードでは、行数が事前にわからない (したがってステートメントの処理中に自動インクリメント番号が割り当てられる)
INSERTステートメントが存在する場合には、任意の 「INSERTのような」 ステートメントによって割り当てられたすべての自動インクリメント値が必ず連続した値になるため、その処理は、ステートメントベースのレプリケーションで使用しても安全です。
（ innodb_autoinc_lock_mode = 2 ）
If the only statements executing are “simple inserts” where the number of rows to be inserted is known ahead of time, there are no gaps in the numbers generated for a single statement, except for “mixed-mode inserts”.
実行中のステートメントが「「単純な挿入」」のみで、挿入される行数が事前にわかっている場合、「「混合モードの挿入」」を除き、単一のステートメントに対して生成される番号にギャップはありません。 ただし、「一括挿入」が実行されると、特定のステートメントで割り当てられた自動インクリメント値にギャップが発生する可能性があります。
おわりに
MySQL で AUTO INCREMENT で採番された値を Go（の ORM ライブラリ）がどのように取得しているかをみてきました。よしなに Struct のフィールドの値がセットされる点は便利な反面、特に、複数行を１ステートメントで INSERT する場合には注意が必要そうです。
最後に宣伝です📣
カミナシでは絶賛採用中です！一緒に最高のサービスを作っていく仲間を募集しています！
Appendix
本記事で使用したサンプルコードの全体を示します。
（GORM）
package main import ( "fmt" "log" "os" "gorm.io/driver/mysql" "gorm.io/gorm" ) type Organization struct { ID int64 `gorm:"primaryKey"` // ID int64 `gorm:"primaryKey;AUTOINCREMENTINCREMENT:5"` Name string } func (o Organization) String() string { return fmt.Sprintf("{ ID: %d, Name: \"%s\" }", o.ID, o.Name) } func main() { dsn := "testuser:testpass@tcp(127.0.0.1:43306)/ormblogdb" db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{}) if err != nil { log.Fatalf("Failed to connect to database: %v", err) } db = db.Debug() if os.Getenv("MULTIPLE_ROWS") == "true" { insertMultipleRows(db) } else { insertSingleRow(db) } } func insertSingleRow(db *gorm.DB) { org := Organization{Name: "Org 1"} err := db.Create(&org).Error if err != nil { log.Fatalf("Failed to insert organization: %v", err) } print(org) } func insertMultipleRows(db *gorm.DB) { orgs := []Organization{ {Name: "Org 1"}, {Name: "Org 2"}, {Name: "Org 3"}, } err := db.CreateInBatches(&orgs, 100).Error if err != nil { log.Fatalf("Failed to insert organizations: %v", err) } for _, org := range orgs { print(org) } } func print(org Organization) { fmt.Println(org) }
（Bun）
package main import ( "context" "database/sql" "fmt" "log" "os" _ "github.com/go-sql-driver/mysql" "github.com/uptrace/bun" "github.com/uptrace/bun/dialect/mysqldialect" "github.com/uptrace/bun/extra/bundebug" ) type Organization struct { ID int64 `bun:",pk,autoincrement"` Name string } func (o Organization) String() string { return fmt.Sprintf("{ ID: %d, Name: \"%s\" }", o.ID, o.Name) } func main() { dsn := "testuser:testpass@tcp(127.0.0.1:43306)/ormblogdb" sqlDB, err := sql.Open("mysql", dsn) if err != nil { log.Fatalf("Failed to connect to MySQL: %v", err) } defer sqlDB.Close() db := bun.NewDB(sqlDB, mysqldialect.New()) db.AddQueryHook(bundebug.NewQueryHook( bundebug.WithVerbose(true), )) defer db.Close() if os.Getenv("MULTIPLE_ROWS") == "true" { insertMultipleRows(db) } else { insertSingleRow(db) } } func insertSingleRow(db *bun.DB) { ctx := context.Background() org := Organization{Name: "Org 1"} _, err := db.NewInsert().Model(&org).Exec(ctx) if err != nil { log.Fatalf("Failed to insert organization: %v", err) } print(org) } func insertMultipleRows(db *bun.DB) { ctx := context.Background() orgs := []Organization{ {Name: "Org 1"}, {Name: "Org 2"}, {Name: "Org 3"}, } _, err := db.NewInsert().Model(&orgs).Exec(ctx) if err != nil { log.Fatalf("Failed to insert organizations: %v", err) } for _, org := range orgs { print(org) } } func print(org Organization) { fmt.Println(org) }
(DB関係）
docker-compose.yml
services: db: image: mysql:8.0 container_name: mysql-orm-blog ports: - "43306:3306" environment: MYSQL_ROOT_PASSWORD: example MYSQL_DATABASE: ormblogdb MYSQL_USER: testuser MYSQL_PASSWORD: testpass
DDL
CREATE TABLE organizations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL );
