`CREATE TABLE ... SELECT` Syntax

You can create one table from another by adding a SELECT statement at the end of the CREATE TABLE statement:

CREATE TABLE new_tbl 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, 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, 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 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 in MariaDB 5.6.4 and later are flagged as unsafe for statement-based replication. With this change, such statements produce a warning in the log when using statement-based mode and are logged using the row-based format when using MIXED mode. See also , "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 generated column:

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 MariaDB 5.5.6, MariaDB 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. MariaDB 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 MariaDB 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 MariaDB 5.1 beginning with 5.1.51.

Previously, for CREATE TABLE IF NOT EXISTS ... SELECT, MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 5.5.6.

This change in handling of IF NOT EXISTS results in an incompatibility for statement-based replication from a MariaDB 5.1 master with the original behavior and a MariaDB 5.5 slave with the new behavior. Suppose that CREATE TABLE IF NOT EXISTS ... SELECT is executed on the master and the destination table exists. The result is that rows are inserted on the master but not on the slave. (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 MariaDB 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 MariaDB 5.1 to 5.5 because when the destination table exists, the rows will be inserted on both the master and slave. 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 master first to 5.1.51 or higher. Note that this differs from the usual replication upgrade advice of upgrading the slave 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 MariaDB 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, MariaDB does not permit concurrent inserts during CREATE TABLE ... SELECT.Important

You cannot use FOR UPDATE as part of the SELECT in a statement such as CREATE TABLE new_table SELECT ... FROM old_table .... If you attempt to do so, the statement fails. This represents a change in behavior from MariaDB 5.5 and earlier, which permitted 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 master to a MariaDB 5.6 or newer slave. See , "Replication of CREATE TABLE ... SELECT Statements", for more information.

CREATE TABLE ... SELECT Syntax

Retornar

`CREATE TABLE ... SELECT` Syntax