MySQL automatically export to new csv files on trigger

Question

I have 10 machines and I have created one table for each machine I am collecting data from, each machine has the exact same columns in their tables. Columns are: order#, timestamp, pv, mv, sp. The operator will select order# prior to running the machines so I want the MySQL database to export rows specific to that order number to a new CSV file named "order#.csv" every time a new order# is entered. I am very new to databases so this is what I have this far:

(SELECT 'Order Number','Timestamp','Mv','Pv','Sp')
union  
(select orderNo,Timestamp,Mv,Pv,Sp
from pid
where orderNo = new.orderNo
INTO OUTFILE 'H:/test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n');

In the end what I am trying to achieve is having one CSV file with all machine data collected when the machine is working on ONE order, however long it's been working on that order. So that I will have one CSV file for one order on one machine, and I can go back and look at the machine performance for different orders. Any help on this is greatly appreciated!

no it is not working. The "where orderNo = new.orderNo" part is not correct and I am still figuring out how to automatically generate files specific to an order number. — user1759827, Jun 29 '17 at 16:34
If this is a trigger, is the `new.` similar to the `inserted` table in SQL? If so, then shouldn't you join that `new` table based on the `orderNo`? — SS_DBA, Jun 29 '17 at 17:05
Your outfile looks fine according to this solution. https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format?rq=1 — SS_DBA, Jun 29 '17 at 17:36
I just updated my question. I want the database to auto-export data asscociated to a unique order # into a new CSV fie. — user1759827, Jun 29 '17 at 17:44

Bill Karwin · Answer 1 · 2017-06-29T18:14:37.443

To get your query to work, you just need to move the parentheses.

(SELECT 'Order Number','Timestamp','Mv','Pv','Sp')
union  
(select orderNo,Timestamp,Mv,Pv,Sp
from pid
where orderNo = new.orderNo)         <-- here
INTO OUTFILE 'H:/test.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

But I would advise against doing this with INTO OUTFILE in a trigger.

MySQL's INTO OUTFILE won't overwrite an existing file. See https://dev.mysql.com/doc/refman/5.7/en/select-into.html which says:

file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.

So you'd have to move or delete the csv file immediately after it is created, before the trigger executes again. Or else generate a new random csv filename every time you want to output. If you don't do one of these things, the INTO OUTFILE will generate an error, which will cause the trigger to abort, and that causes the INSERT that spawned the trigger to fail.
Using INTO OUTFILE also writes the file on the database server host, not the host where you run your application code. If you scale out to run applications on a different host than the database, you will find that your application no longer can find the csv file.
Just because a trigger is executed, this doesn't mean the data will be committed. The transaction that spawned the trigger might roll back. So the data written to your csv file will contain "phantom data" that is not committed in the database.

Instead, I would suggest that you export to csv using application code, after your database changes have been committed successfully.

MySQL automatically export to new csv files on trigger

1 Answers1

Linked