# 避免重复的行记录 

__*ON DUPLICATE KEY UPDATE*__
__*处理重复插入行*__

+ `INSERT ... ON DUPLICATE KEY UPDATE`: 避免重复行记录错误
+ 类似于 REPLACE, 但它使用 UPDATE 而不是 DELETE 来处理重复的行记录

```8
tidb> select * from test.r1;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  1 | Z    | F      |
|  2 | Z    | F      |
+----+------+--------+
2 rows in set (0.00 sec)

tidb> replace into test.r1 (id, name) values (1, 'A');
Query OK, 2 rows affected (0.00 sec)

tidb> insert into test.r1 (id, name) values (2, 'B') on duplicate key update name='B';
Query OK, 2 rows affected (0.01 sec)

tidb> select * from test.r1;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  1 | A    | ~!@NULL@!~   |
|  2 | B    | ~!@F@!~      |
+----+------+--------+
2 rows in set (0.00 sec)
```
----------------------------------------------------------------
```
tidb> SELECT * FROM test.r1;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  1 | Z    | F      |
|  2 | Z    | F      |
+----+------+--------+
2 rows in set (0.00 sec)

tidb> REPLACE INTO test.r1 (id, name) VALUES (1, 'A');
Query OK, 2 rows affected (0.01 sec)

tidb> SELECT * FROM test.r1;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  1 | A    | NULL   |
|  2 | Z    | F      |
+----+------+--------+
2 rows in set (0.00 sec)

tidb> INSERT INTO test.r1 (id, name) VALUES (2, 'B') ON DUPLICATE KEY UPDATE name ='B';
Query OK, 2 rows affected (0.01 sec)

tidb> SELECT * FROM test.r1;
+----+------+--------+
| id | name | gender |
+----+------+--------+
|  1 | A    | NULL   |
|  2 | B    | F      |
+----+------+--------+
2 rows in set (0.00 sec)
```