MySQL INSERT ON DUPLICATE KEY UPDATE
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL INSERT ON DUPLICATE KEY UPDATE,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3170字,纯文字阅读大概需要5分钟。
内容图文
来源:https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/
Introduction to the MySQL INSERT ON DUPLICATE KEY UPDATE
statement
The INSERT ON DUPLICATE KEY UPDATE
is a MySQL’s extension to the SQL standard’s INSERT
statement.
When you insert a new row into a table if the row causes a duplicate in UNIQUE
index or PRIMARY KEY
, MySQL will issue an error.
However, if you specify the ON DUPLICATE KEY UPDATE
option in the INSERT
statement, MySQL will update the existing row with the new values instead.
The syntax of INSERT ON DUPLICATE KEY UPDATE
statement is as follows:
INSERT INTO table (column_list)
VALUES (value_list)
ON DUPLICATE KEY UPDATE
c1 = v1,
c2 = v2,
...;
The only addition to the INSERT
statement is the ON DUPLICATE KEY UPDATE
clause where you specify a list of column-value-pair assignments in case of duplicate.
Basically, the statement first tries to insert a new row into the table. If a duplicate error occurs, it will update the existing row with the value specified in the ON DUPLICATE KEY UPDATE
clause.
MySQL returns the number of affected-rows based on the action it performs:
- If the new row is inserted, the number of affected-rows is 1.
- If the existing row is updated, the number of affected-rows is 2.
- If the existing row is updated using its current values, the number of affected-rows is 0.
To use the values from the INSERT
clause in the DUPLICATE KEY UPDATE
clause, you use the VALUES()
function as follows:
INSERT INTO table_name(c1)
VALUES(c1)
ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + 1;
The statement above sets the value of the c1
to its current value specified by the expression VALUES(c1)
plus 1 if there is a duplicate in UNIQUE
index or PRIMARY KEY
.
MySQL INSERT ON DUPLICATE KEY UPDATE
example
Let’s take a look at an example of using the INSERT ON DUPLICATE KEY UPDATE
to understand how it works.
First, create a table named devices
to store the network devices.
CREATE TABLE devices (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)
);
Next, insert rows into the devices
table.
INSERT INTO devices(name)
VALUES('Router F1'),('Switch 1'),('Switch 2');
Then, query the data from the devices
table to verify the insert:
SELECT
id,
name
FROM
devices;
Now, we have three rows in the devices
table.
After that, insert one more row into the devices
table.
INSERT INTO
devices(name)
VALUES
('Printer')
ON DUPLICATE KEY UPDATE name = 'Printer';
Because there is no duplicate, MySQL inserts a new row into the devices
table. The statement above has the same effect as the following statement:
INSERT INTO devices(name)
VALUES ('Printer');
Finally, insert a row with a duplicate value in the id
column.
INSERT INTO devices(id,name)
VALUES
(4,'Printer')
ON DUPLICATE KEY UPDATE name = 'Central Printer';
MySQL issues the following message:
2 row(s) affected
Because a row with id 4 already exists in the devices
table, the statement updates the name from Printer
to Central Printer
.
In this tutorial, you have learned how to insert or update data in a table using the ON DUPLICATE KEY UPDATE
option of the INSERT
statement.
原文:https://www.cnblogs.com/john123/p/12232329.html
内容总结
以上是互联网集市为您收集整理的MySQL INSERT ON DUPLICATE KEY UPDATE全部内容,希望文章能够帮你解决MySQL INSERT ON DUPLICATE KEY UPDATE所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。