mysql基于查询结果批量插入和更新

最新推荐文章于 2024-09-11 12:06:01 发布

转载最新推荐文章于 2024-09-11 12:06:01 发布 · 9.4k 阅读

sql/db 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一种使用临时表解决数据整合问题的方法，包括创建临时表、加载数据、更新现有记录和插入缺失记录的过程。

解决办法是用临时表：

One possible way to do this is to create a temporary table, insert the data into that, and then do 1 query with a join to insert the records that don't exist followed by and update to the fields that do exist. The basics would be something like this.

CREATE TABLE MyTable_Temp LIKE MyTable;

LOAD DATA INFILE..... INTO MyTable_Temp;

UPDATE MyTable INNER JOIN 
MyTable_Temp
ON MyTable.ID=MyTable_Temp.ID
SET MyTable.Col1=MyTable_Temp.Col1, MyTable.Col2=MyTable_Temp.Col2.....;

INSERT INTO MyTable(ID,Col1,Col2,...)
SELECT ID,Col1,Col2,... 
FROM MyTable_Temp
LEFT JOIN MyTable 
ON MyTable_Temp.ID = MyTable.ID
WHERE myTable.ID IS NULL;

DROP TABLE MyTable_Temp;

引用自 Kibbee