bash 自动将 csv 文件传输到 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5314271/
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
Automate transfer of csv file to MySQL
提问by quarkdown27
I have a csv file that I will be regularly updating through a batch script that calls cygwin+ bash script. I would like to automate the upload of the csv file into a MySQL database such that a table in my database would be updated with the csv file at regular intervals. The database is currently running on a Windows Server 2003 machine and administered with phpMyAdmin.
我有一个 csv 文件，我将通过调用 cygwin+ bash 脚本的批处理脚本定期更新该文件。我想自动将 csv 文件上传到 MySQL 数据库，以便我的数据库中的表将定期更新为 csv 文件。该数据库目前在 Windows Server 2003 机器上运行，并使用 phpMyAdmin 进行管理。
I have looked online and found some ways that I could achieve part of that, but I am confused as to where the code presented in those sources should be placed and how they would be called. For instance, Import CSV file directly into MySQLseems to show how to upload a csv file to a MySQL database from the SQL command line once, but not repeatedly, the latter being what I need.
我在网上查看并找到了一些可以实现部分目标的方法，但我对这些源中提供的代码应该放在何处以及如何调用它们感到困惑。例如，将CSV 文件直接导入 MySQL似乎展示了如何将 csv 文件从 SQL 命令行上传到 MySQL 数据库一次，但不是重复，后者是我需要的。
I would prefer the solution to involved bash scripting (as opposed to batch and php) if possible (i.e. I would prefer a solution that I could integrate with the bash scripts that update the csv file).
如果可能的话，我更喜欢涉及 bash 脚本（而不是批处理和 php）的解决方案（即我更喜欢可以与更新 csv 文件的 bash 脚本集成的解决方案）。
Thank you
谢谢
回答by dmcnelis
You can execute a MySQL script from the command line by doing something like:
您可以通过执行以下操作从命令行执行 MySQL 脚本：
mysql -uUsername -pPassword database_name < infile.sql
You could invoke that from the command line and in the infile.sql you could have code like:
您可以从命令行和 infile.sql 中调用它，您可以使用以下代码：
LOAD DATA INFILE 'filename.csv' TO table_name
FIELDS TERMINATED BY ','
回答by Paused until further notice.
You can use a here document:
您可以使用此处的文档：
# some bash script stuff
mysql ... <<EOF
SQL COMMANDS
GO HERE
EOF
# more bash script stuff
回答by Manyalo
You can use Quartz to create a cronjob - for periodically updating your database. with the help of cronmaker (http://www.cronmaker.com/), you get to choose when and how often your database gets updated.
您可以使用 Quartz 创建一个 cronjob - 用于定期更新您的数据库。在 cronmaker ( http://www.cronmaker.com/)的帮助下，您可以选择更新数据库的时间和频率。
This is a sample SQL Script to import data into your MySQL database:
这是将数据导入 MySQL 数据库的示例 SQL 脚本：
LOAD DATA INFILE 'c:/.../filename.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
run the above script in your cronjob using your preferred language.
使用您的首选语言在您的 cronjob 中运行上述脚本。
