Quick Intro to SHOW CREATE DATABASE in MySQL

by

MySQL has a SHOW CREATE DATABASE statement that shows the CREATE DATABASE statement that would be used to recreate the database.

We can use the SHOW CREATE DATABASE statement to create an identical database on another server, or to create a similar database on the same server (while changing the name).

We can alternatively use SHOW CREATE SCHEMA to do the same thing (SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE).

Syntax

The syntax goes like this:

SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

Example

Here’s an example to demonstrate:

SHOW CREATE DATABASE sakila;

Result:

+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| sakila   | CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+

The IF NOT EXISTS Clause

We can include the IF NOT EXISTS clause to include a IF NOT EXISTS clause in the generated CREATE DATABASE statement:

SHOW CREATE DATABASE IF NOT EXISTS sakila;

Result:

+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                                           |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
| sakila   | CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sakila` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+

The SHOW CREATE SCHEMA Statement

As mentioned, SHOW CREATE SCHEMA is a synonym for SHOW CREATE DATABASE, so we can use either one.

Here’s an example of using SHOW CREATE SCHEMA:

SHOW CREATE SCHEMA sakila;

Result:

+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| sakila   | CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+

Same result as previously. Note that this still created a CREATE DATABASE statement even though I used CREATE SCHEMA.

About the Quotes

The resulting output is quoted according to the value of the sql_quote_show_create system variable. This variable allows us to enable or disable the quoting of identifiers for the SHOW CREATE TABLE and SHOW CREATE DATABASE statements.

We can see in the above example that the database name is enclosed in quotes, which reflects that quoted identifiers are enabled in our sql_quote_show_create system variable.

Checking for Quoted Identifiers

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.

Removing the Quotes

We can disable the quotes by setting the sql_quote_show_create to 0:

SET sql_quote_show_create = 0;

Now let’s run the SHOW CREATE DATABASE statement again:

SHOW CREATE DATABASE sakila;

Result:

+----------+--------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                |
+----------+--------------------------------------------------------------------------------------------------------------------------------+
| sakila   | CREATE DATABASE sakila /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+--------------------------------------------------------------------------------------------------------------------------------+

This time there are no quotes around the database name.