You can create one table from another by adding a 通过在SELECT statement at the end of the CREATE TABLE statement:CREATE TABLE语句末尾添加SELECT语句，可以根据一个表创建另一个表：
CREATE TABLEnew_tbl[AS] SELECT * FROMorig_tbl;
MySQL creates new columns for all elements in the MySQL为SELECT. 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 three columns, a, b, and c. a、b和c）的InnoDB表。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. ENGINE选项是CREATE TABLE语句的一部分，不应在SELECT之后使用；这将导致语法错误。The same is true for other 其他CREATE TABLE options such as CHARSET.CREATE TABLE选项（如CHARSET）也是如此。
Notice that the columns from the 请注意，SELECT statement are appended to the right side of the table, not overlapped onto it. SELECT语句中的列被追加到表的右侧，而不是重叠到表的右侧。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.foo中的每一行，将在bar中插入一行，其中包含来自foo的值和新列的默认值。
In a table resulting from 在根据CREATE TABLE ... SELECT, columns named only in the CREATE TABLE part come first. CREATE TABLE ... SELECT产生的表中，仅在CREATE TABLE部分中命名的列优先。Columns named in both parts or only in the 在这两个部分中命名的列或仅在SELECT part come after that. SELECT部分中命名的列位于该部分之后。The data type of 还可以通过在SELECT columns can be overridden by also specifying the column in the CREATE TABLE part.CREATE TABLE部分中指定列来覆盖SELECT列的数据类型。
If errors occur while copying data to the table, the table is automatically dropped and not created. 如果将数据复制到表时出错，则会自动删除该表，而不会创建该表。However, prior to MySQL 8.0.21, when row-based replication is in use, a 然而，在MySQL 8.0.21之前，当使用基于行的复制时，CREATE TABLE ... SELECT statement is recorded in the binary log as two transactions, one to create the table, and the other to insert data. CREATE TABLE ... SELECT语句作为两个事务记录在二进制日志中，一个用于创建表，另一个用于插入数据。When the statement applied from the binary log, a failure between the two transactions or while copying data can result in replication of an empty table. 当从二进制日志应用该语句时，两个事务之间的故障或复制数据时的故障可能会导致复制空表。That limitation is removed in MySQL 8.0.21. MySQL 8.0.21中删除了该限制。On storage engines that support atomic DDL, 在支持原子DDL的存储引擎上，现在在使用基于行的复制时，CREATE TABLE ... SELECT is now recorded and applied as one transaction when row-based replication is in use. CREATE TABLE ... SELECT被记录并作为一个事务应用。For more information, see Section 13.1.1, “Atomic Data Definition Statement Support”.有关更多信息，请参阅第13.1.1节，“原子数据定义语句支持”。
As of MySQL 8.0.21, on storage engines that support both atomic DDL and foreign key constraints, creation of foreign keys is not permitted in 从MySQL 8.0.21开始，在支持原子DDL和外键约束的存储引擎上，在使用基于行的复制时，CREATE TABLE ... SELECT statements when row-based replication is in use. CREATE TABLE ... SELECT语句中不允许创建外键。Foreign key constraints can be added later using 以后可以使用ALTER TABLE.ALTER TABLE添加外键约束。
You can precede the 可以在SELECT by IGNORE or REPLACE to indicate how to handle rows that duplicate unique key values. SELECT之前加IGNORE或REPLACE以指示如何处理重复唯一键值的行。With 使用IGNORE, rows that duplicate an existing row on a unique key value are discarded. IGNORE，将丢弃在唯一键值上与现有行重复的行。With 使用REPLACE, new rows replace rows that have the same unique key value. REPLACE，新行替换具有相同唯一键值的行。If neither 如果未指定IGNORE nor REPLACE is specified, duplicate unique key values result in an error. IGNORE或REPLACE，则重复的唯一键值将导致错误。For more information, see The Effect of IGNORE on Statement Execution.有关更多信息，请参阅IGNORE对语句执行的影响。
In MySQL 8.0.19 and later, you can also use a 在MySQL 8.0.19及更高版本中，还可以在VALUES statement in the SELECT part of CREATE TABLE ... SELECT; the VALUES portion of the statement must include a table alias using an AS clause. CREATE TABLE ... SELECT的SELECT部分使用VALUES语句；语句的VALUES部分必须包含使用AS子句的表别名。To name the columns coming from 要命名来自VALUES, supply column aliases with the table alias; otherwise, the default column names column_0, column_1, column_2, ..., are used.VALUES的列，请提供带有表别名的列别名；否则，将使用默认列名column_0、column_1、column_2……
Otherwise, naming of columns in the table thus created follows the same rules as described previously in this section. 否则，在这样创建的表中，列的命名将遵循本节前面描述的相同规则。Examples:示例：
mysql>CREATE TABLE tv1>SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v;mysql>TABLE tv1;+----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | 3 | 5 | | 2 | 4 | 6 | +----------+----------+----------+ mysql> CREATE TABLE tv2 > SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z); mysql>TABLE tv2;+---+---+---+ | x | y | z | +---+---+---+ | 1 | 3 | 5 | | 2 | 4 | 6 | +---+---+---+ mysql>CREATE TABLE tv3 (a INT, b INT, c INT)>SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);mysql> TABLE tv3; +------+------+------+----------+----------+----------+ | a | b | c | column_0 | column_1 | column_2 | +------+------+------+----------+----------+----------+ | NULL | NULL | NULL | 1 | 3 | 5 | | NULL | NULL | NULL | 2 | 4 | 6 | +------+------+------+----------+----------+----------+ mysql>CREATE TABLE tv4 (a INT, b INT, c INT)>SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);mysql>TABLE tv4;+------+------+------+---+---+---+ | a | b | c | x | y | z | +------+------+------+---+---+---+ | NULL | NULL | NULL | 1 | 3 | 5 | | NULL | NULL | NULL | 2 | 4 | 6 | +------+------+------+---+---+---+ mysql>CREATE TABLE tv5 (a INT, b INT, c INT)>SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(a,b,c);mysql>TABLE tv5;+------+------+------+ | a | b | c | +------+------+------+ | 1 | 3 | 5 | | 2 | 4 | 6 | +------+------+------+
When selecting all columns and using the default column names, you can omit 选择所有列并使用默认列名时，可以省略SELECT *, so the statement just used to create table tv1 can also be written as shown here:SELECT *，因此刚才用于创建表tv1的语句也可以编写为如下所示：
mysql>CREATE TABLE tv1 VALUES ROW(1,3,5), ROW(2,4,6);mysql>TABLE tv1;+----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | 3 | 5 | | 2 | 4 | 6 | +----------+----------+----------+
When using 当使用VALUES as the source of the SELECT, all columns are always selected into the new table, and individual columns cannot be selected as they can be when selecting from a named table; each of the following statements produces an error (ER_OPERAND_COLUMNS):VALUES作为SELECT的源时，所有列始终被选择到新表中，并且不能像从命名表中选择时那样选择单个列；以下每条语句都会产生一个错误（ER_OPERAND_COLUMNS）：
CREATE TABLE tvx
SELECT (x,z) FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
CREATE TABLE tvx (a INT, c INT)
SELECT (x,z) FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
Similarly, you can use a 类似地，您可以使用TABLE statement in place of the SELECT. TABLE语句代替SELECT。This follows the same rules as with 这遵循与VALUES; all columns of the source table and their names in the source table are always inserted into the new table. VALUES相同的规则；源表的所有列及其在源表中的名称始终插入到新表中。Examples:
mysql>TABLE t1;+----+----+ | a | b | +----+----+ | 1 | 2 | | 6 | 7 | | 10 | -4 | | 14 | 6 | +----+----+ mysql>CREATE TABLE tt1 TABLE t1;mysql>TABLE tt1;+----+----+ | a | b | +----+----+ | 1 | 2 | | 6 | 7 | | 10 | -4 | | 14 | 6 | +----+----+ mysql>CREATE TABLE tt2 (x INT) TABLE t1;mysql>TABLE tt2;+------+----+----+ | x | a | b | +------+----+----+ | NULL | 1 | 2 | | NULL | 6 | 7 | | NULL | 10 | -4 | | NULL | 14 | 6 | +------+----+----+
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. SELECT语句中行的顺序，CREATE TABLE ... IGNORE SELECT和CREATE TABLE ... REPLACE SELECT语句对于基于语句的复制被标记为不安全。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. MIXED模式时，此类语句会使用基于行的格式写入二进制日志。See also Section 17.2.1.1, “Advantages and Disadvantages of Statement-Based and Row-Based Replication”.另请参阅第17.2.1.1节，“基于语句和基于行的复制的优缺点”。
CREATE TABLE ... SELECT does not automatically create any indexes for you. CREATE TABLE ... SELECT不会自动为您创建任何索引。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:SELECT语句之前指定这些索引：
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. CREATE TABLE ... SELECT，目标表不会保留有关“从中选择”表中的列是否为生成列的信息。The 语句的SELECT part of the statement cannot assign values to generated columns in the destination table.SELECT部分无法为目标表中生成的列赋值。
For 对于CREATE TABLE ... SELECT, the destination table does preserve expression default values from the original table.CREATE TABLE ... SELECT，目标表将保留原始表中的表达式默认值。
Some conversion of data types might occur. 可能会发生某些数据类型转换。For example, the 例如，AUTO_INCREMENT attribute is not preserved, and VARCHAR columns can become CHAR columns. AUTO_INCREMENT属性不保留，VARCHAR列可以变成CHAR列。Retrained attributes are 重新训练的属性为NULL (or NOT NULL) and, for those columns that have them, CHARACTER SET, COLLATION, COMMENT, and the DEFAULT clause.NULL（或NOT NULL），对于包含这些属性的列，包括CHARACTER SET、COLLATION、COMMENT和DEFAULT子句。
When creating a table with 使用CREATE TABLE ... SELECT, make sure to alias any function calls or expressions in the query. CREATE TABLE ... SELECT创建表时，请确保为查询中的任何函数调用或表达式添加别名。If you do not, the 否则，CREATE statement might fail or result in undesirable column names.CREATE语句可能会失败或导致不需要的列名。
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 target table exists, nothing is inserted into the destination table, and the statement is not logged.CREATE TABLE ... SELECT，如果给定IF NOT EXISTS且目标表存在，则不向目标表中插入任何内容，并且不记录该语句。
To ensure that the binary log can be used to re-create the original tables, MySQL does not permit concurrent inserts during 为了确保二进制日志可以用于重新创建原始表，MySQL不允许在CREATE TABLE ... SELECT. CREATE TABLE ... SELECT期间并发插入。However, prior to MySQL 8.0.21, when a 但是，在MySQL 8.0.21之前，当从二进制日志中应用CREATE TABLE ... SELECT operation is applied from the binary log when row-based replication is in use, concurrent inserts are permitted on the replicated table while copying data. CREATE TABLE ... SELECT操作时，当使用基于行的复制时，复制数据时允许在复制的表上进行并发插入。That limitation is removed in MySQL 8.0.21 on storage engines that support atomic DDL. MySQL 8.0.21在支持原子DDL的存储引擎上消除了这一限制。For more information, see Section 13.1.1, “Atomic Data Definition Statement Support”.有关更多信息，请参阅第13.1.1节，“原子数据定义语句支持”。
You cannot use 在诸如FOR UPDATE as part of the SELECT in a statement such as CREATE TABLE . new_table SELECT ... FROM old_table ...CREATE TABLE 这样的语句中，你不能将new_table SELECT ... FROM old_table ...FOR UPDATE用作SELECT的一部分。If you attempt to do so, the statement fails.如果尝试执行此操作，则语句将失败。
CREATE TABLE ... SELECT operations apply ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE values to columns only. CREATE TABLE ... SELECT操作只将ENGINE_ATTRIBUTE值和SECONDARY_ENGINE_ATTRIBUTE值应用于列。Table and index 表和索引ENGINE_ATTRIBUTE and SECONDARY_ENGINE_ATTRIBUTE values are not applied to the new table unless specified explicitly.ENGINE_ATTRIBUTE值和SECONDARY_ENGINE_ATTRIBUTE值没有应用于新表，除非显式指定。