In MySQL there’s a system variable called sql_quote_show_create which is used to determine whether or not to use quoted identifiers when using the SHOW CREATE TABLE and SHOW CREATE DATABASE statements.

By default sql_quote_show_create is set to 1, which means that quotes/backticks will be generated whenever these statements are run, but we can also change this value to 0 in order to disable quotes.

Below are examples of checking this variable and setting it.

Checking sql_quote_show_create

We can check our sql_quote_show_create option like this:

SELECT @@sql_quote_show_create;

Result:

+-------------------------+
| @@sql_quote_show_create |
+-------------------------+
|                       1 |
+-------------------------+

In my case sql_quote_show_create is enabled. This is the default value.

Setting sql_quote_show_create

Here’s an example of setting the sql_quote_show_create system variable:

SET sql_quote_show_create = 0;

That disables quoted identifiers for when we use the SHOW CREATE TABLE and SHOW CREATE DATABASE statements.

Restoring the Default

We can restore the sql_quote_show_create system variable to its default value by either setting it to 1 or DEFAULT. Specifying DEFAULT results in it being set to 1.

Here’s an example of using DEFAULT:

SET sql_quote_show_create = DEFAULT;

Now let’s check it again:

SELECT @@sql_quote_show_create;

Result:

+-------------------------+
| @@sql_quote_show_create |
+-------------------------+
|                       1 |
+-------------------------+

Example of Usage

Let’s run the SHOW CREATE TABLE statement to see how sql_quote_show_create affects the output:

SHOW CREATE TABLE actor;

Result:

+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| actor | CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

We can see that the table name and column names are surrounded by backticks. That’s because our sql_quote_show_create variable is set to 1.

Here’s what happens when we disable quotes:

SET sql_quote_show_create = 0;
SHOW CREATE TABLE actor;

Result:

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| actor | CREATE TABLE actor (
  actor_id smallint unsigned NOT NULL AUTO_INCREMENT,
  first_name varchar(45) NOT NULL,
  last_name varchar(45) NOT NULL,
  last_update timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (actor_id),
  KEY idx_actor_last_name (last_name)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

This time there are no backticks in the resulting CREATE TABLE statement.