Movatterモバイル変換

You can create one table from another by adding aSELECT statement at the end of theCREATE TABLE statement:

CREATE TABLEnew_tbl [AS] SELECT * FROMorig_tbl;

MySQL creates new columns for all elements in theSELECT. For example:

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

This creates anInnoDB table with three columns,a,b, andc. TheENGINE option is part of theCREATE TABLE statement, and should not be used following theSELECT; this would result in a syntax error. The same is true for otherCREATE TABLE options such asCHARSET.

Notice that the columns from theSELECT statement are appended to the right side of the table, not overlapped onto it. Take the following example:

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: 0mysql> SELECT * FROM bar;+------+---+| m    | n |+------+---+| NULL | 1 |+------+---+1 row in set (0.00 sec)

For each row in tablefoo, a row is inserted inbar with the values fromfoo and default values for the new columns.

In a table resulting fromCREATE TABLE ... SELECT, columns named only in theCREATE TABLE part come first. Columns named in both parts or only in theSELECT part come after that. The data type ofSELECT columns can be overridden by also specifying the column in theCREATE TABLE part.

For storage engines that support both atomic DDL and foreign key constraints, creation of foreign keys is not permitted inCREATE TABLE ... SELECT statements when row-based replication is in use. Foreign key constraints can be added later usingALTER TABLE.

You can precede theSELECT byIGNORE orREPLACE to indicate how to handle rows that duplicate unique key values. WithIGNORE, rows that duplicate an existing row on a unique key value are discarded. WithREPLACE, new rows replace rows that have the same unique key value. If neitherIGNORE norREPLACE is specified, duplicate unique key values result in an error. For more information, seeThe Effect of IGNORE on Statement Execution.

You can also use aVALUES statement in theSELECT part ofCREATE TABLE ... SELECT; theVALUES portion of the statement must include a table alias using anAS clause. To name the columns coming fromVALUES, supply column aliases with the table alias; otherwise, the default column namescolumn_0,column_1,column_2, ..., are used.

Otherwise, naming of columns in the table thus created follows the same rules as described previously in this section. Examples:

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    |        x |        y |        z |+------+------+------+----------+----------+----------+| 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 |+------+------+------+

When selecting all columns and using the default column names, you can omitSELECT *, so the statement just used to create tabletv1 can also be written as shown here:

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 |+----------+----------+----------+

When usingVALUES as the source of theSELECT, all columns are always selected into the new table, and individual columns cannot be selected as they can be when selecting from a named table; each of the following statements produces an error (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);

Similarly, you can use aTABLE statement in place of theSELECT. This follows the same rules as withVALUES; all columns of the source table and their names in the source table are always inserted into the new table. Examples:

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 |+------+----+----+

Because the ordering of the rows in the underlyingSELECT statements cannot always be determined,CREATE TABLE ... IGNORE SELECT andCREATE TABLE ... REPLACE SELECT statements are flagged as unsafe for statement-based replication. Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when usingMIXED mode. See alsoSection 19.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.

CREATE TABLE ... SELECT does not automatically create any indexes for you. This is done intentionally to make the statement as flexible as possible. If you want to have indexes in the created table, you should specify these before theSELECT statement:

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

ForCREATE TABLE ... SELECT, the destination table does not preserve information about whether columns in the selected-from table are generated columns. TheSELECT part of the statement cannot assign values to generated columns in the destination table.

ForCREATE TABLE ... SELECT, the destination table does preserve expression default values from the original table.

Some conversion of data types might occur. For example, theAUTO_INCREMENT attribute is not preserved, andVARCHAR columns can becomeCHAR columns. Retrained attributes areNULL (orNOT NULL) and, for those columns that have them,CHARACTER SET,COLLATION,COMMENT, and theDEFAULT clause.

When creating a table withCREATE TABLE ... SELECT, make sure to alias any function calls or expressions in the query. If you do not, theCREATE statement might fail or result in undesirable column names.

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;

You can also explicitly specify the data type for a column in the created table:

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

ForCREATE TABLE ... SELECT, ifIF NOT EXISTS is given and the target table exists, nothing is inserted into the destination table, and the statement is not logged.

To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts duringCREATE TABLE ... SELECT. For more information, seeSection 15.1.1, “Atomic Data Definition Statement Support”.

You cannot useFOR UPDATE as part of theSELECT in a statement such asCREATE TABLEnew_table SELECT ... FROMold_table .... If you attempt to do so, the statement fails.

CREATE TABLE ... SELECT operations applyENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE values to columns only. Table and indexENGINE_ATTRIBUTE andSECONDARY_ENGINE_ATTRIBUTE values are not applied to the new table unless specified explicitly.