SELECT您可以通过在语句末尾 添加语句来从另一个表创建一个表 CREATE TABLE：

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

MySQL 为 SELECT. 例如：

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        ENGINE=InnoDB SELECT b,c FROM test2;

这将创建一个InnoDB包含三列的表，a、b和 c。该ENGINE选项是CREATE TABLE 语句的一部分，不应在 SELECT;之后使用。这会导致语法错误。其他 CREATE TABLE选项也是如此，例如 CHARSET.

请注意， SELECT语句中的列附加到表的右侧，而不是重叠在上面。举个例子：

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)

对于 tablefoo中的每一行，都会插入一行，其中包含新列bar的值 foo和默认值。

在由 生成的表中 CREATE TABLE ... SELECT，仅在 CREATE TABLE部分中命名的列排在第一位。在这两个部分中命名的列或仅在该 SELECT部分中命名的列在其后。SELECT还可以通过在部件中指定列来覆盖列 的数据类型CREATE TABLE。

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. For more information, see The Effect of IGNORE on Statement Execution.

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 16.2.1.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;

For 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 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;

对于CREATE TABLE ... SELECT，如果IF NOT EXISTS给出并且目标表存在，则不会向目标表中插入任何内容，并且不会记录该语句。

为确保二进制日志可用于重新创建原始表，MySQL 不允许在 CREATE TABLE ... SELECT.

您不能将FOR UPDATE用作语句的一部分， SELECT例如 . 如果您尝试这样做，该语句将失败。 CREATE TABLE new_table SELECT ... FROM old_table ...