MySQL 根据外部 csv 文件的内容更新现有的 sql 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22827402/
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
Update an existing sql table based on content from an external csv file?
提问by Jason
I have a table like this:
我有一张这样的表：
key name address
键名地址
1 aaa **
1 aaa **
2 abc **
2 ABC **
3 bdc **
3 bdc **
4 cda **
4 cda **
5 dda ** ...........
5 dda ** .....
Now I have another flat file (tab-delimited csv file) like this:
现在我有另一个像这样的平面文件（制表符分隔的 csv 文件）：
name phone
姓名电话
abc **
ABC **
dda **
达 **
aaa **
啊啊 **
This flat file will only have part of the entries in the original table. I want to update the table based on the content from the flat file (basically doing a join). Currently I am thinking to wirte some php script to do that. Basically load that flat file as array and have php code do the actual search and update.
此平面文件将仅包含原始表中的部分条目。我想根据平面文件中的内容更新表（基本上是进行连接）。目前我正在考虑编写一些 php 脚本来做到这一点。基本上将该平面文件作为数组加载，并让 php 代码进行实际的搜索和更新。
Is there any easier/better way to do this task? Someone suggested a tool named phpmyadmin but that doesn't seem to be able to handle this level of complex task.
有没有更简单/更好的方法来完成这项任务？有人建议使用名为 phpmyadmin 的工具，但似乎无法处理这种级别的复杂任务。
thanks.
谢谢。
回答by Erico
You can create a temporary table, load the file into it and then update your persistent table.
您可以创建一个临时表，将文件加载到其中，然后更新持久表。
CREATE TEMPORARY TABLE tmp
(
name varchar(255),
phone varchar(255),
address varchar(255)
INDEX (name)
);
LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE tmp
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
(@name, @phone, @address)
SET
name = @name,
phone = @phone
address = @address;
If you want to ignore the first nlines of the csv add the line below after the line LINES TERMINATED.
如果您想忽略csv的前n行，请在 LINES TERMINATED 行之后添加以下行。
IGNORE n LINES
Finally, you add/update the rows to your table based on the temporary one.
最后，根据临时行向表中添加/更新行。
Supposing you're going to use the column name to identify if the row in the csv already exists on the table, you can do something like this:
假设您要使用列名来确定 csv 中的行是否已存在于表中，您可以执行以下操作：
INSERT INTO tbl (name, phone, address)
SELECT name, phone, address FROM tmp WHERE name NOT IN (SELECT name FROM table);
And to update the existing rows, you'll use a update with JOIN.
要更新现有行，您将使用带有 JOIN 的更新。
UPDATE tbl a
JOIN tmp b ON a.name = b.name
SET a.phone = b.phone,
a.address = b.address;
