[转]MySQL的replace into

最新推荐文章于 2025-09-06 22:56:13 发布

weixin_34129696

最新推荐文章于 2025-09-06 22:56:13 发布

阅读量61

点赞数

文章标签：数据库开发工具

本文对比了MySQL中REPLACE与INSERT ON DUPLICATE KEY UPDATE两种操作的不同之处，详细解释了它们在处理主键冲突时的行为差异，包括如何处理缺失字段及自增ID的变化。

转载自：网络

一。 REPLACE INTO table_name ( `col_A`, `col_B`) VALUES ( `col A data`, `col B data`) ;

Replaces items in a row. This action is dependant on the "id" because when doing a REPLACE, you must include the PRIMARY (unique) column. Since we established the "id" column as our PRIMARY key (when establishing the table), MySQL needs this info so it knows which row we are talking about. If we didn't include the "id" coumn, MySQL will have no idea which row we are trying to replace.

In this example, we are replacing row #2
$sql = "REPLACE INTO music (id,artist,album) VALUES ('2','The Beatles','Let It Be')";
mysql_query($sql);
id artist album title track year
1 the beatles Abbey Road
2 The Beatles Let It Be
3 Abbey Road 3 test
Records: 3

Here, we haven't defined the "id" column. Hence, MySQL doesn't know which row to replace, so it just adds a new row.

So as we can see, the REPLACE feature acts very similar to INSERT. We can use this to our advantage!

Again, REPLACE behaves much like INSERT except that:
- if the PRIMARY ("unique column") is supplied, the existing row will be updated
- if the PRIMARY ("unique column") is not provided, a new row will be added.
$sql = "REPLACE INTO music (artist,album) VALUES ('The Beatles','The Magical Mystery Tour')";
mysql_query($sql);
id artist album title track year
1 the beatles Abbey Road
2 The Beatles Let It Be
3 Abbey Road 3 test
4 The Beatles The Magical Mystery Tour
Records: 4
NOTE: You may get errors when trying this kind of REPLACE, because usually the "unique column" can not be null (or empty). Some people like to set up the database where the "unique column" is automatically incremented by MySQL. This is kind of confusing and can lead to a headache. So just remember to include the "unique column" when using the REPLACE statement, or you'll get duplicate rows... OR use the UPDATE statement.

二。 Mysql replace into 与 insert into on duplicate key update 的区别这两种方式的作用是如果数据库中存在记录就更新，否则就插入新记录，但是在使用上也是有一点区别的。总结如下： 1. 如果表中不存在主键记录，replace和insert*update都与insert是一样的特点。 2. 如果表中存在主键记录，replace相当于执行delete 和 insert两条操作，而insert*update的相当于执行if exist do update else do insert操作。因此，如果replace填充的字段不全，则会导致未被更新的字段都会修改为默认值，并且如果有自增id的话，自增id会变化为最新的值（这样如果是以自增id为标志的话可能导致记录丢失）；而insert*update只是更新部分字段，对于未被更新的字段不会变化（不会强制修改为默认值）。

三。unique键的情况

如果存在unique键（无论是某个字段或者是组合字段）， replace into执行的效果是：如果unique键对应的内容已经存在，那么更新主键；如果unique键对应的内容不存在，则插入一条记录。