当前位置:首页 > 框架开发 > INSERT INTO .. ON DUPLICATE KEY更新多行记录,自动判断重复,更新

INSERT INTO .. ON DUPLICATE KEY更新多行记录,自动判断重复,更新

snuday12年前 (2012-12-18)框架开发26

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

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

UPDATE TABLE SET c=c+1 WHERE a=1;

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

     如果你想了解更多关于INSERT INTO .. ON DUPLICATE KEY的功能说明,详见MySQL参考文档:13.2.4. INSERT语法

 


 

     现在问题来了,如果INSERT多行记录, ON DUPLICATE KEY UPDATE后面字段的值怎么指定?要知道一条INSERT语句中只能有一个ON DUPLICATE KEY UPDATE,到底他会更新一行记录,还是更新所有需要更新的行。这个问题困扰了我很久了,其实使用VALUES()函数一切问题都解决了。

     举个例子,字段a被定义为UNIQUE,并且原数据库表table中已存在记录(2,2,9)和(3,2,1),如果插入记录的a值与原有记录重复,则更新原有记录,否则插入新行:

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

     以上SQL语句的执行,发现(2,5,7)中的a与原有记录(2,2,9)发生唯一值冲突,则执行ON DUPLICATE KEY UPDATE,将原有记录(2,2,9)更新成(2,5,9),将(3,2,1)更新成(3,3,1),插入新记录(1,2,3)和(4,8,2)

     注意:ON DUPLICATE KEY UPDATE只是MySQL的特有语法,并不是SQL标准语法!

如果您指定了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选项被忽略。

您可以使用SQL LAST_INSERT_ID()函数查找用于AUTO_INCREMENT列的值。从C API的内部,使用mysql_insert_id()函数。不过,您应该注意,两个函数的作用并不总是相同的。在12.9.3节,“信息函数”25.2.3.36节,“mysql_insert_id()”中进一步讨论了与AUTO_INCREMENT列有关的INSERT

标签: MysqlPHP架构

相关文章

PHPCMS后台密码找回及时间解锁方法

第一种解决办法    下载附件中的文件上传到phpcms所在目录,解压该文件后,上传后访问这个文件即可。即会给你输出用户名和密码!     ...

zend optimizer之php加速器forIIS安装实例

zend optimizer之php加速器forIIS安装实例

windows2003服务器配置IIS6.0+PHP+Mysql+Zend环境组建[图文]Win2003IIS6.0+PHP+Mysql+Zend+环境组建 相关软件如下: 操作系统:Windows...

PHPCMS内容页顶过之后不显示数目,一直处理中的解决办法.....

最近刚刚升级使用最新版的PHPCMS,发现顶一下点击后一直处于处理中,还会出现一些很奇观的问题。而且点击查看也无法查看的问题,请按照以下方法解决找到原因是因为没办法取到栏目ID值。没取到值时表现如下:...

PHP时差及其cannot modify header 集成处理方案

php中时差问题的解决方法:查找php.ini里date.timezone有这么一行(如果没有可以添加上);date.timezone =将;去掉,改成date.timezone = PRC,就OK了...

PHP for IIS(windows2003、Windows2008)zend官方集成环境

PHP for IIS(windows2003、Windows2008)zend官方集成环境

如果您已经安装了IIS,支持了asp和.net,也许你还希望在iis上安装PHP+Zend+Mysql+phpMyAdmin,难道要一个软件一个软件的下载安装,有没有简单的办法? 有!www.ctoh...

Varnish+nginx+php(FastCGI)+MYSQL5+MenCache+MenCachedb构建大流量服务器集群

Varnish+nginx+php(FastCGI)+MYSQL5+MenCache+MenCachedb构建大流量服务器集群

架构 Varnish+nginx+php(FastCGI)+MYSQL5+MenCache+MenCachedb说明:我在设计系统架构时,进行了大胆的尝试,只用6台Web服务器,达到了可承受4000万...