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.

这篇关于MySQL LOAD DATA INFILE 和 ON DUPLICATE KEY UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！