MySQL で他のテーブルからテーブルを作成 (CREATE TABLE ... SELECT)

前回「 MySQL でテーブルの作成 (CREATE TABLE) 」では、 CREATE TABLE 文を使ってテーブルを作成しました。

今回は CREATE TABLE と SELECT を使って、他のテーブルから取得した結果セットからテーブルを作成する方法をご説明します。

目次
  1. MySQL の CREATE TABLE ... SELECT 文の使い方
  2. CREATE TABLE ... SELECT 文で他のテーブルからテーブルを作成する

MySQL の CREATE TABLE ... SELECT 文の使い方

MySQL で他のテーブルから取得した結果セットからテーブルを作成するには CREATE TABLE ... SELECT 文を使います。

[テーブル1] から SELECT で取得した結果セットから、新しいテーブル [テーブル2] を作成する CREATE TABLE ... SELECT 文の構文は次の通りです。

CREATE TABLE [テーブル2]
   SELECT   [カラム1],
            [カラム2],
            [カラム3],
            ...
   FROM     [テーブル1]

SELECT 文は同じテーブルからでも、結合したテーブルからでも、集計した結果でも OK です。

CREATE TABLE ... SELECT で生成したテーブルには、元のテーブルにあった AUTO_INCREMENT、プライマリーキーなどの制約や、インデックスなどはコピーされませんのでご注意ください。

ここでは省略していますが、CREATE TABLE テーブル名の後に、作成するテーブルの定義を追加することも可能です。


他のテーブルから全カラム全レコードをコピーしたテーブルを作成したい時は次のようにできます。

CREATE TABLE [テーブル2]
   SELECT   * 
   FROM     [テーブル1]

他のテーブルから一時テーブルを作成するには、CREATE TEMPORARY TABLE を使って次のようにします。

CREATE TEMPORARY TABLE [テーブル2]
   SELECT   * 
   FROM     [テーブル1]

一時テーブルはそのセッション内でのみ使用可能で、セッションが閉じられた時に自動的に削除されます。

SELECT 文の結果セットを一時的に保持しておきたい時などに便利です。


CREATE TABLE ... SELECT 文で他のテーブルからテーブルを作成する

それでは、CREATE TABLE ... SELECT 文を使って、他のテーブルからテーブルを作成してみましょう。

次のような students テーブルがあります。

MySQL で他のテーブルからテーブルを作成 (CREATE TABLE ... SELECT) 1


この students テーブルのレコードのうち、男性の学生 (gender = 'M') のレコードを持つ student_males という名前のテーブルを作成するには次のようにできます。

CREATE TABLE student_males
	SELECT 	* 
    FROM 	students
    WHERE	gender = 'M';

上の CREATE TABLE ... SELECT 文を実行して、student_males テーブルの中身を確認します。

MySQL で他のテーブルからテーブルを作成 (CREATE TABLE ... SELECT) 2

student_males テーブルが作成されて、students テーブルの男性の学生のレコードが入っていますね。


DESCRIBE 文を使って、students テーブルと student_males テーブルの構造を確認してみましょう。

MySQL で他のテーブルからテーブルを作成 (CREATE TABLE ... SELECT) 3

MySQL で他のテーブルからテーブルを作成 (CREATE TABLE ... SELECT) 4

カラム名やデータ型は同じですが、プライマリーキーやユニークキー制約、AUTO_INCREMENT はコピーされていません。


既存のテーブルのデータから生成できるデータを保持したテーブルは、正規化を崩し、片方のテーブルのデータを更新してもう一方更新を忘れるなど、データの整合性がとれなくなる原因になります。

ですので、この方法でできたテーブルを永久的にそのまま使うのは、あまりおすすめしません。

一時的にデータを保持したいような時に使うのがおすすめです。


以上、MySQL で CREATE TABLE ... SELECT を使って、他のテーブルから取得した結果セットからテーブルを作成する方法をご説明しました。

© 2024 MySQL 入門