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_TempON MyTable.ID=MyTable_Temp.IDSET MyTable.Col1=MyTable_Temp.Col1, MyTable.Col2=MyTable_Temp.Col2.....;INSERT INTO MyTable(ID,Col1,Col2,...)SELECT ID,Col1,Col2,... FROM MyTable_TempLEFT JOIN MyTable ON MyTable_Temp.ID = MyTable.IDWHERE myTable.ID IS NULL;DROP TABLE MyTable_Temp;

引用自Kibbee