MySQL insert update on dumplicate

mysql insert的几点操作(DELAYED 、IGNORE、ON DUPLICATE KEY UPDATE )

INSERT 语法

INSERT [LOW_PRIORITY | 
DELAYED

 | HIGH_PRIORITY] [

IGNORE

]

       [INTO] tbl_name [(col_name,...)]

       
VALUES ({expr | DEFAULT},...),(...),..
.

       [
 ON DUPLICATE KEY UPDATE

 col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | 
DELAYED

 | HIGH_PRIORITY] [

IGNORE

]

       [INTO] tbl_name

       SET col_name={expr | DEFAULT}, ...

       
[
 
ON DUPLICATE KEY UPDATE

 
col_name=expr, ... ]

或：

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [
IGNORE

]

       [INTO] tbl_name [(col_name,...)]

       SELECT ...

       
[ 

ON DUPLICATE KEY UPDATE

 
col_name=expr, ...
 ]

一、DELAYED

 
的使用

     

使用延迟插入操作

DELAYED
调节符应用于INSERT
和REPLACE
语句。当DELAYED
插入操作到达的时候，


服务器把数据行放入一个队列中，并立即给客户端返回一个状态信息，这样客户


端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据


表中读取数据，队列中的数据就会被保持着，直到没有读取者为止。接着服务器


开始插入延迟数据行（delayed-row
）队列中的数据行。在插入操作的同时，服务器


还要检查是否有新的读取请求到达和等待。如果有，延迟数据行队列就被挂起，


允许读取者继续操作。当没有读取者的时候，服务器再次开始插入延迟的数据行。


这个过程一直进行，直到队列空了为止。

几点要注意事项：

· INSERT DELAYED

应该仅用于指定值清单的INSERT
语句。服务器忽略用于INSERT DELAYED...SELECT
语句的DELAYED
。

· 

服务器忽略用于INSERT DELAYED...ON DUPLICATE UPDATE
语句的DELAYED
。

· 

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

· 

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

· DELAYED

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

注意，目前在队列中的各行只保存在存储器中，直到它们被插入到表中为止。这意味着，如果您强行中止了mysqld
（例如，使用kill -9
）


或者如果mysqld
意外停止，则所有没有被写入磁盘的行都会丢失。

二、IGNORE

的使用

IGNORE
是MySQL



相对于标准SQL
的扩展。如果在新表中有重复关键字，


或者当STRICT
模式启动后出现警告，则使用IGNORE
控制ALTER TABLE
的运行。


如果没有指定IGNORE
，当重复关键字错误发生时，复制操作被放弃，返回前一步骤。


如果指定了IGNORE
，则对于有重复关键字的行，只使用第一行，其它有冲突的行被删除。


并且，对错误值进行修正，使之尽量接近正确值。

insert ignore into tb(...) value(...)

这样不用校验是否存在了，有则忽略，无则添加

 



三、ON DUPLICATE KEY UPDATE
的使用

如果您指定了ON DUPLICATE KEY UPDATE ，并且插入行后会导致在一个UNIQUE 索引或PRIMARY KEY 中出现重复值，则执行旧行UPDATE 。例如，如果列a 被定义为UNIQUE ，并且包含值1 ，则以下两个语句具有相同的效果：

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)

       
-> ON DUPLICATE KEY UPDATE c=c+1;

mysql> UPDATE table SET c=c+1 WHERE a=1;

如果行作为新记录被插入，则受影响行的值为1 ；如果原有的记录被更新，则受影响行的值为2 。

注释：如果列b 也是唯一列，则INSERT 与此UPDATE 语句相当：

mysql> UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

如果a=1 OR b=2 与多个行向匹配，则只有一个行被更新。通常，您应该尽量避免对带有多个唯一关键字的表使用ON DUPLICATE KEY 子句。

您可以在UPDATE 子句中使用VALUES(col_name) 函数从INSERT...UPDATE 语句的INSERT 部分引用列值。换句话说，如果没有发生重复关键字冲突，则UPDATE 子句中的VALUES(col_name ) 可以引用被插入的col_name 的值。本函数特别适用于多行插入。VALUES() 函数只在INSERT...UPDATE 语句中有意义，其它时候会返回NULL 。

示例：

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)

       
-> ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

本语句与以下两个语句作用相同：

mysql> INSERT INTO table (a,b,c) VALUES (1,2,3)

       
-> ON DUPLICATE KEY UPDATE c=3;

mysql> INSERT INTO table (a,b,c) VALUES (4,5,6)

       
-> ON DUPLICATE KEY UPDATE c=9;

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

 

总结 ：DELAYED 做为快速插入，并不是很关心失效性，提高插入性能。

        ignore     只关注主键对应记录是不存在，无则添加，有则忽略。

     ON DUPLICATE KEY UPDATE 在添加时操作，关注非主键列，注意与ignore 的区别。有则更新指定列，无则添加。

posted on 2008-08-22 14:17 鱼有所思 阅读(3009) 评论(1)   编辑   收藏 引用 网摘 所属分类: MySQL

评论: #   MySQL INSERT ... ON DUPLICATE KEY UPDATE 2008-08-22 14:26 | 鱼有所思
INSERT ... ON DUPLICATE KEY UPDATE，当插入的记录会引发主键冲突或者违反唯一约束时，则使用UPDATE更新旧的记录，否则插入新记录。

mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| uid | int(11) | NO | PRI | | |
| uname | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from test;
+-----+--------+
| uid | uname |
+-----+--------+
| 1 | uname1 |
| 2 | uname2 |
| 3 | me |
+-----+--------+
3 rows in set (0.00 sec)

mysql> INSERT INTO test values ( 3,'insertName' )
-> ON DUPLICATE KEY UPDATE uname='updateName';
Query OK, 2 rows affected (0.03 sec)

mysql> select * from test;+-----+------------+
| uid | uname |
+-----+------------+
| 1 | uname1 |
| 2 | uname2 |
| 3 | updateName |
+-----+------------+
3 rows in set (0.00 sec)

mysql> create index i_test_uname on test(uname);
Query OK, 3 rows affected (0.20 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> INSERT INTO test VALUES ( 1 , 'uname2') -> ON DUPLICATE KEY UPDATE uname='update2records';Query OK, 2 rows affected (0.00 sec)

mysql> select * from test;+-----+----------------+
| uid | uname |
+-----+----------------+
| 2 | uname2 |
| 1 | update2records |
| 3 | updateName |
+-----+----------------+
3 rows in set (0.00 sec)

插入时会与两条记录发生冲突，分别由主键和唯一索引引起。但最终只UPDATE了其中一条。这在手册中也说明了，有多个唯一索引（或者有键也有唯一索引）的情况下，不建议使用该语句