ALTER DATABASE

This MySQL statement may be used to change the contents of a db.opt file, which contains the default CHARACTER SET and COLLATE values.

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).

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

Explanation

Use this MySQL 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.

Examples

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 MySQL statement, ALTER DATABASE 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 MySQL 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. To determine the current database, use the DATABASE() function:

SELECT DATABASE();

+----------------+
| DATABASE()     |
+----------------+
| mysqlresources |
+----------------+

See the explanations in our MySQL documentation for the SHOW CHARACTER SET and SHOW COLLATION SQL statements for more information on character sets and collations.