插入 MySQL 表或更新(如果存在)

2021-11-20 00:00:00 sql upsert mysql insert-update

我想向数据库表中添加一行，但如果存在具有相同唯一键的行，我想更新该行.

I want to add a row to a database table, but if a row exists with the same unique key I want to update the row.

例如:

INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19);

假设唯一键是ID，在我的数据库中，有一行ID = 1.在这种情况下，我想用这些值更新该行.通常这会出错.
如果我使用 INSERT IGNORE 它会忽略错误，但它仍然不会更新.

Let’s say the unique key is ID, and in my Database, there is a row with ID = 1. In that case, I want to update that row with these values. Normally this gives an error.
If I use INSERT IGNORE it will ignore the error, but it still won’t update.

推荐答案

使用 INSERT ... ON DUPLICATE KEY UPDATE

查询:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE name="A", age=19

相关文章