MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
You can create one table from another by adding a
SELECT
statement at the end of
the CREATE TABLE
statement:
CREATE TABLEnew_tbl
[AS] SELECT * FROMorig_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, a
, b
, and
c
. The 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
CHARSET
.
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 SELECT
by
IGNORE
or 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 IGNORE
nor
REPLACE
is specified, duplicate unique key
values result in an error.
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 17.1.2.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 the
SELECT
statement:
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,
and VARCHAR
columns can become
CHAR
columns. Retrained
attributes are NULL
(or NOT
NULL
) and, for those columns that have them,
CHARACTER SET
, COLLATION
,
COMMENT
, and the DEFAULT
clause.
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;
For CREATE TABLE
... 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 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
from the 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 n
columns
and the SELECT
produces
m
columns, where
m
<
n
, the selected values are
assigned to the m
rightmost
columns in the table. Each of the initial
n
−
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
columns (m
>
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 IF NOT
EXISTS
handling:
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
CREATE
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
5.1.51.
Previously, for
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
EXISTS
is present and an error when it is not.
This change means that, for the preceding example, the
CREATE
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
CREATE
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
CREATE
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 EXISTS
and
INSERT ...
SELECT
statements. (If the
SELECT
in the original
statement is preceded by IGNORE
or
REPLACE
, the
INSERT
becomes
INSERT
IGNORE
or REPLACE
,
respectively.)
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
CREATE
TABLE IF NOT EXISTS
and
INSERT ...
SELECT
statements rather than
CREATE
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
CREATE
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 ...
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. This represents a
change in behavior from MySQL 5.5 and earlier, which permitted
new_table
SELECT ... FROM
old_table
...CREATE
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 17.4.1.7, “Replication of CREATE TABLE ... SELECT Statements”, for more information.