MySQL supports different character sets and collations to provide functionality for users who want to create database applications in different languages.
The character set determines how MySQL stores character data. Each letter in a character string is represented internally by a numeric value, and the character set determines the character mapping of each number.
A collation is a set of rules for comparing characters in a character setessentially, it defines the alphabetical order for a character set. For instance, the character Ö in a German character set is usually treated as OE for ordering purposes.
Collation rules can also take into account case sensitivity. You saw in Lesson 7, "String Operators and Functions," that string comparisons are not case sensitive unless you include the BINARY keyword. In fact, BINARY is a collation type that tells MySQL to consider the underlying character map values. The default collation for MySQL is not case sensitive, so it considers the character P to have the same value as p when ordering.
The actual character sets available on your system depend on those supported by your operating system. To see the character sets available, issue the SHOW CHARACTER SET command. The following output shows just a few sample lines of outputyour actual output will vary.
mysql> SHOW CHARACTER SET; +---------+-------------------+-------------------+--------+ Charset | Description | Default collation | Maxlen | +---------+-------------------+-------------------+--------+ | ascii | US ASCII | ascii_general_ci | 1 | | hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 | | ujis | EUC-JP Japanese | ujis_japanese_ci | 3 | | greek | ISO 8859-7 Greek | greek_general_ci | 1 | ...
The Maxlen column indicates the number of bytes required to store a character using each character set. Some complex alphabets require 2 or 3 bytes per character.
To specify the default character set and collation for a new database, use the CHARACTER SET and COLLATION attributes in the CREATE DATABASE statement. For instance, the following statement would create a new database using a Greek character set and the greek_general_ci collation:
mysql> CREATE DATABASE greekdb -> CHARACTER SET greek COLLATE greek_general_ci; Query OK, 1 row affected (0.00 sec)