MySQLでテーブルを正しくTRUNCATE（切り捨て）する方法【外部キー制約への対処も解説】

外部キー制約があるテーブルをTRUNCATEするには

MySQLでは、外部キー制約が設定されたテーブルに対してそのままTRUNCATEを実行すると、参照整合性のチェックによってエラーが発生し、処理できません。これを回避するには、まずFOREIGN_KEY_CHECKSを無効化してからテーブルを切り捨て、すべての処理が完了した後にもう一度有効化します。

基本の構文

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE yourTableName1;
TRUNCATE TABLE yourTableName2;
TRUNCATE TABLE yourTableName3;
.
.
.
TRUNCATE TABLE yourTableNameN;
SET FOREIGN_KEY_CHECKS = 1;

このように、チェックをオフにしてから各テーブルを順番にTRUNCATEし、最後に必ずチェックをオンに戻すのがポイントです。

実際の実行例

それでは、データベース「test」内のいくつかのテーブルを実際に切り捨ててみましょう。以下のクエリを実行します。

mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> TRUNCATE TABLE skiplasttenrecords;
Query OK, 0 rows affected (0.97 sec)

mysql> TRUNCATE TABLE searchtextdemo;
Query OK, 0 rows affected (0.89 sec)

mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

エラーが出ることなく、両方のテーブルが正常に空になりました。

データが削除されたかどうかを確認する

本当にテーブルの中身が空になっているかを確認するには、SELECT文を実行します。

mysql> SELECT * FROM searchtextdemo;
Empty set (0.00 sec)

mysql> SELECT * FROM skiplasttenrecords;
Empty set (0.00 sec)

結果として「Empty set」と表示されれば、テーブル内にレコードが1件も存在しないこと、つまりTRUNCATEが正しく完了したことを意味します。

注意点

FOREIGN_KEY_CHECKS = 0に設定している間は、参照整合性の検証が行われなくなります。誤った操作や不整合なデータの投入につながる可能性もあるため、作業が完了したら必ずSET FOREIGN_KEY_CHECKS = 1;で元に戻すことを忘れないようにしましょう。