Different output when redirecting

$ mysql -e 'select a,b from tablefoo' databasename

yields

+---+---+
| a | b |
+---+---+
| 1 | 0 |
| 2 | 1 |
+---+---+

whereas

$ mysql -e 'select a,b from tablefoo' databasename > file

yields a file file containing

a b
1 0
2 1

(where a tab is between the alphanumeric characters on each line).

I'd think that redirection shouldn't change the output. Why do I get two different results?

Edit: William Jackson's answer says that this is a feature of mysql: output format depends on whether the output is being redirected. This doesn't answer my question, though. How does mysql 'know' whether output is being redirected? Does redirection not just take the output and, well, redirect it somewhere? Shouldn't that be invisible to mysql?

Solution 1:

Edit: I cannot be certain this is how mysql does it, but it could be using isatty(3) to determine whether STDOUT is a terminal or not, and modifying the output accordingly.

Edit 2: The mysql command line tool definitely uses isatty(). You can read the source code.

There are some good examples of this (although not in C) over at Stack Overflow:

• PHP: How can I detect STDOUT redirection in PHP CLI?
• Python: How do I detect whether sys.stdout is attached to terminal or not?

To answer your question, "Why?": Because that is what the documentation says. See the reference manual:

When used interactively, query results are presented in an ASCII-table format. When used noninteractively (for example, as a filter), the result is presented in tab-separated format.

I suspect this decision was made for readability. When used interactively, mysql can assume a human is reading the output, and it is generally easier for us humans to read data that is delimited by those lines. When used non-interactively, the assumption is that another program is going to consume the output, and that tab-delimited output is easier to programmatically consume.

If you are interested in overriding this default and getting ASCII-table format when redirecting the output, you can use the --table (-t) command line option:

mysql -t -e 'select a,b from tablefoo' databasename > file