从本地文件更新 MySQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10680162/
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 MySQL table from a local file
提问by genekogan
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
我在数据库中有一个表，我想更新我在本地文件中离线的列。文件本身有两列
- an ID which corresponds to an ID column in the table, and
- the actual value.
- 对应于表中 ID 列的 ID，以及
- 实际值。
I've been able to create new rows using
我已经能够使用创建新行
LOAD DATA INFILE 'file.txt' INTO TABLE table
FIELDS TERMINATED BY ','
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?
但我不确定如何以文件中的 ID 列连接到表中的 ID 列的方式专门插入值。有人可以帮助使用 SQL 语法吗？
回答by eggyal
I suggest you load your data into a temporary table, then use an INSERT ... SELECT ... ON DUPLICATE KEY UPDATE; for example:
我建议您将数据加载到临时表中，然后使用INSERT ... SELECT ... ON DUPLICATE KEY UPDATE; 例如：
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;
回答by Rahul
Another way could be ...
另一种方式可能是...
Since you already know the table name as well have the ID and actual value ... what you can do is ... directly write the update statements in a file, like
由于您已经知道表名以及 ID 和实际值……您可以做的是……直接将更新语句写入文件，例如
update mytable set value_col = value where ID_col = ID;
Second Update Statement
Third Update statement
.......
Save the file as *.sqllike, updatescript.sqland then execute that script directly like
将文件另存为*.sql，updatescript.sql然后直接执行该脚本，如
mysql -h <hostname> -u root -p <your_db_name> < "E:/scripts/sql/updatescript.sql"
回答by PandeyTheDBA
It depends of the no of rows , If it is in hundreds make a script of update column and run it , but if it is in large volume import that file in to a new table and update your table with a join , and then drop the table
这取决于行数，如果有数百个，则制作一个更新列的脚本并运行它，但如果它是大容量的，则将该文件导入到一个新表中并使用连接更新您的表，然后删除桌子
