mysql 5.7 insert_MySQL-5.7 Insert语句详解

1.语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

[PARTITION (partition_name [, partition_name] ...)]

[(col_name [, col_name] ...)]

{VALUES | VALUE} (value_list) [, (value_list)] ...

[ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

[PARTITION (partition_name [, partition_name] ...)]

SET assignment_list

[ON DUPLICATE KEY UPDATE assignment_list]

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]

[INTO] tbl_name

[PARTITION (partition_name [, partition_name] ...)]

[(col_name [, col_name] ...)]

SELECT ...

[ON DUPLICATE KEY UPDATE assignment_list]

value:

{expr | DEFAULT}

value_list:

value [, value] ...

assignment:

col_name = value

assignment_list:

assignment [, assignment] ...

2.三种常用语法示例

mysql> desc students;

+---------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------+-------------+------+-----+---------+----------------+

| sid | int(11) | NO | PRI | NULL | auto_increment |

| sname | varchar(54) | YES | | NULL | |

| gender | varchar(12) | YES | | NULL | |

| dept_id | int(11) | NO | MUL | NULL | |

+---------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

mysql> select * from students;

+-----+-------+--------+---------+

| sid | sname | gender | dept_id |

+-----+-------+--------+---------+

| 1 | aaa | 1 | 2 |

| 2 | bbb | 2 | 2 |

| 3 | ccc | 3 | 2 |

+-----+-------+--------+---------+

3 rows in set (0.00 sec)

mysql> insert into students values(4,'aaaa',1,1);

Query OK, 1 row affected (0.04 sec)

mysql> insert into students set sid=5,sname='ddd',gender=2,dept_id=1;

Query OK, 1 row affected (0.03 sec)

mysql> insert into students select * from students_bak;

说明：

insert…values和insert…set两种语句都是将指定的数据插入到现成的表中，而insert….select语句是将另外表中的数据查出来并插入到现成的表中；

3.属性说明

1)Partition

代表可以将数据插入到指定的表分区中

2)Tbl_name

代表将数据插入到目标表

3)Col_name

代表要插入指定数据的目标列，如果是多列则用逗号隔开，如果目标表中的某些列没有在Insert语句中指定，则这些列会插入默认值，当然可以使用default显示指定插入默认值

4)LOW_PRIORITY

INSERT的执行被推迟到没有其他用户正在读取表。在这种情况下，用户必须等到插入语句完成后，如果表频繁使用，它可能花很长时间。这与INSERT DELAYED让用户马上继续正好相反。

LOW_PRIORITY和HIGH_PRIORITY仅在MyISAM，MEMORY和MERGE三种存储引擎下才生效。

5)IGNORE

表中任何复制一个现有PRIMARY或UNIQUE键的行被忽略并且不被插入。如果你不指定IGNORE，插入如果有任何复制现有关键值的行被放弃。

当违反主键和唯一键约束条件时不报错只产生警告信息，违反的行被丢弃，而不是整个语句回退；在数据类型转换有问题时也是如此。

6)DELAYED(5.7版本不再支持，语句不会报错，但会产生警告)

如果用户不能等到INSERT完成，它是很有用的。当你为日志登录使用MySQL时，而且你也周期性地运行花很长时间完成的SELECT语句，这是一个常见的问题。

当你使用INSERT DELAYED时，用户将马上准备好，并且当表不被任何其他的线程使用时，行将被插入。

另一个使用INSERT DELAYED的主要好处是从很多用户插入被捆绑在一起并且写进一个块。这比做很多单独的插入要来的快。

使用DELAYED时有一些限制：

INSERT DELAYED仅适用于MyISAM, MEMORY和ARCHIVE表。对于MyISAM表，如果在数据文件的中间没有空闲的块，则支持同时采用SELECT和INSERT语句。在这些情况下，基本不需要对MyISAM使用INSERT DELAYED。

INSERT DELAYED应该仅用于指定值清单的INSERT语句。服务器忽略用于INSERT DELAYED…SELECT语句的DELAYED和INSERT DELAYED…ON DUPLICATE UPDATE语句的DELAYED。

因为在行被插入前，语句立刻返回，所以您不能使用LAST_INSERT_ID()来获取AUTO_INCREMENT值。AUTO_INCREMENT值可能由语句生成。

对于SELECT语句，DELAYED行不可见，直到这些行确实被插入了为止。

DELAYED在从属复制服务器中被忽略了，因为DELAYED不会在从属服务器中产生与主服务器不一样的数据。

7)ON DUPLICATE KEY UPDATE

如果您指定了ON DUPLICATE KEY UPDATE，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。

当您使用ON DUPLICATE KEY UPDATE时，DELAYED选项被忽略。

--假设a,b为唯一索引,表table没有1,2这样的行是正常插入数据，冲突时，更新c列的值

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;

--或者是

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=values(c);

--引用其他列更新冲突的行

INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

向一个已定义为NOT NULL的列中插入NULL。对于一个多行INSERT语句或INSERT INTO…SELECT语句，根据列数据的类型，列被设置为隐含的默认值。对于数字类型，默认值为0；对于字符串类型，默认值为空字符串(”)；对于日期和时间类型，默认值为“zero”值。

INSERT INTO…SELECT的ON DUPLICATE KEY UPDATE

insert into tbl_name1(a,b,c)

select col1,col2,col3 from tbl_name2

ON DUPLICATE KEY UPDATE c=values(c);

4.注意事项

1)Values中除了可以指定确定的数值之外，还可以使用表达式expr

INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); 正确

INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15); 错误

2)执行结果

Insert…values和Insert…select语句执行结果如下：

Records:100 Duplicates:0 Warnings:0

Records代表语句操作了多少行数据，但不一定是多少行被插入的数据，因为如果存在相同行数据且违反了某个唯一性，Duplicates会显示非0数值；

warnings代表语句执行过程中的一些警告信息；

Warnings的一些触发情况：

插入NULL到被声明了NOT NULL的列，列被设置为它的缺省值。

将超出列范围的值设置给一个数字列，值被剪切为范围内适当的端点值。

将数字列设成例如’10.34 a’的值，拖尾的垃圾被剥去并仍然是数字部分被插入。如果值根本不是一个数字，列被设置到0。

把一个字符串插入到超过列的最大长度的一个CHAR、VARCHAR、TEXT或BLOB列中。值被截断为列的最大长度。

把一个对列类型不合法的值插入到一个日期或时间列。列被设置为该列类型适当的“零”值。

3)Insert…select

当目标表和select语句中的表相同时，则会将select语句的结果存放在临时表中，再插入到目标表中(注意执行顺序)。

4)INSERT INTO … SELECT语句满足下列条件

查询不能包含一个ORDER BY子句。

INSERT语句的目的表不能出现在SELECT查询部分的FROM子句，因为这在ANSI SQL中被禁止让从你正在插入的表中SELECT。(问题是SELECT将可能发现在同一个运行期间内先前被插入的记录。当使用子选择子句时，情况能很容易混淆)

AUTO_INCREMENT列像往常一样工作。