你可以用Replace代替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符号来使用${}获取它的值，否则它将被视为数组中的字符串。