Automate transfer of csv file to MySQL

问题:

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.

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 MySQL seems 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.

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).

Thank you

回答1:

You can execute a MySQL script from the command line by doing something like:

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:

LOAD DATA INFILE 'filename.csv' TO table_name 
FIELDS TERMINATED BY ','

回答2:

You can use a here document:

# some bash script stuff
mysql ... <<EOF
SQL COMMANDS
GO HERE
EOF
# more bash script stuff