如何执行 UPSERT，使我可以使用新的和旧的值更新部分

愚蠢但简单的例子: 假设我有一个表“ Item”，其中保存了收到的项目的总数。

Item_Name              Items_In_Stock

项目名称是这里的主键。如何实现以下目标时，我每次收到物品 A 的数量 X。

如果该商品不存在，我将插入一个新的记录的商品 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' )

我的问题是在实际的表中有多个列。在更新部分中编写多个 select 语句是一个好主意吗？

当然我可以用代码来做，但是有没有更好的方法呢？

91802 次浏览
小开
最佳答案

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:

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 :)

小开

You can get idea from this example:

Suppose you want to add user wise seven days data

It should have unique value for userid and 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 |
+--------+-----+-------+-------+
小开

This is the syntax for an upsert

INSERT INTO `{TABLE}` (`{PKCOLUMN}`, `{COLUMN}`) VALUES (:value)
ON DUPLICATE KEY UPDATE `{COLUMN}` = :value_dup';
小开

Although Michael's answer is the right one, you need to know a bit more to do the upsert programmatically:

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 cellId and entityRowId are same:

INSERT INTO Cell VALUES( 1, 6, 199, 1.0 );

Duplicate entry '1-199' for key 'id_ce'

That's why we use the upsert command:

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.0 that's already there as value and does a 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.

小开

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

Example with INSERT IGNORE

INSERT IGNORE INTO Table1
(ID, serverID, channelID, channelROLE)
VALUES
(....);

Example with 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 INTO table1
(ID, serverID, channelID, channelROLE)
VALUES
(...);
小开

Example for upsert

INSERT INTO table1 (col1, col2, col3)
VALUES ($1, $2, $3)
ON CONFLICT (col1)
DO
UPDATE
SET col2 = $2, col3 = $3
WHERE col1 = $1
RETURNING col1

提交答案