mysql中on duplicate key update用法

mysql中on duplicate key update用法

在MySQL中，ON DUPLICATE KEY UPDATE是一种特殊的INSERT语句，它允许在插入一行数据时，如果发生了主键或唯一索引冲突，就执行更新操作。这个功能非常有用，可以用来处理重复数据的情况，或者在进行批量插入时避免插入重复数据。以下是关于ON DUPLICATE KEY UPDATE的详细用法说明。

语法：

INSERT INTO table_name (column1, column2, ..., columnN)

VALUES (value1, value2, ..., valueN)

ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...,

columnN = valueN;

使用ON DUPLICATE KEY UPDATE语句的条件是，插入的表必须有一个主键或者唯一索引。

当执行INSERT语句时，如果插入的行的主键或唯一索引与表中已有的行发生冲突，就会执行更新操作，更新操作的列和值由SET子句定义。

下面是一个具体的例子来说明ON DUPLICATE KEY UPDATE的用法：

假设有一个表students，其中有sid（主键），name和age三列。

1. 插入一条新数据：

INSERT INTO students (sid, name, age) VALUES (1, 'Alice', 18);

如果表中没有主键或唯一索引为（1, 'Alice', 18）的行，那么这个INSERT语句会插入一条新的数据。

2. 插入一条新数据，如果主键已存在，则更新age字段：

INSERT INTO students (sid, name, age) VALUES (1, 'Alice', 20)

ON DUPLICATE KEY UPDATE age = VALUES(age);

如果表中已经有主键为1的行，那么这个INSERT语句将会更新该行的age字段为20。

3. 插入一条新数据，如果主键已存在，则更新name和age字段：

INSERT INTO students (sid, name, age) VALUES (1, 'Alice', 20)

ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

如果表中已经有主键为1的行，那么这个INSERT语句将会更新该行的name字段为'Alice'，age字段为20。

4. 插入多条数据，如果主键已存在，则分别更新name和age字段：

INSERT INTO students (sid, name, age) VALUES (1, 'Alice', 20), (2, 'Bob',

22), (3, 'Charlie', 19)

ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age);

如果表中已经有主键为1的行，那么更新该行的name字段为'Alice'，age字段为20；如果表中已经有主键为2的行，那么更新该行的name字段为'Bob'，age字段为22；如果表中已经有主键为3的行，那么更新该行的name字段为'Charlie'，age字段为19。

需要注意的是，ON DUPLICATE KEY UPDATE语句中的VALUES函数表示插入值列表中的新值。在更新操作中，可以使用VALUES函数来引用插入语句中的新值，以便用于更新操作。

当使用ON DUPLICATE KEY UPDATE语句时，需要注意以下几点：

1. 需要在表中定义主键或唯一索引。如果没有主键或唯一索引，将无法使用ON

DUPLICATE KEY UPDATE语句。

2. 插入的行的主键或唯一索引必须与表中已有的行发生冲突，才会执行更新操作。如果插入的行的主键或唯一索引在表中不存在，那么将会插入一条新的行。

3. 更新操作的列和值由SET子句定义。可以在SET子句中使用VALUES函数来引用插入语句中的新值。

4. 如果插入语句中的新值与表中已有的值相同，那么不会触发更新操作。仅当插入的新值与已有的值不同，才会执行更新操作。

使用ON DUPLICATE KEY UPDATE语句可以有效地处理重复数据的情况，避免插入重复数据，提高数据插入的效率。在进行批量插入时，可以先尝试执行一次INSERT语句，如果发生了主键或唯一索引冲突，再执行更新操作，这样可以有效地减少数据库的访问次数。

总结：

ON DUPLICATE KEY UPDATE是一种在插入数据时，如果发生主键或唯一索引冲突，则执行更新操作的特殊语句。它的用法需要在表中定义主键或唯一索引，并指定需要更新的列和值。在执行插入语句时，如果插入的行的主键或唯一索引与表中已有的行发生冲突，将会执行更新操作。使用ON DUPLICATE KEY

UPDATE语句可以避免插入重复数据，提高数据插入的效率。