MySQL LOAD DATA INFILE 和 ON DUPLICATE KEY UPDATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15271202/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE
提问by Jan
For loading huge amounts of data into MySQL, LOAD DATA INFILEis by far the fastest option. Unfortunately, while this can be used in a way INSERT IGNORE or REPLACE works, ON DUPLICATE KEY UPDATE is not currently supported.
对于将大量数据加载到 MySQL 中，LOAD DATA INFILE是迄今为止最快的选择。不幸的是，虽然这可以以 INSERT IGNORE 或 REPLACE 工作的方式使用，但当前不支持 ON DUPLICATE KEY UPDATE。
However, ON DUPLICATE KEY UPDATEhas advantages over REPLACE. The latter does a delete and an insert when a duplicate exists. This brings overhead for key management. Also, autoincrement ids will not stay the same on a replace.
但是，ON DUPLICATE KEY UPDATE具有优于REPLACE. 后者在存在重复项时执行删除和插入操作。这给密钥管理带来了开销。此外，自动增量 ID 在替换时不会保持不变。
How can ON DUPLICATE KEY UPDATEbe emulated when using LOAD DATA INFILE?
ON DUPLICATE KEY UPDATE使用 LOAD DATA INFILE 时如何模拟？
回答by Jan
These steps can be used to emulate this functionality:
这些步骤可用于模拟此功能：
1) Create a new temporary table.
1) 创建一个新的临时表。
CREATE TEMPORARY TABLE temporary_table LIKE target_table;
2) Optionally, drop all indices from the temporary table to speed things up.
2) 或者，从临时表中删除所有索引以加快速度。
SHOW INDEX FROM temporary_table;
DROP INDEX `PRIMARY` ON temporary_table;
DROP INDEX `some_other_index` ON temporary_table;
3) Load the CSV into the temporary table
3) 将 CSV 加载到临时表中
LOAD DATA INFILE 'your_file.csv'
INTO TABLE temporary_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(field1, field2);
4) Copy the data using ON DUPLICATE KEY UPDATE
4) 使用 ON DUPLICATE KEY UPDATE 复制数据
SHOW COLUMNS FROM target_table;
INSERT INTO target_table
SELECT * FROM temporary_table
ON DUPLICATE KEY UPDATE field1 = VALUES(field1), field2 = VALUES(field2);
5) Remove the temporary table
5）删除临时表
DROP TEMPORARY TABLE temporary_table;
Using SHOW INDEX FROMand SHOW COLUMNS FROMthis process can be automated for any given table.
对于任何给定的表，使用SHOW INDEX FROM和SHOW COLUMNS FROM这个过程可以自动化。
回答by Suneet Khurana
we can replace first (two steps) with below single query in the procedure shared by (Jan).
我们可以在 (Jan) 共享的过程中用以下单个查询替换第一步（两个步骤）。
1) and 2) we can create new table with same reference structure and without any indexes.
1) 和 2) 我们可以创建具有相同引用结构且没有任何索引的新表。
CREATE TEMPORARY TABLE temporary_table SELECT * FROM target_table WHERE 1=0;
创建临时表临时表 SELECT * FROM target_table WHERE 1=0;
Instead of..
代替..
1) Create a new temporary table.
1) 创建一个新的临时表。
CREATE TEMPORARY TABLE temporary_table LIKE target_table;
CREATE TEMPORARY TABLE 临时表 LIKE target_table;
2) Optionally, drop all indices from the temporary table to speed things up.
2) 或者，从临时表中删除所有索引以加快速度。
SHOW INDEX FROM temporary_table;
DROP INDEX PRIMARYON temporary_table;
DROP INDEX some_other_indexON temporary_table;
从临时表显示索引；DROP INDEX PRIMARYON 临时表；DROP INDEX some_other_indexON 临时表；
