How to display results in MySQL and MariaDB without a table and without extraneous characters

Contents

1. How to output data without a table when saving results to a file
2. How to display results without a table on the screen
3. How to display results in mariadb and mysql clients without a table
4. How to configure the output of results without a table by default

When working with MySQL and MariaDB in the command line, the results of SQL queries with SELECT and SHOW operators are displayed in a very visual form: tables are created with pseudo-graphics, in which columns and rows are separated by space characters and other auxiliary characters.

All this seems very convenient until the moment when you need to copy the information displayed on the screen, obtained as a result of a query to MySQL and MariaDB. At this moment, it “suddenly” turns out that the data is littered with unnecessary characters “|”, “-”, “+” and spaces.

And in an instant, what was very attractive on the screen turns out to be completely unsuitable for use in further work, for example, to paste into an office document (Word, Writer, Excel, Calc).

Let's look at various methods for saving SQL query results without table-forming characters in MySQL and MariaDB clients.

1. How to output data without a table when saving results to a file

You don't have to use interactive sessions to execute a SQL query – you can specify your command after the -e option, for example:

mariadb -u root -e 'SELECT test_column FROM TestDB.TestTABLE;'

Note: I use mariadb as the executable name because I have MariaDB installed on my test computer. If you have MySQL installed, use mysql instead of mariadb in this and all subsequent commands. Currently, MariaDB also supports the mysql alias. That is, if you are not sure which version of the software you have installed, you can use the mysql file name as a more universal one. What matters is not the software on the server you are connecting to, but the software installed on the computer from which you are running the client. If you are performing all actions on one computer and the DBMS server is running locally, then the client and server versions (MariaDB or MySQL) are the same.

As you can see, the command worked, but we got the table header and various dashes depicting the table in the output.

But you can use a trick – if you save the query result to a file, then only the command result will be saved in it, without the table.

To save the results of a MySQL or MariaDB query to a file, use the following construct:

mariadb -u root -e 'SQL-QUERY' -r -N > FILE

In this command:

• SQL-QUERY is your SQL query
• FILE is the path to the file where you want to save the result
• Option -r (--raw) is used to write fields without conversion (if you do not specify this option, then, for example, newline characters will be replaced with literal “\n”)
• Option -N (--skip-column-names) is necessary to hide the column names

For example, the following command will execute a query to the DBMS and save the result to the file result.txt:

mariadb -u root -e 'SELECT test_column FROM TestDB.TestTABLE;' -r -N > result.txt

In my opinion, this is a very convenient way if you can work with MySQL and MariaDB in non-interactive mode.

2. How to display results without a table on the screen

To display the results of a DBMS query on the screen without a table, add the -s option. Also use the -r option so that newline characters are not replaced with “\n” notations. If you do not want to display the column names, then add the -N option:

mariadb -u root -e 'SQL-QUERY' -r -s -N

Example of command execution:

mariadb -u root -e 'SELECT test_column FROM TestDB.TestTABLE;' -r -s -N

3. How to display results in mariadb and mysql clients without a table

Let's consider the case when you want to get pure results without a table during an interactive session.

To do this, when connecting to a MySQL or MariaDB server, specify three additional options for the client: -s, -r and -N, for example:

mariadb -u root -s -r -N

Now the results will be displayed without a table:

SELECT test_column FROM TestDB.TestTABLE;

4. How to configure the output of results without a table by default

You can specify all the options discussed above (as well as any others) in the MySQL and MariaDB configuration file, and when connecting to the DBMS, you will not need to enter these options each time.

For example, save the following lines to the ~/.my.cnf file:

[client]
skip_column_names
silent
raw

See also: How to determine the location and name of the MySQL (MariaDB) configuration file. How to find the group name for MySQL and MariaDB configuration files

Now, when executing SQL queries, tables will not be displayed. For example:

mariadb -u root -e 'SELECT test_column FROM TestDB.TestTABLE;'

And one more example:

mariadb -u root

SELECT test_column FROM TestDB.TestTABLE;