Last Updated: September 09, 2019

Formatting the Output of MySql Queries With \G

Running queries on wide tables using the MySQL command line can often result in unreadable output:

SELECT * FROM mysql.user;
+----------------------------+-----------------+-------------------------------------------+-------------+-------------+------- ------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+---------- -------+------------+------------+--------------+------------+-----------------------+------------------+--------------+------- ----------+------------------+------------------+----------------+---------------------+--------------------+------------------ +------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+---- ---------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update _priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | Reference s_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_s lave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max _updates | max_connections | max_user_connections | plugin | authentication_string |
+----------------------------+-----------------+-------------------------------------------+-------------+-------------+------- ------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+---------- -------+------------+------------+--------------+------------+-----------------------+------------------+--------------+------- ----------+------------------+------------------+----------------+---------------------+--------------------+------------------ +------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+---- ---------+-----------------+----------------------+--------+-----------------------+
| % | someuser | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| localhost | someuser | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |

You can format this output so that the columns are displayed as key: value pairs by using \G rather than ;, like so:

SELECT * FROM mysql.user \G

*************************** 1. row ***************************
 Host: localhost
 User: someuser
 Password: 
 Select_priv: N
 Insert_priv: N
 Update_priv: N
 Delete_priv: N
 Create_priv: N
 Drop_priv: N
 Reload_priv: N
 Shutdown_priv: N
 Process_priv: N
 File_priv: N
 Grant_priv: N
 References_priv: N
 Index_priv: N
 Alter_priv: N
 Show_db_priv: N
 Super_priv: N
 Create_tmp_table_priv: N
 Lock_tables_priv: N
 Execute_priv: N
 Repl_slave_priv: N
 Repl_client_priv: N
 Create_view_priv: N
 Show_view_priv: N
 Create_routine_priv: N
 Alter_routine_priv: N
 Create_user_priv: N
 Event_priv: N
 Trigger_priv: N
Create_tablespace_priv: N
 ssl_type:
 ssl_cipher:
 x509_issuer:
 x509_subject:
 max_questions: 0
 max_updates: 0
 max_connections: 0
 max_user_connections: 0
 plugin:
 authentication_string:
*************************** 12. row ***************************
 Host: %
 User: someuser
 Password: 
 Select_priv: N
 Insert_priv: N
 Update_priv: N
 Delete_priv: N
 Create_priv: N
 Drop_priv: N
 Reload_priv: N
 Shutdown_priv: N
 Process_priv: N
 File_priv: N
 Grant_priv: N
 References_priv: N
 Index_priv: N
 Alter_priv: N
 Show_db_priv: N
 Super_priv: N
 Create_tmp_table_priv: N
 Lock_tables_priv: N
 Execute_priv: N
 Repl_slave_priv: N
 Repl_client_priv: N
 Create_view_priv: N
 Show_view_priv: N
 Create_routine_priv: N
 Alter_routine_priv: N
 Create_user_priv: N
 Event_priv: N
 Trigger_priv: N
Create_tablespace_priv: N
 ssl_type:
 ssl_cipher:
 x509_issuer:
 x509_subject:
 max_questions: 0
 max_updates: 0
 max_connections: 0
 max_user_connections: 0
 plugin:
 authentication_string:

#mysql