php 每 10 分钟从 CSV 文件更新 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16504881/
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 from CSV file Every 10 Minutes
提问by MillerMedia
I am relatively new to MySQL and am looking for a way to run a MySQL update every 10 minutes. Basically there is a central CSV file that is updated every ten minutes on a server that we grab the data from. I am trying to set up a MySQL table that will read that CSV file and load it into the MySQL table every ten minutes.
我对 MySQL 比较陌生，正在寻找一种每 10 分钟运行一次 MySQL 更新的方法。基本上有一个中央 CSV 文件，在我们从中获取数据的服务器上每十分钟更新一次。我正在尝试设置一个 MySQL 表，该表将每十分钟读取该 CSV 文件并将其加载到 MySQL 表中。
I know that you can link a MySQL table to a CSV file directly using 'load data.' My confusion comes with the actual automatic updating. I've done some research and read about 'cron' jobs which will perform automated tasks at set intervals. My thought is to have the PHP script run by the cron job every ten minutes (if the host allows it).
我知道您可以使用“加载数据”直接将 MySQL 表链接到 CSV 文件。我的困惑来自实际的自动更新。我做了一些研究并阅读了有关“cron”作业的信息，这些作业将以设定的时间间隔执行自动化任务。我的想法是让 cron 作业每十分钟运行一次 PHP 脚本（如果主机允许）。
I'm just wondering if this is best practice. I've been searching around and don't see many other options and wanted to see if you all knew of any other methods that I may be missing so I can make a proper assessment of this job. Thanks for your help!
我只是想知道这是否是最佳实践。我一直在四处寻找，没有看到很多其他选择，想看看你们是否都知道我可能遗漏的任何其他方法，以便我可以对这项工作进行适当的评估。谢谢你的帮助！
回答by Orangepill
cron is going to be the way to go. Depending on the size of the file loaded it might be an expensive task (in terms of time). I would suggest having the cron upload the file to a temp table then within a transaction drop the old table then rename the temp table to the correct table name. If the file is relatively small then it might make sense to walk each line in the file and determine if based on querying the database a record needs to be added or updated.
cron 将是要走的路。根据加载的文件的大小，它可能是一项昂贵的任务（就时间而言）。我建议让 cron 将文件上传到临时表，然后在事务中删除旧表，然后将临时表重命名为正确的表名。如果文件相对较小，那么遍历文件中的每一行并根据查询数据库确定是否需要添加或更新记录可能是有意义的。
