MySQL对重复键更新在一个查询中插入多行

我有一个SQL查询，我想在单个查询中插入多行。所以我用了这样的东西:

$sql = "INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29)";


mysql_query( $sql, $conn );

问题是，当我执行这个查询时，我想检查是否应该检查UNIQUE键(不是PRIMARY KEY)，例如上面的'name'，如果这样的'name'已经存在，则应该更新对应的整行，否则将插入。

例如，在下面的例子中，如果'Katrina'已经存在于数据库中，那么不管字段的数量如何，整行都应该被更新。同样，如果'Samia'不存在，则应该插入该行。

我想到用:

INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29) ON DUPLICATE KEY UPDATE

这是陷阱。我被困住了，不知道该怎么做。我一次有多行要插入/更新。请给我指路。谢谢。

148704 次浏览
小开

你可以用取代代替INSERT…重复密钥更新。

小开
最佳答案

从MySQL 8.0.19开始，你可以为该行使用别名(参见参考)。

INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29)
AS new
ON DUPLICATE KEY UPDATE
age = new.age
...

对于早期版本，使用关键字VALUES (参见参考，在MySQL 8.0.20中已弃用)。

INSERT INTO beautiful (name, age)
VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29)
ON DUPLICATE KEY UPDATE
age = VALUES(age),
...
小开

插入…ON DUPLICATE KEY UPDATE只适用于MYSQL，不适用于SQL Server。

对于SQL server，解决这个问题的方法是首先声明一个临时表，向该临时表插入值，然后使用MERGE

是这样的:

declare @Source table
(
name varchar(30),
age decimal(23,0)
)


insert into @Source VALUES
('Helen', 24),
('Katrina', 21),
('Samia', 22),
('Hui Ling', 25),
('Yumie', 29);




MERGE beautiful  AS Tg
using  @source as Sc
on tg.namet=sc.name


when matched then update
set tg.age=sc.age


when not matched then
insert (name, age) VALUES
(SC.name, sc.age);
小开

我使用jdbi的BindBeanList寻找相同的行为，发现语法与上面Peter Lang的答案完全相同。如果有人遇到这个问题，这是我的代码:

  @SqlUpdate("INSERT INTO table_one (col_one, col_two) VALUES <beans> ON DUPLICATE KEY UPDATE col_one=VALUES(col_one), col_two=VALUES(col_two)")
void insertBeans(@BindBeanList(value = "beans", propertyNames = {"colOne", "colTwo"}) List<Beans> beans);

需要注意的一个关键细节是，您在@BindBeanList注释中指定的propertyName与您在更新时传递给VALUES()调用的列名不相同。

小开

让我在数据库中有2个表:(1)品牌(2)brand_item。

DROP TABLE IF EXISTS `brand`;
CREATE TABLE `brand` (
`brand_id` int(11) NOT NULL AUTO_INCREMENT,
`brand_key` varchar(255) DEFAULT NULL,
`brand_name` varchar(2048) DEFAULT NULL,
`disclaimer` varchar(2048) DEFAULT NULL,
`description` varchar(2048) DEFAULT NULL,
`short_description` varchar(2048) DEFAULT NULL,
`terms` varchar(2048) DEFAULT NULL,
`created_date` datetime DEFAULT NULL,
`last_updated_date` datetime DEFAULT NULL,
`always_show_disclaimer` varchar(2048) DEFAULT NULL,
`disclaimer_instructions` varchar(2048) DEFAULT NULL,
`display_instructions` varchar(2048) DEFAULT NULL,
`terms_and_conditions_instructions` varchar(2048) DEFAULT NULL,
`image_urls` json DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`feature` boolean DEFAULT '1',
PRIMARY KEY (`brand_id`),
UNIQUE KEY `brand_key` (`brand_key`)
) ENGINE=InnoDB AUTO_INCREMENT=91 DEFAULT CHARSET=latin1;
 

