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.

If any errors occur while copying the data to the table, it is automatically dropped and not created.

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.

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 16.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.

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.

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.