概要
データベース運用において、既存のテーブル構造を正確かつ迅速にコピーし、新しいテーブルを作成するニーズは多岐にわたります。開発環境のセットアップ、テストデータの準備、本番環境へのスキーマ変更テスト、あるいはデータ移行のための一時テーブル作成など、その利用シーンは枚挙にいとまがありません。このような場面で、データベース管理者（DBA）として、あるいは開発者として、最も効率的かつ安全な方法を知っていることは、プロジェクトの成否を左右する重要なスキルとなります。
本記事では、そのための強力なSQL構文である`CREATE TABLE … LIKE`に焦点を当て、その基本的な機能から、詳細な挙動、実務における具体的な活用法、さらには類似構文との違いまでを徹底的に解説します。この構文を使いこなすことで、手作業によるスキーマ定義ミスを防ぎ、開発・運用プロセスの大幅な効率化、そして何よりもデータベースの健全性を保つことに貢献できるでしょう。`CREATE TABLE … LIKE`は単なるテーブル作成コマンドではなく、DBAの作業を劇的に変える可能性を秘めた、まさに「極意」とも言える存在です。
詳細解説
`CREATE TABLE … LIKE`構文は、既存のテーブルの構造定義を完全に複製し、その定義に基づいて新しいテーブルを作成するためのものです。この「構造定義」には、単なるカラム定義だけでなく、インデックス、制約（PRIMARY KEY, UNIQUE KEY, FOREIGN KEY）、デフォルト値、NULL許容性、そしてテーブルエンジンや文字コードといったテーブルオプションまでが含まれる点が最大の特徴です。
基本構文
最も基本的な構文は以下の通りです。
`CREATE TABLE new_table_name LIKE existing_table_name;`
この一行のSQLで、`existing_table_name`という既存テーブルのすべての構造情報を引き継いだ`new_table_name`が作成されます。
コピーされる要素
`CREATE TABLE … LIKE`がコピーする要素は多岐にわたりますが、特に重要な点を挙げます。
* **カラム定義**: カラム名、データ型、NULL許容性 (`NOT NULL`)、デフォルト値 (`DEFAULT`)、`AUTO_INCREMENT`属性。
* **インデックス**: PRIMARY KEY、UNIQUE KEY、通常のINDEX、FULLTEXT INDEXなど、定義されているすべてのインデックスが複製されます。これにより、新しいテーブルも元のテーブルと同様の検索性能やデータ整合性を持ちます。
* **制約**: PRIMARY KEY制約、UNIQUE制約、FOREIGN KEY制約。外部キー制約もコピーされるため、関連テーブルとの参照整合性もそのまま維持されます。ただし、外部キーが参照するテーブルが存在しない場合、エラーとなるか、制約が無視される場合があります。
* **テーブルオプション**: ストレージエンジン (`ENGINE`)、文字コード (`CHARSET`)、照合順序 (`COLLATE`)、コメント (`COMMENT`)、圧縮設定など、テーブルレベルで設定されているオプションもすべてコピーされます。
コピーされない要素（および注意点）
非常に多くの要素がコピーされる一方で、いくつか重要な「コピーされない要素」や「RDBMS依存の挙動」も存在します。これらを理解しておくことが、安全な運用には不可欠です。
* **データ**: 最も重要な点として、`CREATE TABLE … LIKE`はテーブル構造のみをコピーし、**データ自体はコピーしません**。これは、本構文が`CREATE TABLE … AS SELECT`と大きく異なる点であり、テスト環境用の空のテーブルを迅速に作成したい場合に特に威力を発揮します。
* **トリガー、ストアドプロシージャ、ビュー、イベント**: これらはテーブルに直接関連するオブジェクトではありますが、テーブルの「構造定義」とは異なり、個別のデータベースオブジェクトとして扱われるため、`LIKE`構文ではコピーされません。これらが必要な場合は、別途DDL（Data Definition Language）を実行して作成する必要があります。
* **パーティション定義**: MySQLの場合、パーティション定義もコピーされます。しかし、他のRDBMS（例: PostgreSQLの`CREATE TABLE … PARTITION OF`は異なる概念）では挙動が異なる場合があるため、利用するRDBMSのドキュメントで確認することが重要です。
* **権限**: テーブルに対するユーザー権限はコピーされません。新しいテーブルには、デフォルトでテーブルを作成したユーザーに付与される権限のみが設定されます。
* **テーブルスペース定義**: 特定のテーブルスペースに格納されている場合でも、その定義が自動的にコピーされるとは限りません。特に、RDBMSによってはテーブルスペースの概念が異なるため注意が必要です。
RDBMSごとの差異（MySQLを中心に）
本記事では主にMySQLの挙動を前提に解説していますが、他の主要なRDBMSにも類似の機能や代替手段が存在します。
* **MySQL**: `CREATE TABLE … LIKE`は、前述の通り、カラム定義、インデックス、PRIMARY KEY、UNIQUE KEY、FOREIGN KEY、デフォルト値、AUTO_INCREMENT、テーブルオプション（ENGINE, CHARSET, COLLATE, COMMENT）、そしてパーティション定義までをも完全にコピーします。これは非常に包括的であり、多くのDBAにとって非常に便利な機能です。
* **PostgreSQL**: PostgreSQLには直接的な`CREATE TABLE … LIKE`構文はありませんが、`CREATE TABLE new_table (LIKE existing_table INCLUDING ALL);`という形で実現できます。`INCLUDING ALL`を指定することで、DEFAULT値、制約（NOT NULL、CHECK、UNIQUE、PRIMARY KEY）、インデックス、ストレージパラメータ、コメントもコピーされます。`INCLUDING DEFAULTS`、`INCLUDING CONSTRAINTS`などのオプションでコピーする要素を細かく制御できます。
* **SQL Server**: SQL Serverには`CREATE TABLE … LIKE`に直接対応する構文はありません。代替として、`SELECT * INTO new_table FROM existing_table WHERE 1=0;`という構文を用いることで、カラム名とデータ型、NULL許容性のみをコピーした空のテーブルを作成できます。ただし、PRIMARY KEY、UNIQUE KEY、FOREIGN KEY、インデックス、デフォルト値、IDENTITY属性などはコピーされません。これらは別途手動でDDLを実行する必要があります。
このように、RDBMSによって`LIKE`構文の挙動や利用可能なオプションが異なるため、特定のデータベース環境で利用する際には、そのRDBMSの公式ドキュメントで詳細を確認することが不可欠です。
サンプルコード
ここでは、MySQL環境を想定した`CREATE TABLE … LIKE`の具体的な使用例を示します。
基本的な利用例
まず、元のテーブルを作成します。このテーブルには、基本的なカラム定義に加えて、PRIMARY KEY、AUTO_INCREMENT、デフォルト値を含めます。
-- 1. 元のテーブルを作成
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) DEFAULT 'no_email@example.com',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
is_active BOOLEAN DEFAULT TRUE,
INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='ユーザー情報テーブル';
-- 2. 元のテーブルにデータを挿入 (データがコピーされないことを確認するため)
INSERT INTO users (username, email) VALUES
('alice', 'alice@example.com'),
('bob', 'bob@example.com');
-- 3. 元のテーブルの構造を確認
SHOW CREATE TABLE users;
-- または
DESCRIBE users;
次に、`CREATE TABLE … LIKE`を使用して、この`users`テーブルの構造をコピーした新しいテーブル`new_users`を作成します。
-- 4. CREATE TABLE ... LIKE を使用して新しいテーブルを作成
CREATE TABLE new_users LIKE users;
-- 5. 新しいテーブルの構造を確認
SHOW CREATE TABLE new_users;
-- または
DESCRIBE new_users;
-- 6. 新しいテーブルのデータを確認 (空であることを確認)
SELECT * FROM new_users;
`SHOW CREATE TABLE new_users;`の出力を見ると、`users`テーブルと全く同じカラム定義、PRIMARY KEY、UNIQUE KEY、INDEX、AUTO_INCREMENT、DEFAULT値、ENGINE、CHARSET、COLLATE、COMMENTがコピーされていることが確認できます。一方で、`SELECT * FROM new_users;`の結果は空であり、データはコピーされていないことが分かります。
インデックスと外部キーを含む例
次に、外部キー制約を持つテーブルの構造をコピーする例を示します。
-- 1. 参照される親テーブルを作成
CREATE TABLE categories (
category_id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 2. 外部キーを持つ子テーブルを作成
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category_id INT,
price DECIMAL(10, 2) DEFAULT 0.00,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL,
INDEX idx_product_name (product_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 3. 元のテーブルの構造を確認
SHOW CREATE TABLE products;
この`products`テーブルの構造を`LIKE`でコピーします。
-- 4. CREATE TABLE ... LIKE を使用して新しいテーブルを作成
CREATE TABLE new_products LIKE products;
-- 5. 新しいテーブルの構造を確認
SHOW CREATE TABLE new_products;
`SHOW CREATE TABLE new_products;`の出力には、`products`テーブルと同様にPRIMARY KEY、`idx_product_name`インデックス、そして`FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL`という外部キー制約が正しくコピーされていることが確認できます。これにより、関連するテーブルとの参照整合性も考慮された状態で新しいテーブルが作成されます。
実務アドバイス
`CREATE TABLE … LIKE`は、そのシンプルさからは想像できないほど、DBAの日常業務において多大な恩恵をもたらします。以下に、実務での具体的な活用シーンと注意点について解説します。
開発・テスト環境での活用
* **本番環境に近いテスト環境の迅速構築**: 新機能開発やバグ修正の際、本番環境のテーブル構造を正確に再現したテストテーブルが必要になることが頻繁にあります。`CREATE TABLE … LIKE`を使用すれば、複雑なDDLを手動で記述することなく、瞬時に本番に近いスキーマを持つ空のテーブルを作成できます。これにより、テストデータの投入やアプリケーション連携テストを迅速に開始できます。
* **スキーマ変更の事前検証**: 大規模な`ALTER TABLE`操作を本番環境に適用する前に、テスト環境でその影響を検証することは必須です。`CREATE TABLE … LIKE`で元のテーブル構造をコピーし、そのコピーに対して`ALTER TABLE`を実行することで、変更内容が期待通りに適用されるか、パフォーマンスに問題がないかなどを安全に確認できます。万が一の問題発生時にも、本番環境に影響を与えることなく試行錯誤が可能です。
スキーマ変更時のベストプラクティス
* **ロールバックポイントの確保**: 重要なスキーマ変更を行う前には、必ずロールバック計画を立てるべきです。`CREATE TABLE … LIKE old_table_name;`で元のテーブル構造をコピーし、`ALTER TABLE`の適用前にそのコピーをバックアップとして保存しておくことで、問題発生時に迅速に元の状態に戻すためのDDLを生成したり、比較検証したりする基礎とすることができます。
* **新旧テーブルの比較検証**: 複雑なスキーマ変更（例: カラムの分割、データ型の変更）を行う際、変更前と変更後のテーブル構造を正確に比較したい場合があります。`LIKE`で作成したテーブルと元のテーブルの`SHOW CREATE TABLE`の結果を比較することで、意図しない変更がないか、必要な要素が欠落していないかなどを容易に確認できます。
パフォーマンスへの影響と注意点
* **非常に高速な操作**: `CREATE TABLE … LIKE`はデータをコピーしないため、テーブルサイズがどれほど大きくても、構造のコピーはほぼ瞬時に完了します。これは、データコピーを伴う`CREATE TABLE … AS SELECT`とは対照的で、特に大規模テーブルの構造コピーにおいては圧倒的なパフォーマンス優位性があります。
* **インデックス・制約の再構築**: コピーされるインデックスや外部キー制約は、新しいテーブル上で新たに構築されます。この構築プロセス自体には、テーブルの構造の複雑さ（インデックス数や外部キーの参照関係の深さ）に応じた時間がかかりますが、これはテーブルデータ量には依存しません。
* **パーティション定義の挙動**: MySQLではパーティション定義もコピーされますが、他のRDBMSでは異なる場合があります。パーティションを使用しているテーブルをコピーする際は、必ずそのRDBMSの挙動を確認し、必要であれば手動でパーティション定義を再構築してください。
* **権限**: `CREATE TABLE … LIKE`を実行するには、データベースに対する`CREATE`権限が必要です。また、`LIKE`元となるテーブルが存在するデータベースに対する`SELECT`権限は必要ありませんが、参照される外部キーのテーブルが存在するデータベースに対する`SELECT`権限は必要となる場合があります。
`CREATE TABLE … AS SELECT … WITH NO DATA`との使い分け
`CREATE TABLE … AS SELECT … WHERE 1=0;` (または`WITH NO DATA`などRDBMS依存) も、既存テーブルの構造をコピーして空のテーブルを作成する際に用いられる構文ですが、`CREATE TABLE … LIKE`とはコピーされる要素に大きな違いがあります。
* **`CREATE TABLE … LIKE`**: カラム定義、データ型、NULL許容性、デフォルト値、`AUTO_INCREMENT`、**PRIMARY KEY、UNIQUE KEY、FOREIGN KEY、インデックス、テーブルオプション（ENGINE, CHARSETなど）**のすべてをコピーします。より完全な構造の複製に適しています
コメント