13.1.17.1 CREATE TABLE ... SELECT 構文

CREATE TABLE ステートメントの最後に SELECT ステートメントを追加することによって、あるテーブルを別のテーブルから作成できます。

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

MySQL は、SELECT 内のすべての要素に対して新しいカラムを作成します。例:

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        ENGINE=MyISAM SELECT b,c FROM test2;

これにより、a、b、c の 3 つのカラムを含む MyISAM テーブルが作成されます。ENGINE オプションは CREATE TABLE ステートメントの一部であるため、SELECT のあとに使用してはいけません。これにより、構文エラーが発生します。CHARSET などのその他の CREATE TABLE オプションにも同じことが当てはまります。

SELECT ステートメントからのカラムは、テーブルにオーバーラップされるのではなく、テーブルの右側に付加されます。次の例を考えてみます。

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

テーブル foo 内の行ごとに、foo からの値と新しいカラムのデフォルト値を持つ行が bar 内に挿入されます。

CREATE TABLE ... SELECT の結果として得られるテーブルでは、CREATE TABLE 部分でのみ指定されているカラムが最初に来ます。両方の部分で指定されているカラム、または SELECT 部分でのみ指定されているカラムがそのあとに来ます。SELECT カラムのデータ型は、CREATE TABLE 部分にあるカラムも指定することによってオーバーライドできます。

テーブルへのデータのコピー中にエラーが発生した場合、そのデータは自動的に削除され、作成されません。

一意のキー値を複製する行を処理する方法を示すために、SELECT の前に IGNORE または REPLACE を指定できます。IGNORE を指定すると、一意のキー値に関して既存の行を複製する行は破棄されます。REPLACE を指定すると、新しい行によって同じ一意のキー値を持つ行が置き換えられます。IGNORE と REPLACE のどちらも指定されていない場合は、重複した一意のキー値によってエラーが発生します。

ベースとなる SELECT ステートメント内の行の順序を常に特定することはできないため、MySQL 5.6.4 以降では、CREATE TABLE ... IGNORE SELECT および CREATE TABLE ... REPLACE SELECT ステートメントには、ステートメントベースのレプリケーションには安全でないというフラグが付けられます。この変更により、このようなステートメントは、ステートメントベースモードを使用しているときはログ内に警告を生成し、MIXED モードを使用しているときは行ベース形式を使用してログに記録されます。セクション17.1.2.1「ステートメントベースおよび行ベースレプリケーションのメリットとデメリット」も参照してください。

CREATE TABLE ... SELECT は、どのインデックスも自動的には作成しません。これは、ステートメントをできるだけ柔軟にするために意図的に行われます。作成されたテーブル内にインデックスを設定する場合は、これらを SELECT ステートメントの前に指定するようにしてください。

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

何らかのデータ型の変換が実行される可能性があります。たとえば、AUTO_INCREMENT 属性が保持されないため、VARCHAR カラムは CHAR カラムになることができます。リトレインされる属性は NULL (または NOT NULL) と、それらを含むカラムの場合は、CHARACTER SET、COLLATION、COMMENT、および DEFAULT 句です。

CREATE TABLE ... SELECT を使用してテーブルを作成する場合は、クエリー内のすべての関数呼び出しまたは式にエイリアスを付けるようにしてください。そうしないと、CREATE ステートメントが失敗するか、または好ましくないカラム名が生成される可能性があります。

CREATE TABLE artists_and_works
  SELECT artist.name, COUNT(work.artist_id) AS number_of_works
  FROM artist LEFT JOIN work ON artist.id = work.artist_id
  GROUP BY artist.id;

また、生成されるカラムのデータ型を明示的に指定することもできます。

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

CREATE TABLE ... SELECT で、IF NOT EXISTS が指定されているときに宛先テーブルがすでに存在する場合、その結果はバージョンに依存します。MySQL 5.5.6 より前は、MySQL はこのステートメントを次のように処理します。

• CREATE TABLE 部分で指定されているテーブル定義は無視されます。その定義が既存のテーブルの定義に一致しない場合でも、エラーは発生しません。MySQL は、いずれにしても SELECT 部分から行を挿入しようとします。

• テーブル内のカラム数と、SELECT 部分によって生成されたカラム数の間に不一致がある場合、選択された値は右端のカラムに割り当てられます。たとえば、テーブルに n 個のカラムが含まれているとき、SELECT によって m 個のカラムが生成される場合 (ここで、m < n)、選択された値はテーブル内の右端の m 個のカラムに割り当てられます。最初の n - m 個の各カラムにはデフォルト値が割り当てられます。このデフォルト値は、カラム定義で明示的に、またはその定義にデフォルトが含まれていない場合は暗黙的なカラムデータ型のデフォルトで指定されます。SELECT 部分によって生成されるカラムが多すぎる場合は (m > n)、エラーが発生します。

• 厳密な SQL モードが有効になっており、かつこれらの最初のカラムのいずれにも明示的なデフォルト値が含まれていない場合、このステートメントはエラーで失敗します。

