環境

MySQL 8.0

経緯

テーブルを削除したあとでオートインクリメントを実行する想定でした。

以下は例です。

-- トランザクション開始
BEGIN;

-- 確認（5件）
SELECT COUNT(*) FROM users;

-- 削除
DELETE FROM users;

-- 確認（0件）
SELECT COUNT(*) FROM users;

-- 連番ふり直す
ALTER TABLE `users` AUTO_INCREMENT = 1;

-- （中略、別作業、、）

-- トランザクション終了
COMMIT;

中略した別作業で失敗したため、いったんROLLBACKし、SQLを修正したあとでもう一度実行しました。

すると、最初のSELECT COUNT(*) FROM usersですでに0件になっていました。

5件あるはずなのに！

なぜ？

MySQLでは、ALTER TABLEも含めてDDLを実行すると暗黙的なコミットが発生するようです。

データベースオブジェクトを定義または変更するデータ定義言語 (DDL) ステートメント。
ALTER EVENT, ALTER FUNCTION, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER VIEW, CREATE DATABASE, CREATE EVENT, CREATE FUNCTION, CREATE INDEX, CREATE PROCEDURE, CREATE ROLE, CREATE SERVER, CREATE SPATIAL REFERENCE SYSTEM, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, DROP DATABASE, DROP EVENT, DROP FUNCTION, DROP INDEX, DROP PROCEDURE, DROP ROLE, DROP SERVER, DROP SPATIAL REFERENCE SYSTEM, DROP TABLE, DROP TRIGGER, DROP VIEW, INSTALL PLUGIN, RENAME TABLE, TRUNCATE TABLE, UNINSTALL PLUGIN。

そういえばそんな仕様があったような......と思い出したので、パニックにならずにすみました（でも実行する前に気が付きたかった）。

どうすれば良かったか

トランザクション中にALTER TABLEせず、終了後に実施します。