CREATE DATABASE

Common Syntax

CREATE [OR REPLACE] { DATABASE | SCHEMA } [IF NOT EXISTS] <db_name>
   [ [DEFAULT] COLLATE [=] <collation_name> ]
   [ [DEFAULT] [CHARACTER SET] [=] <charset_name> ]
   [ COMMENT [=] "<text_string>"]

Railroad Diagram

Top-level diagram:

For "Create Database Option Diagram":

Compatibility Notes

Some syntax is supported for compatibility with other databases.

When specifying the character set:

• If absent, the DEFAULT reserved word before CHARACTER SET is assumed.

• If present, the equal (=) sign after CHARACTER SET is ignored.

• CHARSET is a synonym for CHARACTER SET.

• CHAR SET is a synonym for CHARACTER SET.

When specifying the collation:

• If present, the DEFAULT reserved word before COLLATE is ignored.

• If present, the equal (=) sign after COLLATE is ignored.

Version-Specific Behavior

The details on this page, except as noted, are intended to cover the following product versions:

• MariaDB Enterprise Server 10.2 through MariaDB Enterprise Server 23.08

• MariaDB Community Server 10.2 through MariaDB Community Server 11.2

Some features or behavior described on this page are only available with certain versions:

• COMMENT support

  • Introduced in MariaDB Enterprise Server 10.5.3-1 and MariaDB Community Server 10.5.0.

  • Available in MariaDB Enterprise Server 10.5 and greater, and MariaDB Community Server 10.5 and greater.

Existing Databases

With CREATE DATABASE, if a named database already exists, behavior depends on the SQL statement:

IF NOT EXISTS cannot be used with CREATE OR REPLACE DATABASE.

Character Set and Collation

The character set and collation of a database impact the following:

The character set and collation of the database depend on what has been specified in the SQL statement creating that database:

The character set and collation specified in a database are set at the time the database is created.

The character set and collation specified in the database can be changed after creation using ALTER DATABASE.

For additional information on character sets and collations, see:

• information_schema.COLLATIONS

• Supported Collations

• information_schema.CHARACTER_SETS

• Supported Character Sets

Implicit Commit Behavior

If autocommit=OFF and a transaction is in-progress, CREATE DATABASE will trigger an implicit commit.

SYNONYMS

In MariaDB Server, schema is a synonym for database.

The following SQL statements are synonyms for CREATE DATABASE:

• CREATE SCHEMA

PARAMETERS

PRIVILEGES

Display Database Details

It is often useful to examine existing database details when creating a new database, or to examine the details of a database after it is created.

• SHOW DATABASES can be used to display a list of databases.

• SHOW CREATE DATABASE will output a CREATE DATABASE statement that could be used to create the given database. This output does not include the objects within the database. The statement shown may not match the actual statement used when the database was created.

• Details about databases can queried in information_schema.SCHEMATA

CREATE DATABASE

To create a database:

CREATE DATABASE test_db;

With the default behavior of CREATE DATABASE:

• If the named database does not already exist, it will be created.

• If the named database already exists, an error will be raised:

  ERROR 1007 (HY000): Can't create database 'test_db'; database exists
  

CREATE OR REPLACE DATABASE

With CREATE DATABASE, if you try to create a database and that named database already exists, an error will be raised.

When instead CREATE OR REPLACE DATABASE is used:

• If the named database does not already exist, it will be created.

• If the named database already exists, it will be dropped and re-created. All objects in the database will be deleted. Typically, no warning or error will be raised.

To create a database, replacing the existing database if one already exists with this database name:

CREATE OR REPLACE DATABASE test_db;

IF NOT EXISTS

With CREATE DATABASE, if you try to create a database and that named database already exists, an error will be raised.

When instead CREATE DATABASE IF NOT EXISTS is used:

• If the named database does not already exist, it will be created.

• If the named database already exists, it will not be changed. A warning will be raised.

