mysql 如何执行UPSERT,以便在更新部分中使用新值和旧值

lmyy7pcs  于 2023-11-16  发布在  Mysql
关注(0)|答案(6)|浏览(120)

一个愚蠢但简单的例子:假设我有一个表Item,我在其中保存了我收到的物品的总数。有两列:Item_Name(主键)和Items_In_Stock(金额)。
当我收到数量为X的物品A时:

  • 如果该项目不存在,则为项目A插入一条新记录,并将库存项目设置为X。
  • 如果存在物料A的记录,其中库存物料为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语句是个好主意吗?
当然我可以用代码来做,但有更好的方法吗?

ws51t4hk

ws51t4hk1#

正如我在评论中提到的,你不必做子选择来引用导致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

字符串
记住,大多数事情都很简单,如果你发现自己把应该简单的事情复杂化了,那么你很可能做错了:)

2mbi3lxu

2mbi3lxu2#

虽然Michael的答案是正确的,但你需要知道更多的知识来编程地执行upsert:
首先,创建表并指定要在哪些列上创建唯一索引:

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

字符串
然后在其中插入一些值:

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


再次尝试执行同样的操作,你会得到一个重复键错误,因为cellIdentityRowId是相同的:

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


关键字"id_ce"的重复条目"1 - 199"
这就是为什么我们使用upsert命令:

INSERT INTO Cell ( cellId, attributeId, entityRowId, value)
VALUES( 1, 6, 199, 300.0 )
ON DUPLICATE KEY UPDATE `value` = `value` + VALUES(`value`)


此命令将已经存在的值1.0作为值,并执行value = value + 300.0
因此,即使在执行上述命令后,表中也只有一行,值为301.0

dgtucam1

dgtucam13#

你可以从这个例子中得到启发:

假设您要添加用户明智的七天数据
它应该有唯一的值为userid和天一样

UNIQUE KEY `seven_day` (`userid`,`day`)

字符串
这是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`)
);


你的问题是

INSERT INTO table_name (userid,day,open,close) 
    VALUES ('val1', 'val2','val3','val4') 
        ON DUPLICATE KEY UPDATE open='val3', close='val4';


范例:

<?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);
}
?>


您的数据将在表中:

+--------+-----+-------+-------+
| userid | day | open  | close |
+--------+-----+-------+-------+
| 1001   | sat | 03.01 | 33.01 |
| 1001   | sun | 02.01 | 22.01 |
| 1002   | mon | 07.01 | 07.01 |
+--------+-----+-------+-------+

pbwdgjma

pbwdgjma4#

这是upsert的语法

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

字符串

vhipe2zx

vhipe2zx5#

如果PK列的值或列上的唯一索引满足唯一性,则可以使用INSERT IGNOREINSERT INTO ... ON DUPLICATEREPLACE

INSERT IGNORE示例

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

字符串

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;

Replace示例

REPLACE INTO table1
    (ID, serverID, channelID, channelROLE)
VALUES
    (...);

rqdpfwrv

rqdpfwrv6#

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

字符串

相关问题