MySQL LOAD DATA INFILE 和 ON DUPLICATE KEY UPDATE
为了将大量数据加载到 MySQL，LOAD DATA INFILE 是迄今为止最快的选择.不幸的是，虽然这可以以 INSERT IGNORE 或 REPLACE 的工作方式使用，但目前不支持 ON DUPLICATE KEY UPDATE.
For loading huge amounts of data into MySQL, LOAD DATA INFILE is 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.
然而，ON DUPLICATE KEY UPDATE 比 REPLACE 有优势.后者在存在重复项时执行删除和插入操作.这给密钥管理带来了开销.此外，自动增量 ID 在替换时不会保持不变.
However, ON DUPLICATE KEY UPDATE has 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.
如何在使用 LOAD DATA INFILE 时模拟 ON DUPLICATE KEY UPDATE?
How can ON DUPLICATE KEY UPDATE be emulated when using LOAD DATA INFILE?
推荐答案
这些步骤可用于模拟此功能:
These steps can be used to emulate this functionality:
1) 创建一个新的临时表.
1) Create a new temporary table.
CREATE TEMPORARY TABLE temporary_table LIKE target_table;
2) 或者，从临时表中删除所有索引以加快速度.
2) Optionally, drop all indices from the temporary table to speed things up.
SHOW INDEX FROM temporary_table;
DROP INDEX `PRIMARY` ON temporary_table;
DROP INDEX `some_other_index` ON temporary_table;
3) 将 CSV 加载到临时表中
3) Load the CSV into the temporary table
LOAD DATA INFILE 'your_file.csv'
INTO TABLE temporary_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(field1, field2);
4) 使用 ON DUPLICATE KEY UPDATE 复制数据
4) Copy the data using 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) 删除临时表
DROP TEMPORARY TABLE temporary_table;
使用 SHOW INDEX FROM 和 SHOW COLUMNS FROM 可以为任何给定的表自动执行此过程.
Using SHOW INDEX FROM and SHOW COLUMNS FROM this process can be automated for any given table.
相关文章