MySQL in a Nutshell, 2nd Edition

Name

ALTER DATABASE

Synopsis

ALTER {DATABASE|SCHEMA} database
   [DEFAULT] CHARACTER SET character_set |
   [DEFAULT] COLLATE collation

Use this statement to alter settings for a database. Version 4.1.1 of MySQL introduced this function and added a file named db.opt containing the database settings to the database directory. Currently, two options are available: CHARACTER SET and COLLATE. Here are the contents of a typical db.opt file:

default-character-set=latin1
default-collation=latin1_swedish_ci

Although an administrator can edit the file manually, it may be more robust to use the ALTER DATABASE statement to change the file. It’s synonymous with ALTER SCHEMA as of version 5.0.2 of MySQL. The ALTER privilege is necessary for this statement.

The CHARACTER SET option can set the first line shown, which specifies the default database character set that will be used. The COLLATE option can set the second line, which specifies the default database collation (how the character data is alphabetized). Here’s an example of the use of this statement:

ALTER DATABASE human_resources
CHARACTER SET latin2_bin
COLLATE latin2_bin;

Notice that both options may be given in one SQL statement. The DEFAULT keyword is unnecessary, but it is offered for compatibility with other database systems. Beginning with version 4.1.8 of MySQL, if the name of the database is omitted from this SQL statement, the current database will be assumed. To determine the current database, use the DATABASE() function:

SELECT DATABASE(); ...