MySQL in a Nutshell, 2nd Edition

Name

CREATE DATABASE

Synopsis

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

This statement creates a new database with the name given. As of version 5.0.2 of MySQL, the keyword DATABASE is synonymous with SCHEMA 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 cannot be longer than 64 bytes (not characters) in size. The system uses Unicode (UTF-8), so any character that is 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 quotation 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 backslashes, 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 reserved word, you must always enclose it in quotes when referring to it.

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 to change the name from within MySQL, as you may destroy data. Instead, there are a couple of methods you can use. One is to shut down 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. For more options with this utility, see Chapter 16.

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 DATABASE sales_prospects
CHARACTER SET latin1
COLLATE latin1_bin;

There is no equals sign before the value given for each option and no comma between the first and second option. Here are 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 new versions of MySQL. If you need one of the new ones, you’ll have to upgrade your server to the new version.

Table of Contents for MySQL in a Nutshell, 2nd Edition

Name

Synopsis

Table of Contents for
MySQL in a Nutshell, 2nd Edition