MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (6)

数据库杂货铺 2021-04-12

372

CREATE TABLE ... SELECT 语句

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

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=MyISAM SELECT b,c FROM test2;

这将创建一个包含a、b和c三列的 MyISAM 表。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)

表 foo 中的每一行都将插入到 bar 中，其中包含来自 foo 的值和新列的默认值。

在由 CREATE TABLE ... SELECT 语句生成的表中，仅来自 CREATE TABLE 语句生成的列排在前面。在这两个部分中命名的列或仅在 SELECT 中命名的列排在之后。也可以通过在 CREATE TABLE 语句中指定列来覆盖 SELECT 语句中列的数据类型。

如果在将数据复制到表时发生错误，则会自动删除表而不创建表。但是，在 MySQL 8.0.21 之前，当使用基于行的复制时，CREATE TABLE ... SELECT 语句将被作为两个事务记录在二进制日志中，一个用于创建表，另一个用于插入数据。当从二进制日志应用语句时，两个事务之间有错误发生，或在复制数据时出现错误，可能导致复制一个空表。这个限制在MySQL 8.0.21中被移除。在支持原子DDL的存储引擎上，当使用基于行的复制时，CREATE TABLE ... SELECT 将被记录并作为一个事务应用。

从MySQL 8.0.21开始，在支持原子DDL和外键约束的存储引擎上，在 CREATE TABLE ... SELECT 语句中进行基于行的复制时不允许创建外键。以后可以使用 ALTER TABLE 添加外键约束。

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

在 MySQL 8.0.19 及以后版本中，你还可以在 CREATE TABLE ... SELECT 语句的 SELECT 部分使用 VALUES 语句;语句的 VALUES 部分必须包含一个使用 AS 子句的表别名。若要命名来自 VALUES 的列，请使用表别名提供列别名;不然默认使用列名 column_0、column_1、column_2 ......。

否则，在这样创建的表中对列的命名遵循与本节前面描述的相同的规则。例子:

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 |
+------+------+------+

在选择所有列并使用默认列名时，可以省略 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 |
+----------+----------+----------+

当使用 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);

类似地，您可以使用 TABLE 语句代替 SELECT 语句。这遵循与 VALUES 语句相同的规则;源表的所有列及其在源表中的名称总是插入到新表中。示例:

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 |
+------+----+----+

因为不总是能确定 SELECT 语句中底层的行排序顺序，所以 CREATE TABLE ... IGNORE SELECT 和 CREATE TABLE ... REPLACE SELECT 被标记为不安全的基于语句的复制，当使用基于语句的模式时，这些语句会在错误日志中产生一个警告，当使用 MIXED 模式时，这些语句会使用基于行的格式写入二进制日志。

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

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

对于 CREATE TABLE ... SELECT 语句，目标表不保留关于选择列是否为生成列的信息。语句的 SELECT 部分无法为目标表中的生成列分配值。

对于 CREATE TABLE ... SELECT 语句，目标表将保留原始表中的表达式默认值。

可能会发生某些数据类型的转换。例如，AUTO_INCREMENT 属性不被保留，VARCHAR 列可以变成 CHAR 列。保留的属性为NULL(或 NOT NULL)，以及具有这些属性的列，CHARACTER SET、COLLATION、COMMENT 和 DEFAULT 子句。

当用 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 在 CREATE TABLE ... SELECT 语句执行时不允许并发插入。但是，在 MySQL8.0.21 之前，当使用基于行的复制时，如果执行源自二进制日志的 CREATE TABLE ... SELECT 语句，复制数据时允许对复制的表进行并发插入。MySQL 8.0.21 在支持原子 DDL 的存储引擎上取消了这一限制。

不能在诸如 CREATE TABLE new_table SELECT ... FROM old_table .... 此类的语句中使用 FOR UPDATE 作为 SELECT 的一部分，如果尝试这样做，语句将失败。

CREATE TABLE ... SELECT 操作只对列应用 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值。除非显式指定，否则表和索引 ENGINE_ATTRIBUTE 和 SECONDARY_ENGINE_ATTRIBUTE 值不会应用于新表。

官方地址：

https://dev.mysql.com/doc/refman/8.0/en/create-table-select.html

mysql

文章转载自数据库杂货铺，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的SQL语句 - 数据定义语句（14）- CREATE TABLE 语句 (6)

评论