Name

ALTER DATABASE

Synopsis

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

Use this statement to alter settings for a database. Version 4.1.1 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

The CHARACTER SET option sets the first line, which specifies the default database character set that will be used. The COLLATE option sets the second line, which specifies the default database collation. Here’s an example of how you can use 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 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.

Get MySQL in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial