Command-line MySQL select with different field terminator

Question

I have a bash script in which I am running a MySQL statement:

#!/usr/bin/env bash

mysql -N --user=foo --password=bar --host=baz -A -D db -e '\
    SELECT ... \
    FROM fieldA, fieldB \
    LIMIT 100'

This works fine. However, instead of the tabular format that this returns, I'd like to get a tab-delimited result.

I tried the following, but it gives a syntax error:

#!/usr/bin/env bash

mysql -N --user=foo --password=bar --host=baz -A -D db -e '\
    SELECT ... \
    FROM fieldA, fieldB \
    LIMIT 100 \
    FIELDS TERMINATED BY "\t"'

The error is:

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS TERMINATED BY "\t"' at line 10

I also tried

    ...
    FIELDS TERMINATED BY \'\t\''

But I get the same error.

Is there a correct way to change the field terminator from this kind of MySQL run? Thanks for your advice.

Answer 1

According to the man page for the mysql client:

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.

Your use of -e is making mysql treat it as an interactive command. But the client can also accept input via stdin. This is what you want:

mysql -N --user=foo --password=bar --host=baz -A db <<-'EOF'
    SELECT ...
    FROM fieldA, fieldB
    LIMIT 100
EOF