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
包含三列的表,a
、b
和
c
。该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
。
如果在将数据复制到表时发生任何错误,它会自动删除而不创建。
您可以在SELECT
by
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
,,,,和
子句。
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 5.5.6 之前,MySQL 对语句的处理如下:
部分中给出的表定义将
CREATE TABLE
被忽略。即使定义与现有表的定义不匹配,也不会发生错误。SELECT
无论如何, MySQL 都会尝试从该部分插入行。如果表中的列数与
SELECT
部件生成的列数不匹配,则所选值将分配给最右边的列。例如,如果表包含n
列并且SELECT
生成m
列,其中m
<n
,则所选值将分配给m
表中最右边的列。每个初始n
-m
columns 被赋予其默认值,或者在列定义中明确指定,或者如果定义不包含默认值,则为隐式列数据类型 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
(withoutIF NOT EXISTS
) 失败并出现错误,但 MySQL 没有插入任何行,也没有将语句写入二进制日志。当目标表存在时,MySQL 现在以相同的方式处理这两个语句,因为这两个语句都不插入行或写入二进制日志。它们之间的区别在于 MySQL 在存在时产生警告,在
IF NOT EXISTS
不存在时产生错误。
此更改意味着,对于前面的示例,从
CREATE
TABLE IF NOT EXISTS ... SELECT
MySQL 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 中
的基于语句的二进制日志记录
发生了变化:
如果目标表不存在,则没有更改:语句按原样记录。
如果目标表确实存在,则该语句将记录为等效的
CREATE TABLE IF NOT EXISTS
andINSERT ... SELECT
语句对。(如果SELECT
原语句中的 the 前面有IGNORE
orREPLACE
,则 the 分别INSERT
变为INSERT IGNORE
orREPLACE
。)
此更改为从 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 EXISTS
and
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 语句的复制”。