Why the \G in SELECT * FROM table_name\G?

Question

Terminating a MySQL query with \G instead of ; will cause MySQL to return the result set in vertical format, which is sometimes easier to read if the number of returned columns is large.

Example:

mysql> SELECT * FROM help_keyword LIMIT 3\G
*************************** 1. row ***************************
help_keyword_id: 0
           name: JOIN
*************************** 2. row ***************************
help_keyword_id: 1
           name: REPEAT
*************************** 3. row ***************************
help_keyword_id: 2
           name: SERIALIZABLE
3 rows in set (0.00 sec)

My question asked out of pure curiosity: Is there any rationale behind choosing the character combination \G?

Uniparous

Answer 1

My thoughts:

1. It probably means "go".
2. Postgresql also uses \g as a statement terminator. Postgresql is older than MySQL so it might have influenced them.

But as I pointed out in my comment to Andriyev's post above, it's actually making the \G uppercase that causes the display to be laid out vertically. Lowercase \g doesn't have that effect, or if it does then the documentation doesn't mention it. (I don't have a MySQL install handy to try it out.)

Anselme

"\g" is equivalent to ";" in MySQL – Divot

Answer 2

Short Answer
The ubiquitous semicolon command terminator ; is actually shorthand for the \g command, which is in itself shorthand for the go command. The go command is used both historically and currently in other flavours of SQL to submit batches of commands to be compiled and / or interpretted by the server. The \G command seems to inherit it's characteristic letter from \g, and is capitalised to further indicate a modified behaviour, as described by...
mysql> help ... \g go Send command to mysql server. \G ego Send command to mysql server, display result vertically. ...

Longer Answer ( It should really be \E )
Entering help at the mysql prompt lists all the possible mysql commands, including go and ego shown above. The ego command acquires a prepended 'e' indicating that this form of the go command also adopts a behaviour that would normally be imposed by invoking mysql with the similar switch mysql -E

From man mysql... ... --vertical, -E Print query output rows vertically (one line per column value). Without this option, you can specify vertical output for individual statements by terminating them with \G. ...

So why use -E as shorthand for --vertical ?... Because both V, v, and e had already been assigned as switches to other invocation behaviours. The ego command could just have easily used \E as it's shortcut, but confusingly adopted a capitalised version of the \g command.

In summary...
--vertical >> -E >> ego >> \G ...Tada !

Hunt

Great explanation of the meaning of this symbol, and the reasoning/convention behind it. This should be the accepted answer. – Thisbe

Interestingly, mysql> SHOW TABLE STATUS\ego brings up a temp file with the query in the file: mysql show table status ... ... "/tmp/sql4hD8Ov" [noeol] 1L, 17C Not sure what this is meant for? – Rufus

Answer 3

MySQL Client - Automatic Vertical Output

Additionally to Gavin Jackson great answer, I've found this little gem:

$ mysql --auto-vertical-output

If the output is wider than your terminal, results are displayed in vertical format automatically.

add an alias:
$ alias mysql='mysql --auto-vertical-output'

make alias permanent
$ echo "alias mysql='mysql --auto-vertical-output'" | tee -a ~/.bashrc

Rufus

Answer 4

It matches the Unix command 'ls' formatting options

Conn

How does it match the ls formatting options? Do you mean "ls -G"? – Uniparous

No, -G jsut hides the group. And -g just hides the owner. Not sure what you are thinking of, but -1 shows it in 1 column. – Mute