【mysql】insert 冲突

问题，如果在某一列或者某几列上定义了unique约束或者key，插入相同的数据就会报错，例子：

mysql> show index from user;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY       |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          0 | uniq_name_age |            1 | name        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          0 | uniq_name_age |            2 | age         | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> select * from user;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | li    |   56 |
|  1 | liyao |   27 |
|  7 | lzy   |   19 |
+----+-------+------+
3 rows in set (0.01 sec)

 

mysql> insert into user (name,age)values("lzy",19);
ERROR 1062 (23000): Duplicate entry 'lzy-19' for key 'uniq_name_age'

将会得到一个duplicate的error。

 

解决办法有三种：

1）insert ignore

如果有冲突就忽略，可以通过insert的返回值判断到底有没有冲突；

mysql> insert ignore into user (name,age)values("lzy",19);
Query OK, 0 rows affected, 1 warning (0.00 sec)

可以看到，rows返回0.

 

2）on duplicate key update col1 = xxx, col2 = xxx

mysql> insert into user (name,age)values("lzy",19) on duplicate key update name = "lzy1", age = 19;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from user;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  2 | li    |   56 |
|  1 | liyao |   27 |
|  7 | lzy1  |   19 |
+----+-------+------+
3 rows in set (0.01 sec)

可以看到行被更新。

这个在计数的场景下很常用，update count = count + 1

 

3）replace into

 其中的into可以省略。

replace into table () values ()；

replace into table select；

replace into table set；

共有三种用法。