具有ON DUPLICATE KEY UPDATE的MySQL LOAD DATA INFILE [英] MySQL LOAD DATA INFILE with ON DUPLICATE KEY UPDATE

问题描述

要在MySQL中加载大量数据，请加载数据文件是迄今为止最快的选择.不幸的是，尽管可以通过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具有优势.当存在重复项时，后者执行删除和插入操作.这带来了密钥管理的开销.另外，自动增量编号在替换时不会保持不变.

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.

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