方法一: 传统方法 not exists()
方法二: replace into
表结构CREATE TABLE`t` (
`id`int(11) NOT NULLAUTO_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=utf8insert into t (a,b) values(2,2),(3,3),(4,4);
mysql> select * fromt;+----+---+---+
| 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.01sec)
mysql> select * fromt;+----+---+----+
| 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.02sec)
mysql> select * fromt;+----+---+----+
| 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 * fromt;+----+---+----+
| id | a | b |
+----+---+----+
| 1 | 2 | 2 |
| 3 | 4 | 4 |
| 4 | 3 | 30 |
+----+---+----+
3 rows in set (0.00sec)
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.04sec)
mysql> select * fromt;+----+---+----+
| 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