问题描述
我当前正在使用mySQL LOAD DATA INFILE将csv文件插入到我的数据库中.每天将此csv文件下载到服务器,以保持产品数据为最新.
I am currently using mySQL LOAD DATA INFILE to insert a csv file into my database. This csv file is downloaded to the server on a daily basis in order to keep product data up-to-date.
我想知道的是如何使用新的csv更新表并在没有区别的地方保存现有数据?
What I want to know is how can I update the table with the new csv and preserve the existing data where it's not different?
这是我当前的声明:
LOAD DATA LOCAL INFILE '$file' REPLACE INTO TABLE products FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\\' IGNORE 1 LINES (aw_product_id,merchant_id,merchant_image_url,aw_deep_link,description,in_stock,merchant_name,brand_name,display_price,product_name,rrp_price,merchant_category
这很好用,但是它用一个全新的集替换了ID列,并且还将我要忽略的列恢复为默认状态.例如,我有一个名为"Published"的列,其值为0或1.如果使用REPLACE,它将将该列设置回0.
This works fine but it replaces the ID column with a completely new set and also reverts columns that I want to ignore, back to the default state. For example I have a column called 'Published' with a value of 0 or 1. If I use REPLACE it sets that column back to 0.
如何使用REPLACE但忽略一些列?
How can I use REPLACE but ignore some columns?
推荐答案
如何使用REPLACE但忽略某些列?的答案是您不能: REPLACE
始终替换完整的行,而不是该行的单个字段值.
The answer to How can I use REPLACE but ignore some columns? is you can't: REPLACE
allways replaces a complete row, not the single field values of that row.
尽管是,我仍然可以实现我的目标的答案是:是:我的建议是将LOAD DATA LOCAL INFILE
插入另一个表,然后使用存储过程或查询来INSERT
和UPDATE
(与REPLACE
相对)主表.如果您向我们提供更多信息(表结构,哪一列将加载的数据与现有数据进行匹配),我们也许可以为您提供进一步的帮助.
The answer to Can I still achieve my goal though is Yes: My recommendation would be to LOAD DATA LOCAL INFILE
into another table, then use a stored procedure or query to INSERT
and UPDATE
(as opposed to REPLACE
) your main table. If you give us a bit more information (table structure, which column matches the loaded data with the existing data) we might be able to help you further.
这篇关于mysql LOAD DATA INFILE更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!