MySQL provides the sql_quote_show_create system variable, which allows us to specify whether or not to include quotes around identifiers when using the SHOW CREATE TABLE and SHOW CREATE DATABASE statements.
By default, this variable is set to 1, which means that quotes/backticks will be generated whenever these statements are run.
However, we might not always want quotes or backticks around the object names, and so we can change the value of this variable to disable quoted identifiers.
Check the Current Value
We can check the current value of the sql_quote_show_create variable like this:
SELECT @@sql_quote_show_create;Result:
+-------------------------+ | @@sql_quote_show_create | +-------------------------+ | 1 | +-------------------------+
In this case, the variable is set to 1, which means that quoted identifiers are enabled (table names, column names, etc will be wrapped in quotes when running the SHOW CREATE ... statements).
Example of Usage
Here’s an example of where the sql_quote_show_create system variable takes effect:
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 backticks around the table name, as well as the column names and also the index name. That’s because our sql_quote_show_create system variable is set to 1.
Change the Value
We can change the value of sql_quote_show_create like this:
SET sql_quote_show_create = 0;Result:
Query OK, 0 rows affected (0.00 sec)
I just set it to 0, which means quoted identifiers are disabled.
Now let’s run the SHOW CREATE TABLE statement again:
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 the same CREATE TABLE statement was generated, except that the table names and columns are not quoted.
Restore to the Default Value
We can set sql_quote_show_create to its default value by either setting it to 1 or DEFAULT (both of these will set it to 1).
Example:
SET sql_quote_show_create = DEFAULT;Now let’s check its value again:
SELECT @@sql_quote_show_create;Result:
+-------------------------+ | @@sql_quote_show_create | +-------------------------+ | 1 | +-------------------------+
Here I specified DEFAULT, which set it to 1 (the default value). Using DEFAULT can be handy if you can’t remember what the default value is.