テーブルのバックアップで学んだ話

CREATE TABLE ... AS SELECT ... でバックアップ（bk）テーブルを作る時の落とし穴

（インデックス・外部キーがコピーされない／CREATE TABLE ... LIKE ... では外部キーのみコピーされない）

DBの作業中、「念のためバックアップを取りたい」という場面はよくあります。
そのとき、つい次のように 元テーブルの内容をまるごとコピーして bk テーブルを作る ことがあります。

CREATE TABLE original_table_bk AS
SELECT * FROM original_table;

一見便利なのですが、この方法でバックアップ用テーブルを作る場合は注意が必要でした。

結論：コピーされるのは「データ」だけで、定義（インデックス・外部キー等）はコピーされない

CREATE TABLE ... AS SELECT ... は、データ自体はコピーされます。
しかし、データ以外の項目（テーブル定義の重要部分）はコピーされません。

代表的にコピーされないもの

• インデックス（PRIMARY KEY / UNIQUE / INDEX）
• 外部キー制約（FOREIGN KEY）
• AUTO_INCREMENT
• DEFAULT 値
• NOT NULL 制約（状況による）
• コメント、トリガーなど

そのため、bk テーブルは「見た目は同じ列がある」だけで、実体は別物になりやすいです。

実際にハマったこと：インデックスと外部キーを後から設定し直して工数が増えた

上記の方法で bk テーブルを作成したところ、後から次の問題が発生しました。

• 「検索が遅い（インデックスが無い）」
• 「整合性を保ちたい（外部キーが無い）」

結局、インデックスと外部キー制約を設定し直す ことになりました。

バックアップのつもりが、テーブル定義の復元作業まで発生し、想定以上の工数がかかってしまいました。

さらに怖い：bkテーブルを削除できないケースがある（外部キーのせい）

仮に bk テーブルにも外部キーを張り直した場合、別の落とし穴があります。

• bkテーブルが「参照される側」になってしまうと DROP できない
• 運用や作業の流れによっては、他テーブルが bk テーブルを参照する外部キーを持ってしまい、
  • DROP TABLE original_table_bk; が失敗する
  • 先に外部キー制約を外す必要がある
  • 依存関係の洗い出しが必要になる

といった状況になります。

「バックアップのために作ったテーブルのせいで削除がスムーズにできない」というのは、地味にストレスが大きいポイントです。

じゃあどうするのが良い？（現実的な選択肢）

用途によって選択肢が変わります。

1) "定義も含めて" 同じテーブルを作りたいなら：CREATE TABLE ... LIKE ...

MySQLならこちらの方が安全です。

CREATE TABLE original_table_bk LIKE original_table;
INSERT INTO original_table_bk SELECT * FROM original_table;

ポイント

• LIKE で テーブル定義（インデックスなど）をコピー
• INSERT ... SELECT で データをコピー
• ※インデックスはコピーされるが、外部キーはコピーされない（MySQL の仕様）。外部キーが必要な場合は別途 ALTER TABLE で追加する必要があります。

インデックスの確認方法

元テーブルと bk テーブルでインデックスが同じか確認するには、次のクエリが使えます。

-- 各テーブルのインデックスを確認
SHOW INDEX FROM original_table;
SHOW INDEX FROM original_table_bk;

-- 両テーブルを並べて比較
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columns
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('original_table', 'original_table_bk')
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY TABLE_NAME, INDEX_NAME;

検証：テーブル作成と SHOW CREATE TABLE の結果

本記事の検証で使用したテーブル作成クエリと、SHOW CREATE TABLE の結果です。original_table には外部キーがありますが、original_table_bk（CREATE TABLE ... LIKE ... で作成）には外部キーが含まれていないことが確認できます。

-- 参照先テーブル（外部キー用）
CREATE TABLE category (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- 元テーブル（バックアップ対象）
CREATE TABLE original_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    category_id INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES category(id),
    INDEX idx_category_id (category_id),
    INDEX idx_created_at (created_at)
);

-- サンプルデータ
INSERT INTO category (id, name) VALUES (1, 'カテゴリA'), (2, 'カテゴリB');
INSERT INTO original_table (id, name, category_id) VALUES (1, 'レコード1', 1), (2, 'レコード2', 2);

-- bk テーブル作成（CREATE TABLE ... LIKE ...）
CREATE TABLE original_table_bk LIKE original_table;
INSERT INTO original_table_bk SELECT * FROM original_table;

original_table の結果（外部キーあり）

original_table | CREATE TABLE `original_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `category_id` int NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `original_table_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

original_table_bk の結果（外部キーなし）

original_table_bk | CREATE TABLE `original_table_bk` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `category_id` int NOT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

→ original_table_bk には CONSTRAINT ... FOREIGN KEY ... の行が存在しません。

2) 本当に欲しいのがバックアップなら：dump / エクスポートを使う

「バックアップ」が目的であれば、テーブルを増やすよりも次の方法が安全です。

方法	概要	特徴
mysqldump（論理バックアップ）	MySQL 標準のコマンドで、テーブル定義とデータを SQL 形式で出力	定義・データ・インデックス・外部キーをすべて含む。別サーバーへの移行にも使える
スナップショット / バックアップ機構（例：RDS など）	ストレージの状態をそのまま保存する物理バックアップ	クラウドサービスが自動で提供。運用負荷が少なく、本番向け
CSV エクスポート（簡易用途）	データを CSV 形式で出力	定義は含まない。小規模な一時退避や分析に便利

バックアップ専用の仕組みに寄せた方が、運用としては安定しやすいです。

まとめ

• CREATE TABLE ... AS SELECT ... は データはコピーされるが、インデックスや外部キー等はコピーされない
• CREATE TABLE ... LIKE ... で インデックスはコピーされるが、外部キーはコピーされない
• 後から定義を揃えることになり、工数が増える
• 外部キーの状況によっては、bk テーブルを削除しにくくなる
• MySQLなら CREATE TABLE ... LIKE ... + INSERT ... SELECT ... の方が安全
• そもそもバックアップ目的なら、dump等の方式を検討した方がよい