愚蠢但简单的例子:假设我有一个表"项目",我在其中保存收到的项目的总计。
Item_Name Items_In_Stock
项目名称在这里是主键。当我收到数量为 X 的项目 A 时,我如何实现以下目标。
如果该商品不存在,我为物料 A 插入一个新的 recored 并将库存商品设置为 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' )
我的问题是我的实际表中有多列。在更新部分编写多个选择语句是个好主意吗?
当然,我可以在代码中做到这一点,但是有更好的方法吗?
正如我的评论中所述,您不必执行子选择来引用导致 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
请记住,大多数事情都非常简单,如果您发现自己过于复杂的事情应该很简单,那么您很可能以错误的方式做:)
尽管 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 );
尝试再次执行相同的操作,您将收到重复键错误,因为cellId
和entityRowId
是相同的:
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
。
你可以从这个例子中得到想法:
假设您要添加用户明智的七天数据
它应该对用户 id 和 day 具有唯一值,例如
UNIQUE KEY `seven_day` (`userid`,`day`)
这是表格
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 |
+--------+-----+-------+-------+
这是更新插入的语法
INSERT INTO `{TABLE}` (`{PKCOLUMN}`, `{COLUMN}`) VALUES (:value)
ON DUPLICATE KEY UPDATE `{COLUMN}` = :value_dup';
如果你在满足单性性的列上有 PK 列或唯一索引的值,则可以使用 INSERT IGNORE
、 INSERT INTO ... ON DUPLICATE
或 REPLACE
带有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
(...);
更新插入的示例
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