CREATE SCHEMA

This MySQL statement is synonymous with CREATE DATABASE.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE {DATABASE|SCHEMA} [IF NOT EXISTS] database [options]

Explanation

This MySQL statement creates a new database with the name given. As of version 5.0.2 of MySQL, the keyword SCHEMA is synomymous with DATABASE wherever used in any SQL statement. You can use the IF NOT EXISTS flag to suppress an error message when the statement fails if a database with the same name already exists.

A database name in MySQL cannot be longer than 64 bytes (not characters) in size. The system uses Unicode (UTF-8), so any character part of the UTF-8 character set may be used. The name cannot be the ASCII value of 0 (0x00) or 255 (0xff)—these are reserved. Database names should not include single or double quote marks or end with a space. If you want a database name to include quotes, though, you will have to enable the SQL mode of ANSI_QUOTES. This can be done with the --sql-mode server option. As of version 5.1.6 of MySQL, database names can contain back-slashes, forward-slashes, periods, and other characters that may not be permitted in a directory name at the filesystem level. If you use a name that is a reserve word, you must always enclose it in quotes when referring to it.

Examples

Special characters in the name are encoded in the filesystem names. If you upgrade your system to a new version of MySQL and you have a database that has special characters in its name, the database will be displayed with a prefix of #mysql50#. For instance, a database named human-resources will be displayed as #mysql50#human-resources. You won't be able to access this database. Don't try change the name from within MySQL as you may destroy data. Instead, there are a couple of methods that may be used. One would be to shutdown MySQL, go to the MySQL data directory, and rename the subdirectory that contains the database to a name without the unacceptable character (e.g., from human-resources to human_resources) and then restart MySQL. Another method would be to use the mysqlcheck utility like so:

mysqlcheck --check-upgrade --fix-db-names

The --fix-db-names option was added in version 5.1.7 of MySQL.

As of version 4.1.1, a db.opt file is added to the filesystem subdirectory created for the database in the MySQL server's data directory. This file contains a couple of settings for the database. You can specify these settings as options to this SQL statement in a comma-separated list.

Currently, two options are available: CHARACTER SET and COLLATE. Here is an example of how you can create a database with both of these options:

CREATE SCHEMA sales_prospects
CHARACTER SET latin1
COLLATE latin1_bin;

There is no equal sign before the value given for each option and no comma between the first and second option. Below is the contents of the db.opt file created for this statement:

default-character-set=latin1
default-collation=latin1_bin

For a list of character sets available on your system, use the SHOW CHARACTER SET statement. For a list of collation possibilities, use the SHOW COLLATION statement. MySQL occasionally adds new character sets and collations to the server. If you need one of the new ones, you'll have to upgrade your server to the new edition.