MySQL 尝试使用 REPLACE 和 AUTO_INCREMENT 执行 LOAD DATA INFILE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10801302/
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
Trying to do LOAD DATA INFILE with REPLACE and AUTO_INCREMENT
提问by jonasMcFerreira
I am trying to load a file onto a MySQL database, having the primary key auto_incremented and I would like the data to be updated if i find any duplicate rows. However, the REPLACE keywords only works on primary key, which is auto generated so i'm stuck.
我正在尝试将文件加载到 MySQL 数据库中，主键为 auto_incremented，如果我发现任何重复的行，我希望更新数据。但是，REPLACE 关键字仅适用于自动生成的主键，所以我被卡住了。
how to be able to have a table with an ID that auto_increments and at the same time to be able to insert/update data from a file using LOAD DATA INFILE?
如何能够拥有一个 ID 自动递增的表，同时能够使用 LOAD DATA INFILE 从文件中插入/更新数据？
Here is the table
这是桌子
CREATE TABLE `oxygen_domain`.`TEST` (
`TEST_ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
`VALUE` varchar(255) DEFAULT NULL,
PRIMARY KEY (`TEST_ID`,`NAME`,`VALUE`)
)
and here is the command
这是命令
LOAD DATA LOCAL INFILE 'C:/testData.txt'
REPLACE
INTO TABLE TEST
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);
and here is the sample data
这是示例数据
ignored name, ignored value
name1,value1
name2,value2
name3,value3
The wanted ending result after running the command above multiple times withthe above data is
使用上面的数据多次运行上面的命令后想要的最终结果是
|TEST_ID |NAME |VALUE|
1, 'name1', 'value1'
2, 'name2', 'value2'
3, 'name3', 'value3'
回答by RolandoMySQLDBA
OBSERVATION #1
观察#1
You should not do REPLACEbecause it is a mechanical DELETEand INSERT.
你不应该这样做，REPLACE因为它是一个机械DELETE和INSERT。
As the MySQL Documentationsays about REPLACE
正如MySQL 文档所说的关于 REPLACE
Paragraph 2
第 2 段
REPLACE is a MySQL extension to the SQL standard. It either inserts, or deletes and inserts. For another MySQL extension to standard SQL—that either inserts or updates—see Section 13.2.5.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”.
REPLACE 是 SQL 标准的 MySQL 扩展。它要么插入，要么删除和插入。对于标准 SQL 的另一个 MySQL 扩展（插入或更新），请参阅第 13.2.5.3 节，“INSERT ... ON DUPLICATE KEY UPDATE Syntax”。
Paragraph 5
第 5 段
To use REPLACE, you must have both the INSERT and DELETE privileges for the table.
要使用 REPLACE，您必须同时拥有表的 INSERT 和 DELETE 权限。
Using REPLACE will throw away established values for TEST_ID that cannot automatically be reused.
使用 REPLACE 将丢弃无法自动重用的 TEST_ID 的已建立值。
OBSERVATION #2
观察#2
The table layout will not support trapping of duplicate keys
表格布局将不支持捕获重复键
If a name is unique, the table should be laid out like this
如果名称是唯一的，则该表应该像这样布置
LAYOUT #1
布局 #1
CREATE TABLE `oxygen_domain`.`TEST` (
`TEST_ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
`VALUE` varchar(255) DEFAULT NULL,
PRIMARY KEY (`TEST_ID`),
KEY (`NAME`)
)
If a name allows multiple values, the table should be laid out like this
如果一个名字允许多个值，表格应该这样布局
LAYOUT #2
布局 #2
CREATE TABLE `oxygen_domain`.`TEST` (
`TEST_ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
`VALUE` varchar(255) DEFAULT NULL,
PRIMARY KEY (`TEST_ID`),
KEY (`NAME`,`VALUE`)
)
PROPOSED SOLUTION
建议的解决方案
Use a temp table to catch everything. Then, perform a big INSERT from the temp table based on layout
使用临时表来捕获所有内容。然后，根据布局从临时表中执行一个大的 INSERT
LAYOUT #1
布局 #1
Replace the VALUEfor a Duplicate NAME
替换VALUE为重复NAME
USE oxygen_domain
DROP TABLE IF EXISTS `TESTLOAD`;
CREATE TABLE `TESTLOAD` SELECT NAME,VALUE FROM TEST WHERE 1=2;
LOAD DATA LOCAL INFILE 'C:/testData.txt'
INTO TABLE `TESTLOAD`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);
INSERT INTO `TEST` (NAME, VALUE)
SELECT NAME, VALUE FROM `TESTLOAD`
ON DUPLICATE KEY UPDATE VALUE = VALUES(VALUE);
DROP TABLE `TESTLOAD`;
LAYOUT #2
布局 #2
Ignore Duplicate (NAME,VALUE)rows
忽略重复(NAME,VALUE)行
USE oxygen_domain
DROP TABLE IF EXISTS `TESTLOAD`;
CREATE TABLE `TESTLOAD` SELECT NAME,VALUE FROM TEST WHERE 1=2;
LOAD DATA LOCAL INFILE 'C:/testData.txt'
INTO TABLE `TESTLOAD`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);
INSERT IGNORE INTO `TEST` (NAME, VALUE)
SELECT NAME, VALUE FROM `TESTLOAD`;
DROP TABLE `TESTLOAD`;
Update
更新
if we need to avoid the creating and dropping of the table each time. we can TRUNCATE TRUNCATEthe table before or after using INSERT...INTO statement. Therefore, we do not have to create the table next time.
如果我们需要避免每次创建和删除表。我们可以在使用 INSERT...INTO 语句之前或之后TRUNCATE TRUNCATE表。因此，我们下次不必创建表。
回答by MagyaDEV
Create unique index on NAME & VALUE and use IGNORE instead of REPLACE:
在 NAME & VALUE 上创建唯一索引并使用 IGNORE 而不是 REPLACE：
LOAD DATA LOCAL INFILE 'C:/testData.txt'
IGNORE
INTO TABLE `TEST`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);
回答by Sergi
Just delete the REPLACE from the LOAD DATA, execute it twice and you will get the desired result:
只需从 LOAD DATA 中删除 REPLACE，执行两次，您将获得所需的结果：
LOAD DATA LOCAL INFILE 'C:/testData.txt'
INTO TABLE TEST
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);
I suppose you don't really want the desired result you've posted, do you?
我想你真的不想要你发布的想要的结果，是吗？
