126.96.36.199 CREATE TABLE ... SELECT Statement
CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;
MySQL creates new columns for all elements in the
SELECT. For example:
mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a), KEY(b)) -> ENGINE=MyISAM SELECT b,c FROM test2;
This creates a
MyISAM table with three columns,
ENGINE option is part of the
CREATE TABLE statement, and should not be used following the
SELECT; this would result in a syntax error. The same is true for other
CREATE TABLE options such as
Notice that the columns from the
SELECT 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: 0 mysql> SELECT * FROM bar; +------+---+ | m | n | +------+---+ | NULL | 1 | +------+---+ 1 row in set (0.00 sec)
For each row in table
foo, a row is inserted in
bar with the values from
foo and default values for the new columns.
In a table resulting from
CREATE TABLE ... SELECT, columns named only in the
CREATE TABLE part come first. Columns named in both parts or only in the
SELECT part come after that. The data type of
SELECT columns can be overridden by also specifying the column in the
CREATE TABLE part.
If any errors occur while copying the data to the table, it is automatically dropped and not created.
You can precede the
REPLACE to indicate how to handle rows that duplicate unique key values. With
IGNORE, rows that duplicate an existing row on a unique key value are discarded. With
REPLACE, new rows replace rows that have the same unique key value. If neither
REPLACE is specified, duplicate unique key values result in an error. For more information, see Comparison of the IGNORE Keyword and Strict SQL Mode.
Because the ordering of the rows in the underlying
SELECT statements cannot always be determined,
CREATE TABLE ... IGNORE SELECT and
CREATE 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 using
MIXED mode. See also Section 188.8.131.52, “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 the
mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
CREATE TABLE ... SELECT, the destination table does not preserve information about whether columns in the selected-from table are generated columns. The
SELECT part of the statement cannot assign values to generated columns in the destination table.
Some conversion of data types might occur. For example, the
AUTO_INCREMENT attribute is not preserved, and
VARCHAR columns can become
CHAR columns. Retrained attributes are
NOT NULL) and, for those columns that have them,
COMMENT, and the
When creating a table with
CREATE TABLE ... SELECT, make sure to alias any function calls or expressions in the query. If you do not, the
CREATE 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;
CREATE TABLE ... SELECT, if
IF 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 during
CREATE TABLE ... SELECT.
You cannot use
FOR UPDATE as part of the
SELECT in a statement such as
CREATE TABLE . If you attempt to do so, the statement fails.
new_table SELECT ... FROM