How to get the query result into a file ?
We can output the result of a mysql query into a file by making use of “INTO OUTFILE” parameter.
For example, if we need to have the result into a .txt file;
SELECT order_id,product_name,qty FROM orders INTO OUTFILE ‘/tmp/orders.txt’
If we need to generate a CSV from the query;
SELECT order_id,product_name,qty FROM orders
INTO OUTFILE ‘/tmp/orders.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
This method works for select and such statements, but when we go for show table and such statements this will not work. In such cases, we need to run the mysql query from the command line and then redirect the query result to a file.
eg: mysql -u root -e ‘show tables’ <database_name> > abc.txt