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


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

   [DEFAULT] CHARACTER SET character_set |
   [DEFAULT] COLLATE collation


This MySQL statement is synonymous with the ALTER DATABASE statement. They both may be used to change the contents of a db.opt file.


Here are the contents of a typical db.opt file:


Although an administrator can edit the file manually, it may be more robust to use this MySQL statement or ALTER DATABASE statement to change the file. These two MySQL statements are synonymous 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 SCHEMA 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 SCHEMA() function:


| SCHEMA()       |
| mysqlresources |

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