CREATE TABLE ... SELECT语句
您可以通过在SELECT
语句末尾添加一条语句来从另一个表创建一个表CREATE TABLE
:
CREATE TABLE new_tbl [AS ]SELECT *FROM orig_tbl;
MySQL为中的所有元素创建新列SELECT
。例如:
mysql>CREATE TABLE test (a INT NOT NULLAUTO_INCREMENT , ->PRIMARY KEY (a),KEY (b)) ->ENGINE =MyISAMSELECT b,cFROM test2;
这将创建一个MyISAM
表有三列,a
,b
,和c
。该ENGINE
选项是CREATE TABLE
语句的一部分,不应在SELECT
;之后使用。这将导致语法错误。其他CREATE TABLE
选项(例如)也是如此CHARSET
。
请注意,SELECT
语句中的列被追加到表的右侧,而不是重叠在表的右侧。请看以下示例:
mysql>SELECT *FROM foo; +--- + | n | +--- + | 1 | +--- + mysql>CREATE TABLE bar (m INT)SELECT nFROM 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
部分中命名的列之后。SELECT
也可以通过在CREATE TABLE
零件中指定列来覆盖列的数据类型。
如果将数据复制到表时发生任何错误,则会自动将其删除而不创建。
您可以先于SELECT
通过IGNORE
或REPLACE
指示如何处理重复的唯一键值的行。使用IGNORE
,将删除在唯一键值上复制现有行的行。使用REPLACE
,新行将替换具有相同唯一键值的行。如果既未指定也IGNORE
未REPLACE
指定,则重复的唯一键值将导致错误。有关更多信息,请参见 IGNORE关键字和严格SQL模式的比较。
在MySQL 8.0.19及更高版本中,您还可以VALUES
在;SELECT
部分中使用语句CREATE TABLE ... SELECT
。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 | +------ +------ +------ +
当VALUES
用作的来源时SELECT
,总是将所有列都选择到新表中,并且不能像从命名表中选择时那样选择单个列。以下每个语句都会产生错误(ER_OPERAND_COLUMNS
):
CREATE TABLE tvxSELECT (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 tt1TABLE 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 nFROM foo;
对于CREATE TABLE ... SELECT
,目标表不会保留有关selected-from表中的列是否是生成的列的信息。SELECT
语句的一部分不能为目标表中的生成列分配值。
对于CREATE TABLE ... SELECT
,目标表的确保留原始表中的表达式默认值。
可能会发生一些数据类型的转换。例如,AUTO_INCREMENT
属性未保留,VARCHAR
列可以成为CHAR
列。再培训属性是NULL
(或NOT NULL
),对于拥有它们,这些列CHARACTER SET
,COLLATION
,COMMENT
,和DEFAULT
条款。
使用创建表时CREATE TABLE ... SELECT
,请确保在查询中为所有函数调用或表达式加上别名。否则,该CREATE
语句可能会失败或导致出现不希望的列名。
CREATE TABLE artists_and_worksSELECT artist.name , COUNT(work .artist_id)AS number_of_worksFROM artistLEFT JOIN work ON artist.id =work .artist_idGROUP BY artist.id;
您还可以为创建的表中的列显式指定数据类型:
CREATE TABLE foo (a TINYINT NOT NULL)SELECT b+1AS aFROM bar;
对于CREATE TABLE ... SELECT
,如果IF NOT EXISTS
给出并且目标表存在,则什么都不会插入到目标表中,并且不会记录该语句。
为了确保二进制日志可用于重新创建原始表,MySQL不允许在期间进行并发插入CREATE TABLE ... SELECT
。
您不能将FOR UPDATE
用作SELECT
语句的一部分,例如。如果尝试这样做,该语句将失败。CREATE TABLE new_table SELECT ... FROM old_table...