php - CSV 到 Mysql 导入脚本以匹配字段

Question

是否有这样的脚本或将数据从 CSV 导入 mysql 的方法，可以匹配第一个字段，而不是覆盖数据，只会更新行中缺失的字段或值？希望我已经解释清楚了!

例子:

1,john,doe,programmer
2,jane,doe,accountant
3,judy,doe,manager
1,john,doe,cto

假设第一个字段是一个 ID，我希望脚本在记录不存在时插入，但在 ID 已存在时更新，因此 John Doe 首先作为程序员插入，然后更新为 CTO。

Answer 1

来自MySQL reference manual , 在 LOAD DATA section :

The REPLACE and IGNORE keywords control handling of input rows that duplicate existing rows on unique key values:

If you specify REPLACE, input rows replace existing rows.
In other words, rows that have the same value for a primary key or unique index
as an existing row. See Section 12.2.7, “REPLACE Syntax”.

If you specify IGNORE, input rows that duplicate an existing row
on a unique key value are skipped. If you do not specify either
option, the behavior depends on whether the LOCAL keyword is
specified. Without LOCAL, an error occurs when a duplicate key value
is found, and the rest of the text file is ignored. With LOCAL, the
default behavior is the same as if IGNORE is specified; this is
because the server has no way to stop transmission of the file in the
middle of the operation.

所以我相信，如果您将存储名称的字段设置为主键或唯一索引，您应该能够:

LOAD DATA LOCAL INFILE 'file.csv'
REPLACE INTO TABLE yourtable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, name, surname, etc);

---

更新:

刚刚找到了更多关于该主题的资源:

mysql duplicates with LOAD DATA INFILE

http://support.modwest.com/content/6/253/en/how-do-i-import-delimited-data-into-mysql.html

http://codeinthehole.com/archives/35-How-to-sync-a-MySQL-table-between-two-remote-databases..html