For example, with IF NOT EXISTS the initial database creation is completed normally:

CREATE DATABASE IF NOT EXISTS test_db;

Subsequent attempt to create the same named database generates a warning:

CREATE DATABASE IF NOT EXISTS test_db;
SHOW WARNINGS;

+-------+------+--------------------------------------------------+
| Level | Code | Message                                          |
+-------+------+--------------------------------------------------+
| Note  | 1007 | Can't create database 'test_db'; database exists |
+-------+------+--------------------------------------------------+

Specify Character Set

Character sets define which characters can be used to store information in a database. For more information, see Character Sets in MariaDB Enterprise Server.

Collation and character set can both be specified. In the following example, the database created will have the character set and collation specified.

CREATE DATABASE test_db
   DEFAULT CHARACTER SET = 'utf8mb4'
   DEFAULT COLLATE = 'utf8mb4_general_ci';

In the following example, the character set is specified but the collation is not specified. In this case, the database created will have the specified character set and the collation will be the default collation name associated with the character set.

CREATE DATABASE test_db
   DEFAULT CHARACTER SET = 'utf8mb4';

See "Character Set and Collation" for detail on behavior when neither character set nor collation are specified.

Specify Collation

A collation is a set of rules for sorting and comparing character sets. For more information, see Collations in MariaDB Enterprise Server.

Collation and character set can both be specified. In the following example, the database created will have the character set and collation specified.

CREATE DATABASE test_db
   DEFAULT CHARACTER SET = 'utf8mb4'
   DEFAULT COLLATE = 'utf8mb4_general_ci';

In the following example, the collation is specified but the character set is not specified. In this case, the database created will have the specified collation and will have the character set associated with the collation.

CREATE DATABASE test_db
   DEFAULT COLLATE = 'utf8mb4_general_ci';

See "Character Set and Collation" for detail on behavior when neither character set nor collation are specified.

Specify Comment

A comment can be associated with a database. A comment can help explain (to humans) the purpose of a database throughout the database's lifespan.

A comment can be between zero and 1024 characters. One comment can exist per database.

The COMMENT option was added in MariaDB Enterprise Server 10.5.3-1 and MariaDB Community Server 10.5.0. This feature is available in MariaDB Enterprise Server 10.5 and greater, and MariaDB Community Server 10.5 and greater.

CREATE DATABASE presentations
  COMMENT 'Presentations for conferences';

Implicit Commit

If autocommit=OFF and a transaction is in-progress, CREATE DATABASE will trigger an implicit commit.

-- Disable autocommit.
SET autocommit=0;
-- Begin a new explicit transaction.
BEGIN;
CREATE TABLE autocommit_test (
  id integer,
  description varchar(128)
);
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (7, 'autocommit=0, explicit transaction explicit commit');
-- Explicitly commit the explicit transaction.
COMMIT;
-- Start an explicit transaction.
BEGIN;
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (8, 'autocommit=0, explicit transaction rolled back');
-- Perform explicit rollback of the explicit transaction.
-- Row with id=8 will not appear in output.
ROLLBACK;
-- Start an explicit transaction.
BEGIN;
-- In an explicit transaction, insert a row.
INSERT INTO autocommit_test values (20, 'autocommit=0, implicit commit with CREATE DATABASE');
-- Create a database, which causes an implicit commit.
CREATE DATABASE test_db;
-- Initiate a rollback, which will have no effect since the transaction was committed.
ROLLBACK;
-- Display the resulting table, which contains two rows.
-- One row was committed explicitly with COMMIT, and one implicitly with CREATE DATABASE.
SELECT * FROM autocommit_test WHERE id>5;

+------+----------------------------------------------------+
| id   | description                                        |
+------+----------------------------------------------------+
|    7 | autocommit=0, explicit transaction explicit commit |
|   20 | autocommit=0, implicit commit with CREATE DATABASE |
+------+----------------------------------------------------+