How to store MySQL results to a file in table format

Question

I am trying to create a file and store in it the results from my query. I have a batch file that contains the single query,

USE database1;

SELECT * FROM table WHERE name = "abc" INTO OUTFILE output.txt;

QUIT

Executing this batch file using,

mysql -u root -p -t -vvv < select.sql

However, the result is not table formatted and fields' names are missing from the top.

100 abc Brown 32
101 abc Flair 25
102 abc McDonald 45
.
.
.

If I remove the INTO OUTFILE statement and print the results on terminal, then is working OK.

+----+------+---------+-----+
| id | name | surname | age |
+----+------+---------+-----+
| 100| abc  | Brown   |   32|
| 101| abc  | Flair   |   25|
| 102| abc  | McDonald|   45|
+----+------+---------+-----+

How can I achieve the above in a txt file?

UPDATE

Special thanks to GreyBeardedGeek. Here is the solution for this question with help of GreyBeardedGeek.

Batch file:

USE database1;

SELECT * FROM table WHERE name = "abc";

QUIT

and mysql client:

mysql -u root -p -t -vvv < select.sql > output.txt

Bissonnette

remove the INTO outfile, and then do: mysql -u root -p -t -vvv < select.sql > output.txt – Attorney

OK! Well...I lost my mind here! Is working, but why? What's the difference? Thank's a lot :) – Bissonnette

INTO OUTFILE changes the default output, though it has options for quoting values, line delimiters, etc. – Attorney

So, INTO OUTFILE, is useful if you want to store a csv file for example. And choose the delimiter symbols etc. I'm correct? – Bissonnette

yes, it's usually used to get either CSV or TSV (Tab Delimited) output – Attorney

One more thing .. My output result has the sql query on top. There is any way to remove it? Any flag for this? – Bissonnette

if you're on Linux, pipe the result through sed '1 d', e.g. mysql -u root -p -t -vvv < select.sql | sed '1 d' > output.txt – Attorney

Either you or @Attorney should post this as an answer and then you should accept it. – Leannaleanne

Answer 1

This should do the trick:

mysql -u root -p -t -vvv < select.sql | sed '1 d' > output.txt

Attorney

What's the sed '1 d' part do? – Collier

It removes the first line of the output, which in this case, is the sql query – Attorney

I'm guessing the -t option causes the output to be in a table format, but what does -vvv do? – Heathenish

-vvv activates "verbose" mode – Attorney

Answer 2

You can also do following:

mysql -uroot -p DatabaseName -t -e "select * from table" > file.txt

This doesn't need to make an SQL file and then filter out the first line.

Osmometer

Answer 3

Besides using mysql client program options (mysql --help) one can configure defaults within .my.cnf file. Example (MacOs, Linux) to enable --table option:

edit/create a file:

vim ~/.my.cnf

add/update:

[client]
table

next time you call mysql command line program it will use options from .my.cnf file producing output in table format, e.g. the same as one has called:

mysql ... --table ... 

Please note that command line options override options from the .my.cnf files.

References:

• call mysql --help for list of options
• https://dev.mysql.com/doc/refman/8.0/en/option-files.html

Neuralgia