Mysql on duplicate key update用法及优缺点

Mysql on duplicate key update用法及优缺点

先建立张测试表

CREATE TABLE `insert_update_test` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `stu_id` int(11) DEFAULT NULL COMMENT '学生ID',
  `stu_name` varchar(50) DEFAULT NULL COMMENT '学生名字',
  `yn` tinyint(1) NOT NULL DEFAULT '0' COMMENT '删除标识：1 已删除，0 未删除',
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '时间戳',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_idx_stu_id` (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='插入更新测试表'

1、第一次插入数据

INSERT INTO insert_update_test  (stu_id, stu_name, yn) VALUES (1001, '张三',0)  
  ON DUPLICATE KEY UPDATE stu_name = "插入更新";  

2、再次执行SQL
执行结果

表数据结果

问题注意：

一、id自增过快

如果数据表id是自动递增的不建议使用该语句；id不连续，如果前面更新的比较多，新增的下一条会相应跳跃的更大

实测：

INSERT INTO insert_update_test  (stu_id, stu_name, yn) VALUES (1021, '王五',0)  
  ON DUPLICATE KEY UPDATE stu_name = "插入更新";

执行了几次张三的插入更新后插入理应为第二条数据的王五，结果发现王五的自增id是5，而不是2，这样就造成id自增过快，使用一定要慎重

死锁

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，如果存在，在对该现有的行加上S（共享锁）锁，如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作，然后对该记录加上X（排他锁），最后进行update写入。

实例：