insert into on duplicate key u

解决难题：Mysql_inserOrUpdate_解决办法

mysql "ON DUPLICATE KEY UPDATE" 语法

因为在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，所以插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致唯一值列重复的难题，则插入新行。

例如，所以列 a 为 主键 或 拥有UNIQUE索引，所以包括值1，则以上两个语句具有相似的作用：

复制源码代码如下:

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

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

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

这个单词还可以这么用:

因为INSERT多行记录(计算 a 为数组或 a 是一个 UNIQUE索引列):

复制源码代码如下:

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

执行后, c 的值会变为 4 (第二条与第一条重复, c 在现值上+1).

复制源码代码如下:

INSERT INTO TABLE (a,c) VALUES (1,3),(1,7) ON DUPLICATE KEY UPDATE c=VALUES(c);

执行后, c 的值会变为 7 (第二条与第一条重复, c 在直接取重复的值7).

注意：ON DUPLICATE KEY UPDATE只是MySQL的特有语法，并不是SQL标准英语！

这个单词和适于用在需要 判断记录能否存在,不存在则插入存在则更新的画面.

INSERT INTO .. ON DUPLICATE KEY更新多行记录

因为在INSERT语句末尾指定了ON DUPLICATE KEY UPDATE，所以插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE；如果不会导致唯一值列重复的难题，则插入新行。例如，所以列a被定义为UNIQUE，所以包括值1，则以上两个语句具有相似的作用：

复制源码代码如下:

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值与原有记录重复，则更新原有记录，如果插入新行：

复制源码代码如下:

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)

DUPLICATE KEY UPDATE batch执行时出死锁错误

背景知识

一、 mysql insert 与 duplicate key：

典型的插入语句：

多条：INSERT INTO tablename (columnA, columnB, columnC)

VALUES ('a', 1, 2), ('b', 7, 5)

单条：INSERT INTO tablename SET columnA='a', columnB=1, columnC=2

复制：INSERT [options1] [INTO] tablename [ (columnlist) ]

SELECT …

若表已设置字段如columnA，重覆的插入无效

ERROR 1062 (23000): Duplicate entry 'value' for key 'PRIMARY'

因为中已有某条数据，以上的两条语句可等同：

INSERT INTO tablename (id, data) VALUES (1, 10)
ON DUPLICATE KEY UPDATE data=data+10;

UPDATE tablename SET data=data+10 WHERE id=1;

duplicate key语句一般应用在 格式化多条更新语句：

INSERT INTO tablename (id, data) VALUES (1, 10), (2, 15)

ON DUPLICATE KEY UPDATE data=data+VALUE(data)

二、innodb表增加插入效率

查询表使用的引擎： show create table tablename;

innodb 的储存引擎提供行级锁，支持共享锁和排他锁两种锁定模式，还有四种不同的隔离级别。

对于Innodb 类型的表，我们有以上几种方式可以增加导入的效能：

a. 因为Innodb 类型的表是根据数组的次序保留的，但是将导出的数据根据数组的顺

序排列，可以有效的提高导入数据的效能。如果Innodb 表没有主键，所以功能会默认

创建一个内部列也是索引，但是即使可以给表创建一个主键，将可以借助这个劣势降低

导出数据的效能。

b. 在导出数据前执行SET UNIQUE_CHECKS=，关掉唯一性校验insert into 和update，在导入结束后执行SET

UNIQUE_CHECKS=1，恢复唯一性校验，可以增加导入的效能。

c. 如果应用使用自动填写的方法，建议在导出前执行SET AUTOCOMMIT=，关掉自动

填写，导入结束后再执行SET AUTOCOMMIT=1，打开自动填写，也可以增加导入的效能。

因为因为你同样从同一顾客插入很多行，使用多个值表的INSERT 语句。这比使用分开INSERT 语句快(在一些现象中几倍)。

你从不同客户插入很多行insert into 和update，能通过使用INSERT DELAYED 语句得到更高的速率。Delayed 的意思是让insert 语句马上执行，当然数据都被放置硬盘的队列中，并没有真正写入硬盘；这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有这些用户对表的读写完后才进行插入；

在项目中碰到的难题时，使用了这样insert处理，所以分库分表，数据表类型为innodb， tablename各不相同，duplicate key只是用来合并update和insert语句。

"java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction" 错误情况为 第一个插入即出错，或一个batch中重复一个key，插入多个值。 貌似mysql有这个bug（?id=52020）

使用threadlocal去获取操作的对象，static对象，获取pool的连接并执行批处理方法

mysql DUPLICATE KEY UPDATE 性能难题

1. 测试 replace into 引发死锁

2. 测试 replace 和INSET INTO *** ON DUPLICATE KEY UPDATE *** 性能差

1、 系统：

名称：Mysql 5.5.31

引擎：innodb

字符集：UTF8

安装方法：代码编译安装

2、 部署环境：

软硬件环境：Linux（CentOS-6.2-x86_64）

Mysql 自带 Mysqlslap

一切操作前重启mysql，防止缓存。

检测表：

CREATE TABLE`test_replace_into` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`wechat_init_code` int(11) NOT NULL,

`used` varchar(50) DEFAULT '',

`expire` int(11) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),

UNIQUE KEY `ii` (`wechat_init_code`)

) ENGINE=InnoDBDEFAULT CHARSET=utf8;

insert_on_duplicate.sql：

SET@mm=ROUND(RAND()*100000);

SET@nn=ROUND(RAND()*100000);

INSERT INTOtest.`test_replace_into`(wechat_init_code,used,expire) VALUES (@mm,@nn,@nn) ONDUPLICATE KEY UPDATE used =@nn,expire=@nn ;

replace_into.sql:

SET@mm=ROUND(RAND()*100000);

SET @nn=ROUND(RAND()*100000);

REPLACE INTOtest.`test_replace_into` (`wechat_init_code`,`used`,`expire`) VALUES(@mm,@nn,@nn);

insert_on_duplicate_with_select.sql:

SET@mm=ROUND(RAND()*100000);

SET@nn=ROUND(RAND()*100000);

INSERT INTOtest.`test_replace_into`(wechat_init_code,used,expire) VALUES (@mm,@nn,@nn) ONDUPLICATE KEY UPDATE used =@nn,expire=@nn ;

CALLtest.`proc_test_replace_int`();

replace_into_with_select.sql:

SET@mm=ROUND(RAND()*100000);

SET@nn=ROUND(RAND()*100000);

REPLACE INTOtest.`test_replace_into` (`wechat_init_code`,`used`,`expire`) VALUES(@mm,@nn,@nn);

CALLtest.`proc_test_replace_int`();

test.`proc_test_replace_int`()：

DELIMITER $$

USE `test`$$

DROP PROCEDURE IFEXISTS `proc_test_replace_int`$$

CREATEDEFINER=`root`@`%` PROCEDURE `proc_test_replace_int`()

BEGIN

SET @mm=ROUND(RAND()*100000);

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/ruanjian/article-118550-1.html