MySQL import from stdin

Question

I am generating a csv in stdout using awk. Is there a way to directly import that contents in mysql without putting it to file?

Answer 1

As the answer from @xdazz says, just use LOAD DATA LOCAL INFILE. I assume it was downvoted out of ignorance or lazyness. A quick perusal of the MySQL manuals would have shown that to be a perfectly viable answer.

In 2016 and for MariaDB, which will be most relevant to most users, you do this:

bash awk '{ /* My script that spits out a CSV */ }' | mysql --local-infile=1 -u user -ppassword mydatabase -e "LOAD DATA LOCAL INFILE '/dev/stdin' INTO TABLE mytable FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"';"

Obviously, do bother to read the manual and change the options to LOAD DATA INFILE as required to suit your specific case.

Answer 2

MySQL supports getting data in via extended inserts that look like this:

insert into table (col1, col2, col3) values
(a,b,c),
(d,e,f),
(g,h,i);

So you can modify your CSV to include a left paren and a right paren and a comma, and prepend it with insert into table... and append it with a semicolon, then you can pipe that directly to a MySQL commandline.

You can also use named pipes, a Unix construct, to pipe a TSV (tab-separated, not comma-separated) to a load data infile like this:

mkfifo /tmp/mysqltsv
cat file.csv | sed -e 's/,/\t/g' > /tmp/mysqltsv
mysql -e "load data infile '/tmp/mysqltsv' into table tblname"

That is pseudocode. You need to run the cat in one process and the mysql command in another. Easiest is to use two different terminals. More advanced is to background the cat|sed.

Answer 3

It does not seem that you can import CSV from stdin directly. You have to save it to a file so that mysql uses its name as the name of the table (without the extension), you can use mysqlimport as in:

mysqlimport -uUSER -pPASS DB FILE