概要

MariaDB 10.6.3を使って開発をしていて気になったので、取り敢えずここにまとめる。

AUTO_INCREMENTの値がおかしい...

SELECTしてINSERTしたとき、idの値が飛んだのでなんだろうと思って調べてみる。

テスト環境を用意する

test1テーブル(マスタ情報) と test2テーブルを作成する。

・test1テーブル作成 & データを登録する (4件)

CREATE TABLE `test1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '名前',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='テスト1';

INSERT INTO `test1` (`name`) VALUES ('太郎'), ('次郎'), ('三郎'), ('四郎');

・test2テーブル作成

CREATE TABLE `test2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL COMMENT '名前',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='テスト2';

これで２つのテーブルができました。

再現してみる

test2テーブルにtest1のデータをSELECTしてINSERTしてみる。

INSERT INTO `test2` (`name`) SELECT `test1`.`name` FROM `test1`;
INSERT INTO `test2` (`name`) VALUES ('五郎'), ('六郎');

※ 五郎と六郎のIDが飛んでいる...

※ AUTO_INCREMENTは、10にっている...

普通にINSERTしただけなのに、IDが飛んでいるなぜ？

innodb_autoinc_lock_modeについて

どうやら、ちゃんと連番にしたかったら、「innodb_autoinc_lock_mode = 0」にする必要があるみたい。
しかし、パフォーマンスが悪くなるからデフォルトは「innodb_autoinc_lock_mode = 1」らしい。

確認してみる。

SELECT @@innodb_autoinc_lock_mode;

デフォルトのまま「innodb_autoinc_lock_mode = 1」のようです。

innodb_autoinc_lock_modeの設定を変更してみる。

mysql.cnf

[mysqld]
innodb_autoinc_lock_mode=0

再度、test2テーブルにSELECT INSERTしてみる。

TRUNCATE `test2`;
INSERT INTO `test2` (`name`) SELECT `test1`.`name` FROM `test1`;
INSERT INTO `test2` (`name`) VALUES ('五郎'), ('六郎');

※ 五郎、六郎のIDが正常に連番で登録される

※ AUTO_INCREMENTも飛ばず、「７」になっている

以前からINSERTでエラーが発生したら、IDが飛ぶのは知っていました。
しかし、SELECT ＋ INSERTしたらIDが飛ぶのは認識していませんでした。

この方法で、IDは飛ばなくなったのですが、パフォーマンスが気になる。
「innodb_autoinc_lock_mode=1」に戻して、他に方法がないか検討してみる。

mysql.cnf

[mysqld]
innodb_autoinc_lock_mode=1

IDを指定してみる

SELECTしてINSERTする際、IDを指定してみる。

TRUNCATE `test2`;

BEGIN;
SET @num = -1;
SET @current = (SELECT `auto_increment` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE table_name = 'test2');
INSERT INTO `test2` (`id`, `name`)
SELECT
  @current + (@num := @num + 1) AS `id`,
  `test1`.`name` 
FROM 
  `test1` 
WHERE 
  `name` IS NOT NULL
;
COMMIT;

INSERT INTO `test2` (`name`) VALUES ('五郎'), ('六郎');

※ 五郎、六郎のIDは連番になりました

※ AUTO_INCREMENTも正常な値で飛んでいないようです

IDカラム(auto_incrementのカラム)を直接指定したら、発生しないようです。

PostgreSQLのnextval()関数のようなもので事前に取得できればいいのに...
MySQLにはそのような関数は無さそうなので残念...

INSERT後にAUTO_INCREMENTを更新する

値がおかしくなるのであれば、INSERT後にAUTO_INCREMENTを再設定してみたらどうなるだろうと思い試す。

TRUNCATE `test2`;

BEGIN;
INSERT INTO `test2` (`name`) SELECT `test1`.`name` FROM `test1`;

SET @s = CONCAT('ALTER TABLE `test2` AUTO_INCREMENT=', (SELECT MAX(id) + 1 FROM `test2`));
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
COMMIT;

INSERT INTO `test2` (`name`) VALUES ('五郎'), ('六郎');

結果は、IDを指定したときと同じ結果になりました。

この方法でもIDが飛ぶのを解消はできそう...

まとめ

IDが飛ばないように試行錯誤してみましたが、あまりいい方法では無い気がする。
どうしてもIDを連番にしてパフォーマンスが落ちてもいいのであれば、素直に「innodb_autoinc_lock_mode=0」に設定するのがいいのかな...

何かいい方法が見つかればいいけど...
連番にすることが必要であれば、要検討ですね。

参考サイト