CREATE DATABASE

USAGE

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:

CREATE DATABASE SCHEMA IF NOT EXISTS CREATE OR REPLACE DATABASE SCHEMA <db_name> "Create Database Option Diagram"

For "Create Database Option Diagram":

DEFAULT CHARSET CHAR CHARACTER SET = := <charset_name> DEFAULT DEFAULT COLLATE = := <collation_name> DEFAULT COMMENT = := <text_string>

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.

DETAILS

The CREATE DATABASE SQL statement creates a database.

When creating a new database, consider whether there is a need to update operational practices or documentation. For example, it might be necessary to update your backup procedure to include or omit the new database. New databases may also have security, audit, capacity planning, other planning requirements.

Existing Databases

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

Condition

Behavior

CREATE DATABASE

  • Default behavior without IF NOT EXISTS and without OR REPLACE

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

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

  • When enabled, the general query log and binary log will show the CREATE DATABASE statement.

CREATE DATABASE IF NOT EXISTS

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

  • If the named database already exists, a warning will be raised. The existing database is not changed.

  • When enabled, the general query log and binary log will show the CREATE DATABASE IF NOT EXISTS statement.

CREATE OR REPLACE DATABASE

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

  • If the named database already exists:

    • The named database will be dropped and recreated.

    • All objects in the database will be deleted.

    • Typically, no warning or error will be raised.

  • When enabled, the general query log and binary log will show the CREATE OR REPLACE DATABASE 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:

Subject

Description

Table defaults

  • The character set default and collation default for all newly created tables within that database (CREATE TABLE).

Stored routines defaults

  • The character set default and collation default for all newly created stored functions within that database (CREATE FUNCTION).

  • The character set default and collation default for all newly created stored procedures within that database (CREATE PROCEDURE).

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

CHARACTER SET

COLLATE

Character Set used

Collation used

Not specified

Not specified

As per the character_set_server system variable as of the time when the database is created

As per the collation_server system variable as of the time when the database is created

Specified

Not specified

Character set as specified

As per the default collation name associated with the character set

Not specified

Specified

As per the character set name associated with the collation

Collation as specified

Specified

Specified

Character set as specified

Collation as specified

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:

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

SCHEMA

PARAMETERS

Parameter

Details

<db_name>

Required. Specifies the database name to be created. Must be a valid identifier.

<collation_name>

  • Used with COLLATE to specify a collation.

  • Must be a valid collation name or the bareword DEFAULT.

  • If DEFAULT is specified (COLLATE = DEFAULT), collation is as per the collation_server system variable as of the time when the database is created.

<charset_name>

  • Used with CHARACTER SET to specify a character set.

  • Must be a valid character set name or the bareword DEFAULT.

  • If DEFAULT is specified (CHARACTER SET = DEFAULT), character set is as per the character_set_server system variable as of the time when the database is created.

<text_string>

Used with COMMENT to set a comment on the database. Maximum length is 1024 characters. Minimum length is 0 characters (an empty '' string). One comment can exist per database. This feature requires MariaDB Enterprise Server 10.5.3-1 or greater, or MariaDB Community Server 10.5.0 or greater.

SKYSQL

PRIVILEGES

Action

Required Privileges

CREATE DATABASE

CREATE OR REPLACE DATABASE

EXAMPLES

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 |
+------+----------------------------------------------------+

ERROR HANDLING

Error Code

Details

Error 1007

  • Condition:

    ERROR 1007 (HY000): Can't create database 'x'; database exists
    
  • Cause: CREATE DATABASE attempted to create a named database that already exists. If this error condition is not desired, see "Existing Databases".

Error 1044

  • Condition:

    ERROR 1044 (42000): Access denied for user 'user_name'@'%' to database 'db_name'
    
  • Cause: The user does not have sufficient privileges to perform this operation. A CREATE DATABASE requires CREATE privilege. A CREATE OR DROP requires CREATE privilege and DROP privilege.

Error 1064

  • Condition:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'binary'
    
  • Cause: Some character sets and collations, such as binary, are also reserved words. This error can be avoided when specifying a character set or collation by quoting the name.

Error 1192

  • Condition:

    ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
    
  • Cause: CREATE DATABASE cannot be run while tables are locked (LOCK TABLES). If autocommit=OFF, and a transaction is in-progress, CREATE DATABASE will trigger an implicit commit.

Error 1221

  • Condition:

    ERROR 1221 (HY000): Incorrect usage of OR REPLACE and IF NOT EXISTS
    
  • Cause: OR REPLACE and IF NOT EXISTS cannot be combined.

Error 1273

  • Condition:

    ERROR 1273 (HY000): Unknown collation: 'x'
    
  • Cause: An invalid collation has been specified with COLLATE. Specify a valid collation name or DEFAULT

Error 1302

  • Condition:

    ERROR 1302 (HY000): Conflicting declarations: 'CHARACTER SET x' and 'CHARACTER SET y'
    
  • Cause: Multiple character sets have been specified with CHARACTER SET

Error 1302

  • Condition:

    ERROR 1302 (HY000): Conflicting declarations: 'COLLATE x' and 'COLLATE y'
    
  • Cause: Multiple collations have been specified with COLLATE

Error 4160

  • Condition:

    ERROR 4160 (HY000): Comment for database 'db_name' is too long (max = 1024)
    
  • Cause: Comment cannot exceed 1024 characters.

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES