从本地文件更新MySQL表
问题描述：
我在数据库中有一个表，我想更新一个在本地文件上离线的列.文件本身有两列
I have a table in a database, and I'd like to update a column which I have offline on a local file. The file itself has two columns
- 与表中的ID列相对应的ID，以及
- 实际值.
我已经能够使用
LOAD DATA INFILE 'file.txt' INTO TABLE table
FIELDS TERMINATED BY ','
但是我不确定如何特别地插入值，以使文件中的ID列与表中的ID列结合在一起.有人可以提供有关SQL语法的帮助吗?
But I'm not sure how I can specifically insert values in such a way that the ID column in the file is joined to the ID column in the table. Can someone help with the SQL syntax?
答
我建议您将数据加载到临时表中，然后使用INSERT ... SELECT ... ON DUPLICATE KEY UPDATE;例如:
I suggest you load your data into a temporary table, then use an INSERT ... SELECT ... ON DUPLICATE KEY UPDATE; for example:
CREATE TEMPORARY TABLE temptable (
id INT UNSIGNED NOT NULL,
val INT,
PRIMARY KEY (id)
) ENGINE = MEMORY;
LOAD DATA LOCAL INFILE '/path/to/file.txt' INTO temptable FIELDS TERMINATED BY ',';
INSERT INTO my_table
SELECT id, val FROM temptable
ON DUPLICATE KEY UPDATE val = VALUES(val);
DROP TEMPORARY TABLE temptable;