Thursday, December 01, 2011

Never say "there is no way"

Reading a recent MySQL book, I saw an example of SHOW CREATE TABLE that comes with backticks (`) around the table and column names, and a comment:
Unfortunately, there is no way to remove this from generated syntax with this command.
(Emphasis mine).
Here's how it goes:
mysql> show create table mytest\G
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE `mytest` (
  `id` int(11) NOT NULL,
  `description` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Of course, there is a way!

mysql> pager tr -d '`'
PAGER set to 'tr -d '`''
mysql> show create table mytest\G
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE mytest (
  id int(11) NOT NULL,
  description varchar(50) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Never say "there is no way!"

Instead of "tr -d '`'", you can use "sed -e 's/`//g'", which does the same thing.

If you are running the query at the command line, you may use the pipe directly:

$ mysql -e 'show create table test.mytest\G' | tr -d '`'
*************************** 1. row ***************************
       Table: mytest
Create Table: CREATE TABLE mytest (
  id int(11) NOT NULL,
  description varchar(50) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

9 comments:

Sergei said...

Of course, there are usually at least two ways to do something that "there is no way to".

I prefer

SET SQL_QUOTE_SHOW_CREATE=0

Giuseppe Maxia said...

Thanks, Sergei!
I love it when someone improves my findings.

Baron said...

Sergei beat me to it :-)

Ronald Bradford said...

Very good!

Anonymous said...

Sergei's solution will work everywhere. It is handled by MySQL and dos not depend on any specific console functionality.

Guiseppe's not on Windows for instance. And what about applications?


Peter Laursen

Anonymous said...

Giuseppe,
Apart from using the linux client (Peter is right) -- your solution is also not strictly correct.
As strange as it may sound, a backtick ("`") is allowed within a table name or a column name. Of course, anyone who commits such a crime should be sent to the dungeon, but nevertheless, you should work your unquoting algorithm more carefully.

Mark Grennan said...

Very clever. I hope some day to know MySQL as well as you.

Cédric said...

Never write something in mind that Giuseppe will not reads it !
Thx all for tips with that.

Mats Kindahl said...

Well, maybe he should have said "no easy and reliable way". :)

mysql> show create table t1;

+-------+--------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+--------------------------------------------------------------------------------------------------------------------+

| t1 | CREATE TABLE `t1` (
`msg` varchar(64) DEFAULT 'This is `just` an example'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+-------+--------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)



mysql> pager tr -d '`'

PAGER set to 'tr -d '`''

mysql> show create table t1;

+-------+--------------------------------------------------------------------------------------------------------------------+

| Table | Create Table |

+-------+--------------------------------------------------------------------------------------------------------------------+

| t1 | CREATE TABLE t1 (
msg varchar(64) DEFAULT 'This is just an example'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

+-------+--------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)