Last Updated: September 09, 2019
·
20.11K
· hobnob

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:

3 Responses
Add your response

Ohhh , thanks for this tips

over 1 year ago ·

nice

over 1 year ago ·

how can I format(add "" with the output ) the column id as below ?
select id from table;
o/p
"234"

over 1 year ago ·