Search code examples
mysqlsqlinto-outfile

How can I add headers and format MySQL query output files?

I connect to mysql from my Linux shell and use something like this:

SELECT * FROM students INTO OUTFILE '/tmp/students'.

  1. Why do I see \N at line endings? I want each record in a row, but why do I see the \N explicitly printed?

  2. How can I print all column headers in the first row?

Solution

  • SELECT ... INTO OUTFILE exports the result to a rather mysql specific delimited format. \N means a NULL value, not end-of-line.

    Run e.g. from a command line:

    echo 'select * from students' | mysql mydb >/tmp/students

    The documentation for SELECT shows you how what options you have when using INTO OUTFILE, but you can't export the headers directly that way. See the comments in that documentation for a hacky way of adding header columns though.