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 UPDATEREPLACE 有优势.后者在存在重复项时执行删除和插入操作.这给密钥管理带来了开销.此外,自动增量 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 FROMSHOW COLUMNS FROM 可以为任何给定的表自动执行此过程.

Using SHOW INDEX FROM and SHOW COLUMNS FROM this process can be automated for any given table.

相关文章