開発環境やテスト環境でデータベースをリセットしたいとき、「テーブル構造は残したまま、データだけを削除したい」というケースがあります。
DROP DATABASEしてしまうとマイグレーションを再実行する必要がありますが、TRUNCATE TABLEならテーブル構造を維持したままデータだけを削除できます。
本記事では、MySQLデータベース内の全テーブルを動的に取得し、一括でTRUNCATEするシェルスクリプトの実装方法を紹介します。
DELETEとは
DELETE は、テーブルの行を削除する DML ステートメントです。
テーブル内のデータの行を指定して削除することができます。
WHERE句を使って条件を絞ることも、全レコードを削除することも可能です。
-- 全レコードを削除
DELETE FROM users;
-- 条件を指定して削除
DELETE FROM users WHERE id = 10;
TRUNCATE TABLEとは
TRUNCATE TABLEは条件指定など行わず、テーブル内のデータを全て削除します。
ただ削除するだけならDELETEを使えばいいのでは？と思うかもしれませんが、TRUNCATE TABLEには大きな利点があります。
切り捨て操作はテーブルを削除して再作成するため、特に大きなテーブルの場合は、行を 1 つずつ削除するよりはるかに高速です。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.37 TRUNCATE TABLE ステートメント
全データを削除する際にはTRUNCATE TABLEが推奨されています。
DELETEとTRUNCATEの違い
| 操作 | 速度 | AUTO_INCREMENT | ロールバック | WHERE句 |
|---|---|---|---|---|
| DELETE | 低速 | 維持される | 可能 | 使用可能 |
| TRUNCATE | 高速 | リセットされる | 不可 | 使用不可 |
注意点：ロールバックできない
切り捨て操作は暗黙的なコミットを発生させるため、ロールバックできません。
MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.37 TRUNCATE TABLE ステートメント
本番環境で間違えてコマンドを叩いてデータを削除したら…と考えるだけで恐ろしいですね。
実装のポイント
1. INFORMATION_SCHEMAからテーブル一覧を取得
MySQLのINFORMATION_SCHEMA.TABLESを使用して、指定したデータベース内の全テーブルを動的に取得します。
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='your_database'
AND TABLE_TYPE='BASE TABLE';
TABLE_TYPE='BASE TABLE'を指定することで、ビューを除外しています。
2. 外部キー制約の一時無効化
テーブル間に外部キー制約がある場合、参照先のテーブルを先にTRUNCATEしようとするとエラーになります。
FOREIGN_KEY_CHECKSを一時的に無効化することで回避できます。
-- 無効化
SET FOREIGN_KEY_CHECKS=0;
-- TRUNCATEを実行...
-- 再有効化
SET FOREIGN_KEY_CHECKS=1;
3. データベースの存在チェック
存在しないデータベースに対して操作しようとするとエラーになるため、事前にチェックします。
SELECT SCHEMA_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME='your_database';
完成版スクリプト
#!/bin/sh
set -e
# この辺りでDB接続のための環境変数をセットする
# 指定したDBの全テーブルをTRUNCATEする関数
truncate_all_tables() {
DB_NAME=$1
echo "Truncating all tables in $DB_NAME..."
# データベースが存在するかチェック
DB_EXISTS=$(mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -N -e "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='$DB_NAME';" 2>/dev/null)
if [ -z "$DB_EXISTS" ]; then
echo " Database $DB_NAME does not exist, skipping..."
return
fi
# 全テーブルを取得
TABLES=$(mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -N -e "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$DB_NAME' AND TABLE_TYPE='BASE TABLE';" 2>/dev/null)
if [ -z "$TABLES" ]; then
echo " No tables found in $DB_NAME, skipping..."
return
fi
# 外部キー制約の無効化とTRUNCATEを同一接続で実行
SQL="SET FOREIGN_KEY_CHECKS=0;"
for TABLE in $TABLES; do
echo " Truncating $DB_NAME.$TABLE..."
SQL="${SQL} TRUNCATE TABLE \`$TABLE\`;"
done
SQL="${SQL} SET FOREIGN_KEY_CHECKS=1;"
mysql -h "$MYSQL_HOST" -P "$MYSQL_PORT" -u "$MYSQL_USER" -p"$MYSQL_PASSWORD" -e "$SQL" "$DB_NAME"
echo " Done truncating $DB_NAME"
}
# 使用例
truncate_all_tables "my_database"
mysqlコマンドのオプション解説
| オプション | 説明 |
|---|---|
-h |
ホスト名 |
-P |
ポート番号 |
-u |
ユーザー名 |
-p |
パスワード（オプションとパスワードの間にスペースを入れない） |
-N |
カラムヘッダーを出力しない（スクリプトでの処理に便利） |
-e |
SQLを直接実行 |
その他の注意点
本番環境での使用禁止
このスクリプトは開発・テスト環境専用です。本番環境では絶対に使用しないでください。
テーブル名のエスケープ
テーブル名にはバッククォート（`）を使用してエスケープしています。これにより、予約語や特殊文字を含むテーブル名にも対応できます。
mysql -e "TRUNCATE TABLE \`$TABLE\`;" "$DB_NAME"
権限
実行するユーザーには以下の権限が必要です：
-
SELECTonINFORMATION_SCHEMA -
DROPon 対象データベース（TRUNCATEに必要）
応用：Dockerコンテナ化
このスクリプトをDockerイメージ化することで、CI/CDパイプラインやECSタスクから実行できます。
FROM alpine:3.19
RUN apk add --no-cache mysql-client
COPY scripts/truncate-db.sh /truncate-db.sh
RUN chmod +x /truncate-db.sh
ENTRYPOINT ["/truncate-db.sh"]
-
INFORMATION_SCHEMA.TABLESを使って動的にテーブル一覧を取得 -
FOREIGN_KEY_CHECKS=0で外部キー制約を一時無効化 -
-Nオプションでヘッダーなしの出力を取得し、シェルスクリプトで処理
この方法を使えば、テーブル構造を維持したままデータをリセットでき、マイグレーションを再実行する必要がありません。