mysql技巧：如果记录存在则更新/如果不存在则插入的三种处理方法

方法一: 传统方法 not exists()

方法二: replace into

表结构
 CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

insert into t (a,b) values(2,2),(3,3),(4,4);

mysql> select * from t;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 2 | 2 |
|  2 | 3 | 3 |
|  3 | 4 | 4 |
+----+---+---+
3 rows in set (0.00 sec)

replace into相当于，先检测该记录是否存在(根据表上的唯一键)，如果存在，先delete，然后再insert。 这个方法有一个很大的问题，如果记录存在，每次执行完，主键自增id就变了（相当于重新insert了一条），对于有复杂关联的业务场景，如果主表的id变了，其它子表没做好同步，会死得很难看。-- 不建议使用该方法！

mysql> replace into t(a,b) values(3,30);
Query OK, 2 rows affected (0.01 sec)

mysql> select * from t;
+----+---+----+
| id | a | b  |
+----+---+----+
|  1 | 2 |  2 |
|  3 | 4 |  4 |
|  4 | 3 | 30 | #id 变成了4,即将原记录删除再重新插入
+----+---+----+
3 rows in set (0.00 sec)

下面删除唯一索引

alter table t drop index `a`;

mysql> replace into t(a,b) values(4,40);
Query OK, 1 row affected (0.02 sec)

mysql> select * from t;
+----+---+----+
| id | a | b  |
+----+---+----+
|  1 | 2 |  2 |
|  3 | 4 |  4 | #原来记录,没有变
|  4 | 3 | 30 |
|  5 | 4 | 40 | #新增加了记录
+----+---+----+
4 rows in set (0.00 sec)

方法三：on duplicate key

delete from t where id=5;                     删除上面生成的重复键

alter table t add unique  index `a`(a);  重新加上唯一索引

mysql> select * from t;
+----+---+----+
| id | a | b  |
+----+---+----+
|  1 | 2 |  2 |
|  3 | 4 |  4 |
|  4 | 3 | 30 |
+----+---+----+
3 rows in set (0.00 sec)


mysql> insert into t(a,b) values(4,40) ON DUPLICATE KEY UPDATE a=values(a),b=values(b);
Query OK, 2 rows affected (0.04 sec)

mysql> select * from t;
+----+---+----+
| id | a | b  |
+----+---+----+
|  1 | 2 |  2 |
|  3 | 4 | 40 | #只更新这行值,id没变
|  4 | 3 | 30 |
+----+---+----+
3 rows in set (0.00 sec)

注意上面的on duplicate key，遇到重复键（即：违反了唯一约束），这时会做update，否则做insert。该方法，没有replace into的副作用，不会导致已存在记录的自增id变化。但是有另外一个问题，如果这个表上有不止一个唯一约束，在特定版本的mysql中容易产生dead lock（死锁），见网友文章https://blog.csdn.net/pml18710973036/article/details/78452688