Flite Careers

Don't Quote Me

Do you ever get annoyed by the backticks in the output when you run SHOW CREATE TABLES in MySQL? I do. There are certainly places where I like the backticks – for example in the output of mysqldump – but when I run SHOW CREATE TABLES I usually don’t want the backticks, and I end up stripping them out.

Enter sql_quote_show_create. This session variable allows you to turn the backticks on and off in your session.

I’m sure this session variable has been around for years, but I just recently discovered it and I like it.

Here’s a quick example to show it in action:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> show create table sakila.film_text\G
*************************** 1. row ***************************
       Table: film_text
Create Table: CREATE TABLE `film_text` (
  `film_id` smallint(6) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  PRIMARY KEY (`film_id`),
  FULLTEXT KEY `idx_title_description` (`title`,`description`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> set sql_quote_show_create = 'OFF';
Query OK, 0 rows affected (0.00 sec)

mysql> show create table sakila.film_text\G
*************************** 1. row ***************************
       Table: film_text
Create Table: CREATE TABLE film_text (
  film_id smallint(6) NOT NULL,
  title varchar(255) NOT NULL,
  description text,
  PRIMARY KEY (film_id),
  FULLTEXT KEY idx_title_description (title,description)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Comments

Comments