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=InnoDB SELECT b,c FROM test2;
This creates an
InnoDB table with
ENGINE option is
part of the
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
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
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
indicate how to handle rows that duplicate unique key values.
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.
Because the ordering of the rows in the underlying
SELECT statements cannot always
CREATE TABLE ... IGNORE SELECT
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 220.127.116.11, “Advantages and Disadvantages of Statement-Based and Row-Based
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;
Some conversion of data types might occur. For example, the
AUTO_INCREMENT attribute is not preserved,
VARCHAR columns can become
CHAR columns. Retrained
NULL) and, for those columns that have them,
COMMENT, and the
When creating a table with
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;
... SELECT, if
IF NOT EXISTS is
given and the destination table already exists, the result is
version dependent. Before MySQL 5.5.6, MySQL handles the
statement as follows:
The table definition given in the
CREATE TABLEpart is ignored. No error occurs, even if the definition does not match that of the existing table. MySQL attempts to insert the rows from the
If there is a mismatch between the number of columns in the table and the number of columns produced by the
SELECTpart, the selected values are assigned to the rightmost columns. For example, if the table contains
ncolumns and the
n, the selected values are assigned to the
mrightmost columns in the table. Each of the initial
mcolumns is assigned its default value, either that specified explicitly in the column definition or the implicit column data type default if the definition contains no default. If the
SELECTpart produces too many columns (
n), an error occurs.
If strict SQL mode is enabled and any of these initial columns do not have an explicit default value, the statement fails with an error.
The following example illustrates
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)
As of MySQL 5.5.6, handling of
TABLE IF NOT EXISTS ... SELECT statements was changed
for the case that the destination table already exists. This
change also involves a change in MySQL 5.1 beginning with
CREATE TABLE IF NOT EXISTS ... SELECT, MySQL produced a warning that the table exists, but inserted the rows and wrote the statement to the binary log anyway. By contrast,
CREATE TABLE ... SELECT(without
IF NOT EXISTS) failed with an error, but MySQL inserted no rows and did not write the statement to the binary log.
MySQL now handles both statements the same way when the destination table exists, in that neither statement inserts rows or is written to the binary log. The difference between them is that MySQL produces a warning when
IF NOT EXISTSis present and an error when it is not.
This change means that, for the preceding example, the
TABLE IF NOT EXISTS ... SELECT statement inserts
nothing into the destination table as of MySQL 5.5.6.
This change in handling of
IF NOT EXISTS
results in an incompatibility for statement-based replication
from a MySQL 5.1 source with the original behavior and a MySQL
5.5 replica with the new behavior. Suppose that
TABLE IF NOT EXISTS ... SELECT is executed on the
source and the destination table exists. The result is that rows
are inserted on the source but not on the replica. (Row-based
replication does not have this problem.)
To address this issue, statement-based binary logging for
TABLE IF NOT EXISTS ... SELECT is changed in MySQL 5.1
as of 5.1.51:
If the destination table does not exist, there is no change: The statement is logged as is.
If the destination table does exist, the statement is logged as the equivalent pair of
CREATE TABLE IF NOT EXISTSand
INSERT ... SELECTstatements. (If the
SELECTin the original statement is preceded by
This change provides forward compatibility for statement-based replication from MySQL 5.1 to 5.5 because when the destination table exists, the rows are inserted on both the source and replica. To take advantage of this compatibility measure, the 5.1 server must be at least 5.1.51 and the 5.5 server must be at least 5.5.6.
To upgrade an existing 5.1-to-5.5 replication scenario, upgrade the source first to 5.1.51 or higher. Note that this differs from the usual replication upgrade advice of upgrading the replica first.
A workaround for applications that wish to achieve the original
effect (rows inserted regardless of whether the destination
table exists) is to use
TABLE IF NOT EXISTS and
SELECT statements rather than
TABLE IF NOT EXISTS ... SELECT statements.
Along with the change just described, the following related
change was made: Previously, if an existing view was named as
the destination table for
TABLE IF NOT EXISTS ... SELECT, rows were inserted
into the underlying base table and the statement was written to
the binary log. As of MySQL 5.1.51 and 5.5.6, nothing is
inserted or 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 ...
You cannot use
FOR UPDATE as part of the
SELECT in a statement such as
TABLE . If you
attempt to do so, the statement fails. This represents a
change in behavior from MySQL 5.5 and earlier, which permitted
new_table SELECT ... FROM
TABLE ... SELECT statements to make changes in
tables other than the table being created.
This change can also have implications for statement-based replication from an older source to a MySQL 5.6 or higher replica. See Section 18.104.22.168, “Replication of CREATE TABLE ... SELECT Statements”, for more information.