MySQL 如何执行 UPSERT 以便我可以在更新部分使用新值和旧值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6107752/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to Perform an UPSERT so that I can use both new and old values in update part
提问by WPFAbsoluteNewBie
Stupid but simple example: Assume I have a table 'Item' where I keeps totals of the items that receive.
愚蠢但简单的例子：假设我有一个表“项目”，我在其中保存收到的项目总数。
Item_Name Items_In_Stock
Item name is primary key here. How to i achieve the following when ever I receive item A in quantity X.
项目名称是这里的主键。当我收到数量为 X 的物品 A 时，如何实现以下目标。
If the item does not exist, I insert a new recored for Item A and set the items in stock to X and if there exists a record where items in stock was Y then the new value in items in stock is (X + Y)
如果该项目不存在，我为项目 A 插入一个新记录并将库存项目设置为 X，如果存在库存项目为 Y 的记录，则库存项目的新值为 (X + Y)
INSERT INTO `item`
(`item_name`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`new_items_count` = 27 + (SELECT items_in_stock where item_name = 'A' )
My problem is that i have multiple column in my actual table. Is it a good idea to write multiple select statements in the update part?
我的问题是我的实际表中有多个列。在更新部分编写多个选择语句是个好主意吗？
Of course I can do it in code but is there a better way?
当然我可以用代码来做，但有更好的方法吗？
回答by Michael J.V.
As mentioned in my comment, you don't have to do the subselect to reference to the row that's causing ON DUPLICATE KEY to fire. So, in your example you can use the following:
正如我在评论中提到的，您不必执行子选择来引用导致 ON DUPLICATE KEY 触发的行。因此，在您的示例中，您可以使用以下内容：
INSERT INTO `item`
(`item_name`, items_in_stock)
VALUES( 'A', 27)
ON DUPLICATE KEY UPDATE
`new_items_count` = `new_items_count` + 27
Remember that most things are really simple, if you catch yourself overcomplicating something that should be simple then you are most likely doing it the wrong way :)
请记住，大多数事情都非常简单，如果您发现自己将本应该简单的事情变得过于复杂，那么您很可能以错误的方式做事:)
回答by MaxEcho
You can get idea from this example:
你可以从这个例子中得到想法：
Suppose you want to add user wise seven days data
假设您要添加用户明智的 7 天数据
It should have unique value for userid and day like
它应该对 userid 和 day like 具有独特的价值
UNIQUE KEY `seven_day` (`userid`,`day`)
Here is the table
这是桌子
CREATE TABLE `table_name` (
`userid` char(4) NOT NULL,
`day` char(3) NOT NULL,
`open` char(5) NOT NULL,
`close` char(5) NOT NULL,
UNIQUE KEY `seven_day` (`userid`,`day`)
);
And your query will be
你的查询将是
INSERT INTO table_name (userid,day,open,close)
VALUES ('val1', 'val2','val3','val4')
ON DUPLICATE KEY UPDATE open='val3', close='val4';
Example:
例子：
<?php
//If your data is
$data= array(
'sat'=>array("userid"=>"1001", "open"=>"01.01", "close"=>"11.01"),
'sun'=>array("userid"=>"1001", "open"=>"02.01", "close"=>"22.01"),
'sat'=>array("userid"=>"1001", "open"=>"03.01", "close"=>"33.01"),
'mon'=>array("userid"=>"1002", "open"=>"08.01", "close"=>"08.01"),
'mon'=>array("userid"=>"1002", "open"=>"07.01", "close"=>"07.01")
);
//If you query this in a loop
//$conn = mysql_connect("localhost","root","");
//mysql_select_db("test", $conn);
foreach($data as $day=>$info) {
$sql = "INSERT INTO table_name (userid,day,open,close)
VALUES ('$info[userid]', '$day','$info[open]','$info[close]')
ON DUPLICATE KEY UPDATE open='$info[open]', close='$info[close]'";
mysql_query($sql);
}
?>
Your data will be in table:
您的数据将在表中：
+--------+-----+-------+-------+
| userid | day | open | close |
+--------+-----+-------+-------+
| 1001 | sat | 03.01 | 33.01 |
| 1001 | sun | 02.01 | 22.01 |
| 1002 | mon | 07.01 | 07.01 |
+--------+-----+-------+-------+
回答by Nav
Although Michael's answer is the right one, you need to know a bit more to do the upsert programmatically:
尽管 Michael 的回答是正确的，但您需要了解更多信息才能以编程方式执行 upsert：
First, create your table and specify which columns you want a unique index on:
首先，创建你的表并指定你想要一个唯一索引的列：
CREATE TABLE IF NOT EXISTS Cell (
cellId BIGINT UNSIGNED,
attributeId BIGINT UNSIGNED,
entityRowId BIGINT UNSIGNED,
value DECIMAL(25,5),
UNIQUE KEY `id_ce` (`cellId`,`entityRowId`)
)
Then insert some values into it:
然后在其中插入一些值：
INSERT INTO Cell VALUES( 1, 6, 199, 1.0 );
Try doing the same thing again, and you'll get a duplicate key error, because cellIdand entityRowIdare same:
再次尝试做同样的事情，你会得到一个重复的键错误，因为cellId和entityRowId是相同的：
INSERT INTO Cell VALUES( 1, 6, 199, 1.0 );
Duplicate entry '1-199' for key 'id_ce'
密钥“id_ce”的重复条目“1-199”
That's why we use the upsert command:
这就是我们使用 upsert 命令的原因：
INSERT INTO Cell ( cellId, attributeId, entityRowId, value)
VALUES( 1, 6, 199, 300.0 )
ON DUPLICATE KEY UPDATE `value` = `value` + VALUES(`value`)
This command takes the value 1.0that's already there as value and does a value = value + 300.0.
此命令将1.0已经存在的值作为值并执行value = value + 300.0.
So even after executing the above command, there will be only one row in the table, and the value will be 301.0.
所以即使执行了上面的命令，表中也只有一行，值为301.0.
回答by relipse
This is the syntax for an upsert
这是 upsert 的语法
INSERT INTO `{TABLE}` (`{PKCOLUMN}`, `{COLUMN}`) VALUES (:value)
ON DUPLICATE KEY UPDATE `{COLUMN}` = :value_dup';
回答by Dervi? Kay?mba??o?lu
If you have value for PK Column, or Unique Index on a column which satisfies unicity, You can use INSERT IGNORE, INSERT INTO ... ON DUPLICATE, or REPLACE
如果您有 PK 列的值，或满足唯一性的列上的唯一索引，您可以使用INSERT IGNORE, INSERT INTO ... ON DUPLICATE, 或REPLACE
Example with INSERT IGNORE
示例与 INSERT IGNORE
INSERT IGNORE INTO Table1
(ID, serverID, channelID, channelROLE)
VALUES
(....);
Example with INSERT INTO .. ON DUPLICATE KEY UPDATE
示例与 INSERT INTO .. ON DUPLICATE KEY UPDATE
SET @id = 1,
@serverId = 123545,
@channelId = 512580,
@channelRole = 'john';
INSERT INTO Table1
(ID, serverID, channelID, channelROLE)
VALUES
(@id, @serverId, @channelId, @channelRole)
ON DUPLICATE KEY UPDATE
serverId = @serverId,
channelId = @channelId,
channelRole = @channelRole;
Example with Replace
示例与 Replace
REPLACE INTO table1
(ID, serverID, channelID, channelROLE)
VALUES
(...);
回答by Justin J
Example for upsert
更新插入示例
INSERT INTO table1 (col1, col2, col3)
VALUES (, , )
ON CONFLICT (col1)
DO
UPDATE
SET col2 = , col3 =
WHERE col1 =
RETURNING col1
