Documentation Home
MySQL 8.0 参考手册  / 第 13 章 SQL 语句  / 13.1 数据定义语句  / 13.1.20 CREATE TABLE 语句  /  13.1.17.4 CREATE TABLE ... SELECT 语句

13.1.17.4 CREATE TABLE ... SELECT 语句

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包含三列的表,abc。该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

如果在将数据复制到表时发生任何错误,它会自动删除而不创建。

您可以在SELECTby IGNORE或之前REPLACE指示如何处理重复唯一键值的行。使用IGNORE,与唯一键值上的现有行重复的行将被丢弃。使用 REPLACE,新行替换具有相同唯一键值的行。如果既未指定IGNORE也未 REPLACE指定,则重复的唯一键值会导致错误。

SELECT因为无法始终确定 底层语句中行的顺序 ,CREATE TABLE ... IGNORE SELECT 并且CREATE TABLE ... REPLACE SELECT 语句被标记为对基于语句的复制不安全。使用基于语句的模式时,此类语句会在错误日志中产生警告,并在使用模式时使用基于行的格式写入二进制日志 MIXED。另见 第 17.1.2.1 节,“基于语句和基于行的复制的优点和缺点”

CREATE TABLE ... SELECT不会自动为您创建任何索引。这样做是为了使语句尽可能灵活。如果你想在创建的表中有索引,你应该在 SELECT语句之前指定这些:

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

可能会发生某些数据类型转换。例如, AUTO_INCREMENT不保留属性,VARCHAR列可以成为 CHAR列。重新训练的属性是NULL(或)并且, NOT NULL对于那些具有它们的列 CHARACTER SET,,,,和 子句。 COLLATIONCOMMENTDEFAULT

使用 创建表时 CREATE TABLE ... SELECT,请确保为查询中的任何函数调用或表达式设置别名。如果不这样做,该 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;

您还可以在创建的表中显式指定列的数据类型:

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

对于CREATE TABLE ... SELECT,如果IF NOT EXISTS给定并且目标表已经存在,则结果取决于版本。在 MySQL 5.5.6 之前,MySQL 对语句的处理如下:

  • 部分中给出的表定义将 CREATE TABLE被忽略。即使定义与现有表的定义不匹配,也不会发生错误。SELECT无论如何, MySQL 都会尝试从该部分插入行。

  • 如果表中的列数与 SELECT部件生成的列数不匹配,则所选值将分配给最右边的列。例如,如果表包含n列并且SELECT生成 m列,其中 m< n,则所选值将分配给m表中最右边的列。每个初始 n- mcolumns 被赋予其默认值,或者在列定义中明确指定,或者如果定义不包含默认值,则为隐式列数据类型 default。如果 SELECT部件产生太多列 ( m> n),则会发生错误。

  • 如果启用了严格的 SQL 模式并且这些初始列中的任何一个都没有明确的默认值,则该语句将失败并出现错误。

以下示例说明了IF NOT EXISTS处理:

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)

从 MySQL 5.5.6 开始, CREATE TABLE IF NOT EXISTS ... SELECT对于目标表已经存在的情况,语句的处理发生了变化。此更改还涉及从 5.1.51 开始的 MySQL 5.1 中的更改。

  • 以前,对于 CREATE TABLE IF NOT EXISTS ... SELECT,MySQL 产生了该表存在的警告,但仍然插入了行并将语句写入二进制日志。相比之下, CREATE TABLE ... SELECT(without IF NOT EXISTS) 失败并出现错误,但 MySQL 没有插入任何行,也没有将语句写入二进制日志。

  • 当目标表存在时,MySQL 现在以相同的方式处理这两个语句,因为这两个语句都不插入行或写入二进制日志。它们之间的区别在于 MySQL 在存在时产生警告,在IF NOT EXISTS不存在时产生错误。

此更改意味着,对于前面的示例,从 CREATE TABLE IF NOT EXISTS ... SELECTMySQL 5.5.6 开始,该语句不会向目标表中插入任何内容。

这种处理方式的变化IF NOT EXISTS 导致基于语句的复制从具有原始行为的 MySQL 5.1 源和具有新行为的 MySQL 5.5 副本不兼容。假设 CREATE TABLE IF NOT EXISTS ... SELECT在源表上执行并且存在目标表。结果是行插入到源而不是副本。(基于行的复制没有这个问题。)

CREATE TABLE IF NOT EXISTS ... SELECT为了解决这个问题,从 5.1.51 开始,MySQL 5.1 中 的基于语句的二进制日志记录 发生了变化:

此更改为从 MySQL 5.1 到 5.5 的基于语句的复制提供了向前兼容性,因为当目标表存在时,行将同时插入到源表和副本上。要利用此兼容性措施,5.1 服务器必须至少为 5.1.51,而 5.5 服务器必须至少为 5.5.6。

要升级现有的 5.1 到 5.5 复制方案,请先将源升级到 5.1.51 或更高版本。请注意,这与通常先升级副本的复制升级建议不同。

对于希望实现原始效果(无论目标表是否存在都插入行)的应用程序,一种变通方法是使用 CREATE TABLE IF NOT EXISTSand INSERT ... SELECT语句而不是 CREATE TABLE IF NOT EXISTS ... SELECT语句。

除了刚刚描述的更改外,还进行了以下相关更改:以前,如果将现有视图命名为 的目标表,则会将 CREATE TABLE IF NOT EXISTS ... SELECT行插入基础基表并将语句写入二进制日志。从 MySQL 5.1.51 和 5.5.6 开始,没有插入或记录任何内容。

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

重要的

您不能将FOR UPDATE用作语句的一部分, SELECT例如 . 如果您尝试这样做,该语句将失败。这代表了 MySQL 5.5 及更早版本的行为变化,它允许 语句在表中进行更改,而不是正在创建的表。 CREATE TABLE new_table SELECT ... FROM old_table ...CREATE TABLE ... SELECT

此更改还可能对从旧源到 MySQL 5.6 或更高版本的基于语句的复制产生影响。有关详细信息,请参阅 第 17.4.1.7 节,“CREATE TABLE ... SELECT 语句的复制”