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

解决办法是用临时表：

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