技術資料

既存のテーブルを基に、テスト用のテーブルを別の名前で作りたいことは多々あると思います。列やインデックスを一つずつ書き直すのは面倒ですが、MySQLではCREATE TABLE … LIKEで構造を使い回せます。

この構文、便利ですね。最近まで知らなかったので、知った時には便利さに恐れおののきました。今回は、基本の使い方と、コピーされるもの・されないものを整理します。データもコピーしたい場合の方法は、後半で補足します。

CREATE TABLE … LIKEの基本

基本的な書き方は次のとおりです。

CREATE TABLE new_table LIKE original_table;

new_tableが作成するテーブル名、original_tableが元のテーブル名です。元のテーブルにある列の型、NOT NULL、既定値、主キーやインデックスなどの定義を基に、新しいテーブルを作成します。

ここで作られるのは、データが入っていない空のテーブルです。元のテーブルに何万件のデータがあっても、その行がコピーされるわけではありません。構文の詳細は、MySQL公式のCREATE TABLE … LIKEの説明を参照してください。

小さなテーブルで試す

例えば、本番と同じ構造のテーブルをテスト用に用意したい場面で使えます。ここでは本番DBへ接続せず、検証用DBに作った説明用のテーブルで流れを示します。

Workbenchで専用の検証用DBを選択し、次のテーブル名がまだ使われていないことを確認してください。各SQLは作成・追加が成功したことを確認しながら順に実行します。接続や実行範囲の操作は、MySQL Workbench入門の記事も参照してください。

まず、元になるテーブルを作ります。

CREATE TABLE like_demo_products (
    id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (id),
    KEY idx_price (price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

主キーのほかに、price列のインデックスと既定値を付けています。続けて、説明用のデータを2件追加します。このINSERTは一度だけ実行してください。

INSERT INTO like_demo_products (id, name, price)
VALUES
    (1, 'Keyboard', 3000.00),
    (2, 'Cable', 800.00);

次の1文で、同じ構造を基にしたテーブルを別の名前で作れます。

CREATE TABLE like_demo_products_test LIKE like_demo_products;

列の定義をもう一度書かなくてよいのは、ありがたいですね。元のテーブルの名前やデータが、この操作で変更されることもありません。

構造と行数を確認する

構造の確認には、SHOW CREATE TABLEを使います。

SHOW CREATE TABLE like_demo_products;
SHOW CREATE TABLE like_demo_products_test;

Workbenchの結果にあるCreate Table列で、それぞれの定義を比べてみます。今回の例では、次の項目が作成先にもあることを確認します。

• id、name、priceの列と、それぞれの型・NOT NULL指定
• priceの既定値0.00
• idのPRIMARY KEY
• priceのインデックスidx_price

テーブル名は異なるので、表示されるCREATE TABLE文全体が同じ文字列になるわけではありません。必要な列やインデックスがあるかを見ると分かりやすいです。

行数は次のSELECTで比較できます。

SELECT 'original' AS table_role, COUNT(*) AS row_count
FROM like_demo_products
UNION ALL
SELECT 'test' AS table_role, COUNT(*) AS row_count
FROM like_demo_products_test
ORDER BY table_role;

ここまでのSQLを一度ずつ実行した場合の期待結果です。

元には2件あっても、作成先は0件です。構造のコピーとデータのコピーが別であることが分かります。

外部キー定義はコピーされない

CREATE TABLE … LIKEでは、すべての定義がそのまま引き継がれるわけではありません。特に注意したいのが、外部キー定義です。

例えば、元のテーブルがcategory_id列でカテゴリテーブルを参照していても、作成先にその外部キー制約は付きません。列やインデックスが存在することと、参照先との整合性を検査する制約が存在することは別問題です。

外部キーを含む動作をテストしたい場合は、SHOW CREATE TABLEで確認したうえで、テスト用の参照先に合わせて制約を追加する必要があります。構造を使い回せる便利な構文ですが、必要な定義までそろっているかは確認しておきましょう。

補足：データもコピーしたい場合

作成した空テーブルへデータも入れたい場合は、続けてINSERT … SELECTを使えます。今回の例なら、次のようにコピーする列を明示します。

INSERT INTO like_demo_products_test (id, name, price)
SELECT id, name, price
FROM like_demo_products;

空の作成先へ一度だけ実行した場合、2件が追加される想定です。同じSQLを再実行すると、今回の定義ではidが重複するためエラーになります。コピー後は次のSELECTで内容を確認できます。

SELECT id, name, price
FROM like_demo_products_test
ORDER BY id;

期待結果は、idが1のKeyboardとidが2のCableの2行です。必要な行だけをコピーするなら、SELECT側にWHERE条件を付ける方法もあります。詳しくは、MySQL公式のINSERT … SELECTの説明を参照してください。

本番由来のデータをテストに使う場合は、個人情報などをそのまま持ち込まず、マスキングや説明用データへの置き換えを検討しましょう。また、大量データのコピーは負荷やロックの影響を確認してから行いましょう。

使うときの注意点

• コピー元を参照するSELECT権限と、作成先にテーブルを作るCREATE権限が必要です。データも追加する場合は、作成先のINSERT権限も確認しましょう。
• 作成先に同名テーブルがあるとエラーになります。名前と接続先を確認し、作成に失敗したまま後続のINSERTへ進まないようにしましょう。
• CREATE TABLE … LIKEのコピー元はテーブルです。ビューを指定する用途には使えません。
• コピー元の作成時と現在のSQLモードが異なると、定義の検査で失敗する場合があります。

構造だけならCREATE TABLE … LIKE、データも必要なら続けてINSERT … SELECT。まずは小さなテーブルで定義と行数を比較すると、使いどころをつかみやすいかと思います。