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 ステートメントのレプリケーション」を参照してください。