MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.1.20.4 CREATE TABLE ... SELECT ステートメント

MySQL 8.0 リファレンスマニュアル / ... / CREATE TABLE ... SELECT ステートメント

13.1.20.4 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 部分にあるカラムも指定することによってオーバーライドできます。

テーブルへのデータのコピー中にエラーが発生した場合、テーブルは自動的に削除され、作成されません。ただし、MySQL 8.0.21 より前では、行ベースレプリケーションが使用されている場合、CREATE TABLE ... SELECT ステートメントは、テーブルを作成するトランザクションとデータを挿入するトランザクションの 2 つとしてバイナリログに記録されます。ステートメントがバイナリログから適用された場合、2 つのトランザクション間またはデータのコピー中に障害が発生すると、空のテーブルが複製される可能性があります。この制限は、MySQL 8.0.21 では削除されます。アトミック DDL をサポートするストレージエンジンでは、行ベースレプリケーションが使用されているときに、CREATE TABLE ... SELECT が 1 つのトランザクションとして記録および適用されるようになりました。詳細は、セクション13.1.1「アトミックデータ定義ステートメントのサポート」を参照してください。

MySQL 8.0.21 の時点では、アトミック DDL 制約と外部キー制約の両方をサポートするストレージエンジンでは、行ベースレプリケーションが使用されている場合、CREATE TABLE ... SELECT ステートメントで外部キーの作成は許可されません。外部キー制約は、後で ALTER TABLE を使用して追加できます。

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

MySQL 8.0.19 以降では、CREATE TABLE ... SELECT の SELECT 部分で VALUES ステートメントを使用することもできます。ステートメントの VALUES 部分には、AS 句を使用してテーブルのエイリアスを含める必要があります。 VALUES からのカラムに名前を付けるには、テーブルのエイリアスを使用してカラムのエイリアスを指定します。それ以外の場合は、デフォルトのカラム名の column_0, column_1, column_2... が使用されます。

それ以外の場合、作成されるテーブルのカラムのネーミングは、このセクションで前述したものと同じルールに従います。例:

mysql> CREATE TABLE tv1
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v;
mysql> TABLE tv1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        3 |        5 |
|        2 |        4 |        6 |
+----------+----------+----------+

mysql> CREATE TABLE tv2
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
mysql> TABLE tv2;
+---+---+---+
| x | y | z |
+---+---+---+
| 1 | 3 | 5 |
| 2 | 4 | 6 |
+---+---+---+

mysql> CREATE TABLE tv3 (a INT, b INT, c INT)
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
mysql> TABLE tv3;
+------+------+------+----------+----------+----------+
| a    | b    | c    | column_0 | column_1 | column_2 |
+------+------+------+----------+----------+----------+
| NULL | NULL | NULL |        1 |        3 |        5 |
| NULL | NULL | NULL |        2 |        4 |        6 |
+------+------+------+----------+----------+----------+

mysql> CREATE TABLE tv4 (a INT, b INT, c INT)
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
mysql> TABLE tv4;
+------+------+------+---+---+---+
| a    | b    | c    | x | y | z |
+------+------+------+---+---+---+
| NULL | NULL | NULL | 1 | 3 | 5 |
| NULL | NULL | NULL | 2 | 4 | 6 |
+------+------+------+---+---+---+

mysql> CREATE TABLE tv5 (a INT, b INT, c INT)
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(a,b,c);
mysql> TABLE tv5;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    3 |    5 |
|    2 |    4 |    6 |
+------+------+------+

すべてのカラムを選択し、デフォルトのカラム名を使用する場合、SELECT * を省略できるため、テーブル tv1 の作成に使用したステートメントも次のように記述できます:

mysql> CREATE TABLE tv1 VALUES ROW(1,3,5), ROW(2,4,6);
mysql> TABLE tv1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        3 |        5 |
|        2 |        4 |        6 |
+----------+----------+----------+

VALUES を SELECT のソースとして使用する場合、すべてのカラムが常に新しいテーブルに選択され、名前付きのテーブルから選択する場合と同じように個々のカラムを選択することはできません。次の各ステートメントではエラー (ER_OPERAND_COLUMNS) が生成されます:

CREATE TABLE tvx
    SELECT (x,z) FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);

CREATE TABLE tvx (a INT, c INT)
    SELECT (x,z) FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);

同様に、SELECT のかわりに TABLE ステートメントを使用できます。これは、VALUES の場合と同じルールに従います。ソーステーブルのすべてのカラムとその名前は、常に新しいテーブルに挿入されます。例:

mysql> TABLE t1;
+----+----+
| a  | b  |
+----+----+
|  1 |  2 |
|  6 |  7 |
| 10 | -4 |
| 14 |  6 |
+----+----+

mysql> CREATE TABLE tt1 TABLE t1;
mysql> TABLE tt1;
+----+----+
| a  | b  |
+----+----+
|  1 |  2 |
|  6 |  7 |
| 10 | -4 |
| 14 |  6 |
+----+----+

mysql> CREATE TABLE tt2 (x INT) TABLE t1;
mysql> TABLE tt2;
+------+----+----+
| x    | a  | b  |
+------+----+----+
| NULL |  1 |  2 |
| NULL |  6 |  7 |
| NULL | 10 | -4 |
| NULL | 14 |  6 |
+------+----+----+

基礎となる SELECT ステートメントの行の順序を常に決定できるわけではないため、CREATE TABLE ... IGNORE SELECT および CREATE TABLE ... REPLACE SELECT ステートメントには、ステートメントベースのレプリケーションに対して安全でないフラグが付けられます。このようなステートメントは、ステートメントベースのモードの使用時にエラーログに警告を生成し、MIXED モードの使用時に行ベースの形式を使用してバイナリログに書き込まれます。セクション17.2.1.1「ステートメントベースおよび行ベースレプリケーションのメリットとデメリット」も参照してください。

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

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

CREATE TABLE ... SELECT の場合、宛先テーブルでは、選択元テーブルのカラムが生成されたカラムであるかどうかに関する情報は保持されません。ステートメントの SELECT 部分では、宛先テーブルの生成されたカラムに値を割り当てることはできません。

CREATE TABLE ... SELECT の場合、宛先テーブルは元のテーブルの式のデフォルト値を保持します。

何らかのデータ型の変換が実行される可能性があります。たとえば、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 では、CREATE TABLE ... SELECT 中の並列挿入が許可されません。ただし、MySQL 8.0.21 より前では、行ベースレプリケーションが使用されているときにバイナリログから CREATE TABLE ... SELECT 操作が適用されると、データのコピー中にレプリケートされたテーブルでの同時挿入が許可されます。この制限は、アトミック DDL をサポートするストレージエンジン上の MySQL 8.0.21 では削除されます。詳細は、セクション13.1.1「アトミックデータ定義ステートメントのサポート」を参照してください。

CREATE TABLE new_table SELECT ... FROM old_table ... などのステートメントで SELECT の一部として FOR UPDATE を使用することはできません。それを行おうとすると、このステートメントは失敗します。

CREATE TABLE ... SELECT 操作では、ENGINE_ATTRIBUTE および SECONDARY_ENGINE_ATTRIBUTE の値はカラムにのみ適用されます。明示的に指定しないかぎり、テーブルおよびインデックスの ENGINE_ATTRIBUTE および SECONDARY_ENGINE_ATTRIBUTE の値は新しいテーブルに適用されません。

関連キーワード: 　ステートメント, TABLE, CREATE, テーブル, カラム, DROP, FROM, 作成, VALUES, INT

PREV HOME UP NEXT