Printing \G on shell using mysql client..?

Question

It is really a a headache for me from some hours i am struggling on the problem.I have also asked it on stack-overflow but not got any answer yet so i am asking here.Sorry for Not a good question.

I am Executing a Command on Linux shell.

mysql -uroot -proot -Bse "select Concat('SHOW CREATE TABLE ',TABLE_SCHEMA ,'.',TABLE_NAME,'\\G') from information_schema.tables where TABLE_SCHEMA Not IN('mysql','information_schema','performance_schema');"

I want output as

SHOW CREATE TABLE sakila.category\G
SHOW CREATE TABLE sakila.city\G
SHOW CREATE TABLE sakila.country\G
SHOW CREATE TABLE sakila.customer\G

But I am getting as

SHOW CREATE TABLE sakila.categoryG
SHOW CREATE TABLE sakila.cityG
SHOW CREATE TABLE sakila.countryG
SHOW CREATE TABLE sakila.customerG

Why it is so..?

Answer 1

This has always been a real pain-in-the-neck for me.

• The mysql client seems to interpret the \\G as \G
• The OS seems to take that output and interpret \G as G

Unfortunately, this is the sequence that always happens.

I use the sed command to get around this as follows:

mysql -uroot -proot -Bse "select Concat('SHOW CREATE TABLE ',TABLE_SCHEMA ,'.',TABLE_NAME,' G') from information_schema.tables where TABLE_SCHEMA Not IN('mysql','information_schema','performance_schema');" | sed 's/ G$/\\G/g'

Notice that I replaced \\G with G (blank followed by G)

I have the sed command translate G$ (which means the blank capital G at the end of of line) and convert it to \G. Notice that sed is doing this conversion with OS output and mysql output.

You no longer have to deal with that \G issue. Simply plan for a blank capital G to be transalted by sed to \G.