关于CREATE TABLE IF NOT EXIST … SELECT

justlooks

对于CREATE TABLE  IF NOT EXIST… SELECT ,如果给定IF NOT EXISTS ，而且表已经存在的情况下
做下列事
CREATE TABLE 的表定义部分被忽略，甚至表定义与目前存在的表不一致，也不会抛出错误
如果存在的表的列数和由SELECT部分的列数不匹配的话 SELECT到的值会被靠右填充到表列中
如：
SELECT产生m列，而表定义有n列，当（mn时，会有错误产生

mysql> create table t1 (a int,b int);
mysql> insert int t1 values (1,1),(1,2);
mysql> drop table t4;
mysql> create table t4 (a int,b int,c int,d int);
mysql> insert into t4 values (1,1,1,1);
mysql> create table IF NOT EXISTS t4 (a int) SELECT * FROM t1;
uery OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 1
mysql> show warnings;
+——-+——+—————————+
| Level | Code | Message |
+——-+——+—————————+
| Note | 1050 | Table ‘t4′ already exists |
+——-+——+—————————+
mysql> select * from t4
-> ;
+——+——+——+——+
| a | b | c | d |
+——+——+——+——+
| 1 | 1 | 1 | 1 |
| NULL | NULL | 1 | 1 |
| NULL | NULL | 1 | 2 |
+——+——+——+——+

mysql> drop table t4;
mysql> create table t4 (a int);
mysql> insert into t4 values(111);
mysql> select * from t4;
+——+
| a |
+——+
| 111 |
+——+
mysql> create table IF NOT EXISTS t4 (a int,b int,c int) SELECT * FROM t1;
ERROR 1136 (21S01): Column count doesn’t match value count at row 1

cenalulu

mysql官方给出的解释是：
CREATE TABLE  IF NOT EXIST… SELECT的行为，先判断表是否存在，
如果存在，语句就相当于执行insert into select
如果不存在，则相当于create table ... select

create table ... select 的行为又如下：
For CREATE TABLE … SELECT, if IF NOT EXISTS is given and the table already exists, MySQL handles the statement as follows:
* The table definition given in the CREATE TABLE part is ignored. No error occurs, even if the definition does not match that of the existing table.
* If there is a mismatch between the number of columns in the table and the number of columns produced by the SELECT part, the selected values are assigned to the rightmost columns. For example, if the table contains n columns and the SELECT produces m columns, where m < n, the selected values are assigned to the m rightmost columns in the table. Each of the initial n – m columns is assigned its default value, either that specified explicitly in the column definition or the implicit column data type default if the definition contains no default. If the SELECT part produces too many columns (m > n), an error occurs.
* If strict SQL mode is enabled and any of these initial columns do not have an explicit default value, the statement fails with an error.