13.1.18.4 CREATE TABLE ... SELECT 语句
您可以通过在CREATE TABLE语句的末尾添加SELECT语句来创建另一个 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=MyISAM SELECT b,c FROM test2;
这将创建一个具有a
,b
和c
三列的MyISAMtable。 ENGINE
选项是CREATE TABLE语句的一部分,不应在SELECT之后使用;这将导致语法错误。其他CREATE TABLE选项(例如CHARSET
)也是如此。
请注意,SELECT语句中的列被追加到 table 的右侧,而不是重叠在 table 的右侧。请看以下示例:
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
中的值以及新列的默认值。
在由创建 table...选择生成的 table 中,仅在CREATE TABLE部分中命名的列位于第一位。在这两个部分或仅在SELECT部分中命名的列之后。 SELECT列的数据类型也可以通过在CREATE TABLE部分中指定列来覆盖。
如果将数据复制到 table 时发生任何错误,则会自动将其删除而不创建。
您可以在SELECT之前加IGNORE
或REPLACE
来指示如何处理重复唯一键值的行。使用IGNORE
,将删除在唯一键值上复制现有行的行。使用REPLACE
,新行将替换具有相同唯一键值的行。如果既未指定IGNORE
也未指定REPLACE
,则重复的唯一键值将导致错误。有关更多信息,请参见IGNORE 关键字和严格 SQL 模式的比较。
由于不能始终确定基础SELECT语句中行的 Sequences,因此CREATE TABLE ... IGNORE SELECT
和CREATE TABLE ... REPLACE SELECT
语句被标记为对基于语句的复制不安全。当使用基于语句的模式时,此类语句在错误日志中产生警告,而在使用MIXED
模式时,将使用基于行的格式将此类语句写入二进制日志。另请参见第 16.2.1.1 节,“基于语句的复制和基于行的复制的优缺点”。
创建 table...选择不会自动为您创建任何索引。这样做是为了使语句尽可能灵活。如果要在创建的 table 中包含索引,则应在SELECT语句之前指定这些索引:
mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
对于CREATE TABLE ... SELECT
,目标 table 不会保留有关 selected-fromtable 中的列是否是生成的列的信息。语句的SELECT部分不能将值分配给目标 table 中的生成列。
可能会发生一些数据类型的转换。例如,不保留AUTO_INCREMENT
属性,并且VARCHAR列可以成为CHAR列。重新训练的属性为NULL
(或NOT NULL
),对于具有它们的那些列,为CHARACTER SET
,COLLATION
,COMMENT
和DEFAULT
子句。
使用创建 table...选择创建 table 时,请确保对查询中的所有函数调用或 table 达式都使用别名。否则,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;
您还可以为创建的 table 中的列显式指定数据类型:
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
对于创建 table...选择,如果给出IF NOT EXISTS
并且目标 table 存在,则什么都不会插入到目标 table 中,并且不会记录该语句。
为了确保二进制日志可用于重新创建原始 table,MySQL 不允许在创建 table...选择期间并发插入。
您不能在创建 tablenew_table SELECT ... FROM old_table ...之类的语句中将FOR UPDATE
用作SELECT的一部分。如果尝试这样做,该语句将失败。