设为首页 收藏本站
查看: 797|回复: 0

[经验分享] 转 mysql insert的几点操作(DELAYED 、IGNORE、ON DUPLICATE KEY UPDATE )

[复制链接]

尚未签到

发表于 2016-10-22 08:29:28 | 显示全部楼层 |阅读模式
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
的区别。
有则更新指定列,无则添加。

运维网声明 1、欢迎大家加入本站运维交流群:群②:261659950 群⑤:202807635 群⑦870801961 群⑧679858003
2、本站所有主题由该帖子作者发表,该帖子作者与运维网享有帖子相关版权
3、所有作品的著作权均归原作者享有,请您和我们一样尊重他人的著作权等合法权益。如果您对作品感到满意,请购买正版
4、禁止制作、复制、发布和传播具有反动、淫秽、色情、暴力、凶杀等内容的信息,一经发现立即删除。若您因此触犯法律,一切后果自负,我们对此不承担任何责任
5、所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其内容的准确性、可靠性、正当性、安全性、合法性等负责,亦不承担任何法律责任
6、所有作品仅供您个人学习、研究或欣赏,不得用于商业或者其他用途,否则,一切后果均由您自己承担,我们对此不承担任何法律责任
7、如涉及侵犯版权等问题,请您及时通知我们,我们将立即采取措施予以解决
8、联系人Email:admin@iyunv.com 网址:www.yunweiku.com

所有资源均系网友上传或者通过网络收集,我们仅提供一个展示、介绍、观摩学习的平台,我们不对其承担任何法律责任,如涉及侵犯版权等问题,请您及时通知我们,我们将立即处理,联系人Email:kefu@iyunv.com,QQ:1061981298 本贴地址:https://www.yunweiku.com/thread-289632-1-1.html 上篇帖子: mysql取得一个字符串相同字符中的最后一个字符的位置 下篇帖子: MySQL ERROR 1071 (42000): Specified key was too long; max key length is 767 byte
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

扫码加入运维网微信交流群X

扫码加入运维网微信交流群

扫描二维码加入运维网微信交流群,最新一手资源尽在官方微信交流群!快快加入我们吧...

扫描微信二维码查看详情

客服E-mail:kefu@iyunv.com 客服QQ:1061981298


QQ群⑦:运维网交流群⑦ QQ群⑧:运维网交流群⑧ k8s群:运维网kubernetes交流群


提醒:禁止发布任何违反国家法律、法规的言论与图片等内容;本站内容均来自个人观点与网络等信息,非本站认同之观点.


本站大部分资源是网友从网上搜集分享而来,其版权均归原作者及其网站所有,我们尊重他人的合法权益,如有内容侵犯您的合法权益,请及时与我们联系进行核实删除!



合作伙伴: 青云cloud

快速回复 返回顶部 返回列表