DROP TABLE IF EXISTS `brand_item`;
CREATE TABLE `brand_item` (
`brand_id` int(11) DEFAULT NULL,
`utid` varchar(255) DEFAULT NULL,
`reward_name` varchar(2048) DEFAULT NULL,
`currency_code` varchar(2048) DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`value_type` varchar(255) DEFAULT NULL,
`reward_type` varchar(255) DEFAULT NULL,
`is_whole_amount_value_required` varchar(255) DEFAULT NULL,
`face_value` double(16,2) DEFAULT '0.00',
`min_value` double(16,2) DEFAULT '0.00',
`max_value` double(16,2) DEFAULT '0.00',
`created_date` datetime DEFAULT NULL,
`last_updated_date` datetime DEFAULT NULL,
`redemption_instructions` varchar(2048) DEFAULT NULL,
`countries` varchar(2048) DEFAULT NULL,
UNIQUE KEY `utid` (`utid`),
KEY `brand_id` (`brand_id`),
CONSTRAINT `brand_item_ibfk_1` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Javascript代码插入品牌表: . ON DUPLICATE KEY UPDATE在INSERT查询中处理

async function addToDB(event, brandDetails) {
return new Promise((resolve, reject) => {


let values = `[brandDetails.key,brandDetails.name,brandDetails.disclaimer,brandDetails.description,brandDetails.shortDescription,brandDetails.terms,brandDetails.createdDate,brandDetails.lastUpdateDate,brandDetails.alwaysShowDisclaimer,brandDetails.disclaimerInstructions,brandDetails.displayInstructions,brandDetails.termsAndConditionsInstructions,brandDetails.imageUrls,brandDetails.status]`


let query = "INSERT INTO ?? (brand_key,brand_name,disclaimer,description,short_description,terms,created_date,last_updated_date,always_show_disclaimer,       disclaimer_instructions,display_instructions,terms_and_conditions_instructions,image_urls,status) VALUES (?) ON DUPLICATE KEY UPDATE brand_key=?, brand_name=?, disclaimer=?, description=?, short_description=?, terms=?, created_date=?,last_updated_date=?, always_show_disclaimer=?, disclaimer_instructions=?,\ display_instructions=?,terms_and_conditions_instructions=?, image_urls=?, status=?";


MySQLController.executeQuery(event,query, [BRAND_TABLE, values, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7],values[8],values[9],values[10],values[11],values[12],values[13],values[14]] )
.then((res)=>{
console.log("Success in Insertion ", res);
resolve(res);
})
.catch((err)=>{
console.log("error in DB ",err);
reject(err);
})


})}
插入到brand_item表的Javascript代码: . ON DUPLICATE KEY UPDATE在INSERT查询中处理

 async function addToBrandItem(event, fkey, brandItemDetails) {
return new Promise((resolve, reject) => {
      

let values = [fkey, brandItemDetails.utid, brandItemDetails.rewardName, brandItemDetails.currencyCode, brandItemDetails.status, brandItemDetails.valueType, brandItemDetails.rewardType,brandItemDetails.isWholeAmountValueRequired, `${brandItemDetails.faceValue}`, `${brandItemDetails.minValue}`, `${brandItemDetails.maxValue}`, brandItemDetails.createdDate,brandItemDetails.lastUpdateDate,brandItemDetails.redemptionInstructions,`${brandItemDetails.countries}`]
      

let query = "INSERT INTO ?? (brand_id,utid,reward_name,currency_code,status,value_type,reward_type,is_whole_amount_value_required,face_value,min_value,max_value,created_date,last_updated_date,redemption_instructions,countries) VALUES (?)";
  



AuroraController.executeQuery(event,query , [BRAND_ITEM_TABLE, values, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7],values[8],values[9],values[10],values[11],values[12],values[13],values[14]] )
.then((res)=>{
console.log("Success in Insertion in Bran_item", res);
resolve(res);
})
.catch((err)=>{
console.log("error in DB ",err);
reject(err);
})


})}

为了在值数组中保存十进制值，我有一个tick符号来使用${}获取它的值，否则它将被视为数组中的字符串。


提交答案