概要
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の設定を変更してみる。
[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」に戻して、他に方法がないか検討してみる。
[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」に設定するのがいいのかな...
何かいい方法が見つかればいいけど...
連番にすることが必要であれば、要検討ですね。