new_tbl[AS] SELECT * FROM
MySQL creates new columns for all elements in the
SELECT. For example:
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
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
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
REPLACE to indicate how to handle
rows that duplicate unique key values. With
IGNORE, new 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.
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
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 generated column:
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, MySQL handles
the statement as follows:
The table definition given in the
CREATE TABLE part is ignored.
No error occurs, even if the definition does not match that
of the existing table. MySQL attempts to insert the rows
SELECT part anyway.
If there is a mismatch between the number of columns in the
table and the number of columns produced by the
SELECT part, the selected
values are assigned to the rightmost columns. For example,
if the table contains
m columns, where
n, the selected values are
assigned to the
columns in the table. Each of the initial
m columns 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
SELECT part produces too many
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
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)
To ensure that the binary log can be used to re-create the
original tables, MySQL does not permit concurrent inserts during
CREATE TABLE ...