5

Am frustated by things like:

0:33:1407402356:root@ahost:~# echo 'use wordpress_3_6_1; select * from wp_posts;'
 | mysql -u mysqluser -pmysqlpasswdord | wc -l -L
42   40585

SQL query results being mangled ugly.

PostgreSQL s psql provides the helper feature expanded mode. See it in action:

postgres@ahost:~$ echo '\c openerp7-0 \\ select * from pg_shadow' | psql
You are now connected to database "openerp7-0" as user "postgres".
 usename  | usesysid | usecreatedb | usesuper | usecatupd | userepl |          
   passwd                | valuntil | useconfig 
----------+----------+-------------+----------+-----------+---------+----------
-------------------------+----------+-----------
 openerp |    16384 | t           | t        | t         | t       | 
ahash |          | 
 postgres |       10 | t           | t        | t         | t       | 
anotherhash |          | 
(2 rows)

postgres@ahost:~$ echo '\c openerp7-0 \\ \x \\ select * from pg_shadow' |
psql                
You are now connected to database "openerp7-0" as user "postgres".
Expanded display is on.
-[ RECORD 1 ]------------------------------------
usename     | openerp
usesysid    | 16384
usecreatedb | t
usesuper    | t
usecatupd   | t
userepl     | t
passwd      | ahash
valuntil    | 
useconfig   | 
-[ RECORD 2 ]------------------------------------
usename     | postgres
usesysid    | 10
usecreatedb | t
usesuper    | t
usecatupd   | t
userepl     | t
passwd      | anotherhash
valuntil    | 
useconfig   |

That expanded mode is cute for tables with many columns:

postgres@ahost:~$ echo '\c openerp7-0 \\ \x \\ select * from res_partner' |
psql | wc -l -L
223   44423
postgres@ahost:~$ echo '\c openerp7-0 \\ select * from res_partner' | psql |
wc -l -L
  9   94030

Of course that is only when people do not mind mangling up the row count with wc -l.

How can people accomplish for mysql a feature similar to the expanded mode featured in psql?

178024
  • 36
  • 3
  • 15

1 Answers1

8

If I understand correctly what you want to achieve, you can try it with the \G separator (instead of semicolon), like this:

echo 'SELECT * FROM mytable\G' | mysql -u myuser -p mypassword mydb

Example output:

*************************** 1. row ***************************
    id: 1
locale: de
  name: Afghanistan
*************************** 2. row ***************************
    id: 2
locale: de
  name: Ägypten
Leif
  • 196
  • 3
  • Nice. Obviating the obvious and obviously obviable horizontal aligning difference, it will make OK for me in most cases. – 178024 Aug 07 '14 at 10:13
  • 1
    So in `man mysql` they call it the _vertical format_. But it is related to an _ego_ command, that does the same as _go_ but displaying results using the vertical format. Maybe that _e_ in _ego_ stands for _expanded_. Guess the terminology comes from earlier closed source RDBMS most notably Oracle. – 178024 Aug 08 '14 at 07:51