次の例は、IF NOT EXISTS の処理を示しています。

mysql> CREATE TABLE t1 (i1 INT DEFAULT 0, i2 INT, i3 INT, i4 INT);
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE IF NOT EXISTS t1 (c1 CHAR(10)) SELECT 1, 2;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t1;
+------+------+------+------+
| i1   | i2   | i3   | i4   |
+------+------+------+------+
|    0 | NULL |    1 |    2 |
+------+------+------+------+
1 row in set (0.00 sec)

MySQL 5.5.6 の時点で、CREATE TABLE IF NOT EXISTS ... SELECT ステートメントの処理は、宛先テーブルがすでに存在するケースに関して変更されました。また、この変更には 5.1.51 以降の MySQL 5.1 での変更も含まれています。

• 以前は、CREATE TABLE IF NOT EXISTS ... SELECT の場合、MySQL はテーブルが存在するという警告を生成しましたが、いずれにしてもそれらの行を挿入し、そのステートメントをバイナリログに書き込みました。これに対して、CREATE TABLE ... SELECT (IF NOT EXISTS がありません) はエラーで失敗しましたが、MySQL は行を挿入せず、バイナリログにもステートメントを書き込みませんでした。

• MySQL は現在、宛先テーブルが存在する場合、両方のステートメントを同じ方法で処理します。つまり、どちらのステートメントも行を挿入せず、またバイナリログにも書き込まれません。これらの違いは、IF NOT EXISTS が存在する場合は MySQL が警告を生成し、存在しない場合はエラーを生成することです。

この変更は、前の例で言うと、MySQL 5.5.6 の時点では CREATE TABLE IF NOT EXISTS ... SELECT ステートメントが宛先テーブルに何も挿入しないことを示します。

この IF NOT EXISTS の処理の変更により、元の動作を行う MySQL 5.1 マスターから、新しい動作を行う MySQL 5.5 スレーブへのステートメントベースのレプリケーションで非互換性が発生します。CREATE TABLE IF NOT EXISTS ... SELECT がマスター上で実行されたときに、宛先テーブルが存在しているとします。その結果、マスター上では行が挿入されますが、スレーブ上では挿入されません。(行ベースのレプリケーションにはこの問題はありません。)

この問題に対処するために、MySQL 5.1 では、CREATE TABLE IF NOT EXISTS ... SELECT に対するステートメントベースのバイナリロギングが 5.1.51 の時点で変更されました。

• 宛先テーブルが存在しない場合は、変更ありません。ステートメントはそのままログに記録されます。

• 宛先テーブルが存在しない場合、ステートメントは CREATE TABLE IF NOT EXISTS および INSERT ... SELECT ステートメントの同等のペアとしてログに記録されます。(元のステートメント内の SELECT の前に IGNORE または REPLACE が指定されている場合は、INSERT がそれぞれ、INSERT IGNORE または REPLACE になります。)

この変更により、宛先テーブルが存在する場合はマスターとスレーブの両方で行が挿入されるため、MySQL 5.1 から 5.5 へのステートメントベースのレプリケーションでの上位互換性が提供されます。この互換性対策を利用するには、5.1 サーバーは少なくとも 5.1.51 である必要があり、5.5 サーバーは少なくとも 5.5.6 である必要があります。

既存の 5.1 から 5.5 へのレプリケーションシナリオをアップグレードするには、最初にマスターを 5.1.51 以降にアップグレードします。これは、最初にスレーブをアップグレードするという、通常のレプリケーションアップグレードのアドバイスとは異なります。

元の効果 (宛先テーブルが存在するかどうかには関係なく行が挿入される) を実現したいアプリケーションには、CREATE TABLE IF NOT EXISTS ... SELECT ステートメントではなく、CREATE TABLE IF NOT EXISTS および INSERT ... SELECT ステートメントを使用するという回避方法があります。

今説明した変更とともに、次の関連する変更が行われました。以前は、CREATE TABLE IF NOT EXISTS ... SELECT の宛先テーブルとして既存のビューが指定された場合、基礎となるベーステーブルに行が挿入され、このステートメントがバイナリログに書き込まれました。MySQL 5.1.51 および 5.5.6 の時点では、何も挿入されたり、ログに記録されたりしません。

バイナリログを使用して元のテーブルを確実に再作成できるようにするために、MySQL では、CREATE TABLE ... SELECT 中の並列挿入が許可されません。

重要

CREATE TABLE new_table SELECT ... FROM old_table ... などのステートメントで SELECT の一部として FOR UPDATE を使用することはできません。それを行おうとすると、このステートメントは失敗します。これは、CREATE TABLE ... SELECT ステートメントが作成されているテーブル以外のテーブルで変更を行うことを許可していた、MySQL 5.5 およびそれ以前からの動作の変更を表しています。

この変更はまた、古いマスターから MySQL 5.6 以降のスレーブへのステートメントベースのレプリケーションにも影響を与える場合があります。詳細は、セクション17.4.1.5「CREATE TABLE ... SELECT ステートメントのレプリケーション」を参照してください。