我建议使用 INSERT...ON DUPLICATE KEY UPDATE.

如果您使用INSERT IGNORE，那么如果导致重复键，该行实际上不会被插入.但该语句不会产生错误.相反，它会生成警告.这些情况包括:

If you use INSERT IGNORE, then the row won't actually be inserted if it results in a duplicate key. But the statement won't generate an error. It generates a warning instead. These cases include:

• 在具有 PRIMARY KEY 或 UNIQUE 约束的列中插入重复键.
• 将 NULL 插入具有 NOT NULL 约束的列中.
• 向分区表插入一行，但您插入的值未映射到分区.

• Inserting a duplicate key in columns with PRIMARY KEY or UNIQUE constraints.
• Inserting a NULL into a column with a NOT NULL constraint.
• Inserting a row to a partitioned table, but the values you insert don't map to a partition.

如果你使用REPLACE，MySQL实际上在内部做了一个DELETE后跟一个INSERT，这有一些意想不到的副作用:

If you use REPLACE, MySQL actually does a DELETE followed by an INSERT internally, which has some unexpected side effects:

• 分配了一个新的自增 ID.
• 具有外键的相关行可能会被删除(如果您使用级联外键)，否则会阻止 REPLACE.
• 不必要地执行在 DELETE 上触发的触发器.
• 副作用也会传播到副本.

更正:REPLACE 和 INSERT...ON DUPLICATE KEY UPDATE 都是 MySQL 特有的非标准专有发明.ANSI SQL 2003 定义了一个 MERGE 语句，可以解决同样的(甚至更多)需求，但是 MySQL 不支持 MERGE 语句.

correction: both REPLACE and INSERT...ON DUPLICATE KEY UPDATE are non-standard, proprietary inventions specific to MySQL. ANSI SQL 2003 defines a MERGE statement that can solve the same need (and more), but MySQL does not support the MERGE statement.

有用户试图编辑此帖子(编辑被版主拒绝).该编辑试图添加一个声明，即 INSERT...ON DUPLICATE KEY UPDATE 会导致分配一个新的自动递增 ID.确实，新的id是生成的，但是在改变的行中并没有使用.

A user tried to edit this post (the edit was rejected by moderators). The edit tried to add a claim that INSERT...ON DUPLICATE KEY UPDATE causes a new auto-increment id to be allocated. It's true that the new id is generated, but it is not used in the changed row.

请参阅下面的演示，使用 Percona Server 5.5.28 进行测试.配置变量innodb_autoinc_lock_mode=1(默认):

See demonstration below, tested with Percona Server 5.5.28. The configuration variable innodb_autoinc_lock_mode=1 (the default):

mysql> create table foo (id serial primary key, u int, unique key (u));
mysql> insert into foo (u) values (10);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   10 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1

mysql> insert into foo (u) values (10) on duplicate key update u = 20;
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+

mysql> show create table foo\G
CREATE TABLE `foo` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `u` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `u` (`u`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

上面演示了IODKU语句检测到重复，并调用更新来改变u的值.请注意，AUTO_INCREMENT=3 表示生成了 id，但未在行中使用.

The above demonstrates that the IODKU statement detects the duplicate, and invokes the update to change the value of u. Note the AUTO_INCREMENT=3 indicates an id was generated, but not used in the row.

而 REPLACE 确实删除原始行并插入新行，生成并存储一个新的自增 ID:

Whereas REPLACE does delete the original row and inserts a new row, generating and storing a new auto-increment id:

mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  1 |   20 |
+----+------+
mysql> replace into foo (u) values (20);
mysql> select * from foo;
+----+------+
| id | u    |
+----+------+
|  3 |   20 |
+----+------+