開発環境やテスト環境でデータベースをリセットしたいとき、「テーブル構造は残したまま、データだけを削除したい」というケースがあります。

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"

権限

実行するユーザーには以下の権限が必要です：

SELECT on INFORMATION_SCHEMA
DROP on 対象データベース（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オプションでヘッダーなしの出力を取得し、シェルスクリプトで処理

この方法を使えば、テーブル構造を維持したままデータをリセットでき、マイグレーションを再実行する必要がありません。