`

转 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
调节符应用于INSERTREPLACE
语句。当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

的使用

IGNOREMySQL



相对于标准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 的区别。 有则更新指定列,无则添加。

分享到:
评论

相关推荐

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

    MySQL中的`INSERT`语句是用于向数据库表中插入新数据的命令,它有多种变体,包括`DELAYED`, `IGNORE`, 和 `ON DUPLICATE KEY UPDATE`。这些选项提供了不同的处理策略,以适应不同场景的需求。 1. **DELAYED**: `...

    MySQL小技巧

    `ON DUPLICATE KEY UPDATE`则是在遇到重复键时进行更新的操作。 #### 三、关键字解析 - **LOW_PRIORITY**: 低优先级,当使用此选项时,服务器将等待所有读取操作完成后再执行插入操作。 - **DELAYED**: 延迟插入,...

    mysql insert语句操作实例讲解

    但如果使用IGNORE选项,MySQL将自动忽略该错误并可能执行其他操作,具体取决于ON DUPLICATE KEY UPDATE子句。 9. **多行INSERT语句**: 通过在VALUES列表中包含多个值对,可以同时插入多行数据。例如,`INSERT ...

    MySQL从入门到精通管理自己的数据.pdf

    使用`ON DUPLICATE KEY UPDATE`可以在插入时遇到主键冲突时更新已有记录。 3. **DELETE语句**:用于删除表中的记录。例如,删除部门ID为1001的记录: ```sql DELETE FROM department WHERE department_id=1001; ...

    php mysql 判断update之后是否更新了的方法

    1. `ON DUPLICATE KEY UPDATE`:在INSERT语句中,如果插入的记录违反了唯一索引,此选项允许你更新已经存在的记录,而不是抛出错误。 2. `DELAYED`:在INSERT语句中,这个选项告诉MySQL在事务提交后再插入数据,这...

    SQL语言的使用-数据库和表.doc

    在插入数据时,`INSERT INTO`语句用于指定表和值,如果有主键冲突,`ON DUPLICATE KEY UPDATE`可以更新已有记录。`IDENTITY`或`AUTO_INCREMENT`通常与主键一起使用,确保每个记录的唯一性。 此外,`LOW PRIORITY`、...

Global site tag (gtag.js) - Google Analytics