替代MySQL加载本地信息替换以更新表

Question

如果我有这个档案：

1  someval1
2  someval2
...

MySQL中的这张表

-------------
|id | value |
|-----------|
| 1 | NULL  |
| 2 | NULL  | 
...

我的目标是将表的右列中的值设置为csv文件右侧列中的值，只要id匹配。我可以使用这个查询(但我不想使用)

LOAD DATA 
  LOCAL INFILE 'file'
  REPLACE
  INTO TABLE myTable
  FIELDS TERMINATED BY ' '
  LINES TERMINATED BY '\n'
  (id, value);

我不想使用替换的原因是，根据MySQL文档，每当主键上有匹配项(在本例中为'id')时，这将删除整行，并重新插入包含文件中信息的行。我假设这意味着，在将来的任何时候，如果我要向这个表中添加一个列，我必须确保我在这个查询中使用的任何文件都包含可能被更新的每一行的所有列值；否则，非目标列将被删除。

除了使用本地文件更新表(或者更多)之外，没有更好的替代方法吗？输入文件中的列包含需要匹配的主键？

Answer 1

一种可能的办法是：

使用LOAD DATA INFILE将CSV数据加载到临时表中。

然后，使用INSERT ...SELECT ...ON DUPLICATE KEY UPDATE执行更新。

编辑，有一个经过工作和测试的示例：

正如OP所指出的，LOAD DATA INFILE的问题在于，LOAD DATA INFILE在速度上得到了优化，在处理传入数据中的冲突时非常愚蠢。

它只有两个选择：REPLACE或IGNORE。REPLACE是一个真正的解决方案的大锤。当找到一个重复的键时，它只需删除原始行并插入一个新的行。这里的问题是，现有行中没有包含在CSV文件中的任何数据都会丢失。

另一种选择是“忽略”，但这并不比这更好。它不影响现有的数据，但是当它完成时，就无法判断CSV文件中哪些行已经应用，哪些行没有应用。

LOAD DATA INFILE最擅长的是:快速批量上传新数据。对于现有数据的更新，它可能有其用途，但它并不是一个工具。

我们可以用我们最喜欢的语言(或者我们使用的语言，即使不是我们最喜欢的语言-- PHP任何人？)编写一个例程，解析CSV文件并使用INSERT...ON DUPLICATE UPDATE...查询应用更新，但是这引入了额外的脚本级别，并且拒绝了MySQL优化它所做的事情的机会。在任何情况下，大量单个INSERT查询都是远远不够有效的。我们可以使用允许多个值集的INSERT语法，但是对于大型导入，这可能会违反最大请求大小限制。

解决方案是使用两阶段方法，并允许MySQL做它最擅长的事情。

使用LOAD DATA INFILE将CSV文件导入临时表，然后使用INSERT...SELECT...ON DUPLICATE KEY UPDATE以更智能的方式处理更新过程。

假设我们有一张这样的桌子：

CREATE TABLE `test`.`bestmate` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `age` INT NULL,
  `bestmate` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `id_UNIQUE` (`id` ASC));

我们希望定期从其他来源导入CSV数据，如下所示：

id, name, age
1, fred, 16
2, alan, 22
3, george, 26

我们的bestmate列是由我们的用户在线更新的，所以我们在CSV文件中没有这些信息，我们不想丢失它。

首先，创建一个临时表来保存CSV数据并导入该文件。

drop table if exists `csvTable` ;

CREATE TEMPORARY TABLE `csvTable`
  (`id` INT NOT NULL,
  `name` VARCHAR(45) NULL,
  `age` INT NULL);

LOAD DATA LOCAL INFILE 'c:\\users.csv' INTO TABLE `csvTable`
  FIELDS TERMINATED BY ','
   OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\r\n'
  IGNORE 1 lines;

现在，我们可以在一个命令中插入或更新bestmate表中的行，将表中的任何额外数据保持不变。

INSERT `bestmate` (`id`,`name`,`age`) SELECT * from `csvTable` 
  ON DUPLICATE KEY UPDATE
    `bestmate`.`name` = `csvTable`.`name`,
    `bestmate`.`age` = `csvTable`.`age`;

最后，收拾好我们自己。

drop table if exists `csvTable` ;

Answer 2

可以从file中生成UPDATE语句，然后执行它们：

    awk "{ print \"UPDATE TABLE myTable SET value = '\" \$2 \"' WHERE id ='\" \$1 \"';\" }" < file | mysql db