CREATE DATABASE

Overview

Creates a database.

See also: SQL Statements for MariaDB Xpand 6.1, in 6.0, and in 5.3

USAGE

Common Syntax:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] <db_name>
   [ [DEFAULT] COLLATE [=] <xpand_collation_name> ]
   [ [DEFAULT] {CHARACTER SET | CHARSET} [=] <xpand_charset_name> ]

DETAILS

This statement creates a database.

For Xpand, all objects are case-insensitive.

EXAMPLES

CREATE DATABASE

To create a database:

CREATE DATABASE db1;

IF NOT EXISTS

By default, if you try to create a database with a name that already exists, an error is raised.

To prevent an error if the named database is already present, add the IF NOT EXISTS clause:

CREATE DATABASE IF NOT EXISTS db1;

If the specified database does not exist, it will be created. If the specified database already exists, the query does not raise an error.

Specify Character Set and Collation

Character sets define which characters can be used to store information in a database. A collation is a set of rules for sorting and comparing character sets, for example:

  • In a case-insensitive collation, "Sofia" and "sofia" would be considered the same name

  • In a case-sensitive collation, "Robert" and "robert" would be considered different names

If you do not specify a character set and collation for a database, the defaults will be used. The default character set and collation are determined by the character_set_server and collation_server system variables. If the system variables are not changed, the default character set is utf8, and the default collation is utf8_general_ci.

To set a specific character set and/or collation to be used as the default for all newly created tables within this database:

CREATE DATABASE db1
   DEFAULT CHARACTER SET = 'utf8mb4'
   DEFAULT COLLATE = 'utf8mb4_unicode_ci';

The DEFAULT reserved word and equal (=) sign are optional. This is the equivalent statement:

CREATE DATABASE db1
   CHARACTER SET 'utf8mb4'
   COLLATE 'utf8mb4_unicode_ci';

You can set a different database character set and/or collation for tables within the database using the CREATE TABLE or ALTER TABLE statement.

For additional information on character sets and collations, see "Character Sets in MariaDB Xpand" and "Collations in MariaDB Xpand".

CHANGE HISTORY

Release Series

History

6.1

  • Present starting in MariaDB Xpand 6.1.0.

6.0

  • Present starting in MariaDB Xpand 6.0.3.

5.3

  • Present starting in MariaDB Xpand 5.3.13.