Chapter 10. Character Set Support

Table of Contents

10.1. Character Sets and Collations in General
10.2. Character Sets and Collations in MySQL
10.3. Specifying Character Sets and Collations
10.3.1. Server Character Set and Collation
10.3.2. Database Character Set and Collation
10.3.3. Table Character Set and Collation
10.3.4. Column Character Set and Collation
10.3.5. Character String Literal Character Set and Collation
10.3.6. National Character Set
10.3.7. Examples of Character Set and Collation Assignment
10.3.8. Compatibility with Other DBMSs
10.4. Connection Character Sets and Collations
10.5. Collation Issues
10.5.1. Using COLLATE in SQL Statements
10.5.2. COLLATE Clause Precedence
10.5.3. BINARY Operator
10.5.4. Some Special Cases Where the Collation Determination Is Tricky
10.5.5. Collations Must Be for the Right Character Set
10.5.6. An Example of the Effect of Collation
10.6. Operations Affected by Character Set Support
10.6.1. Result Strings
10.6.2. CONVERT() and CAST()
10.6.3. SHOW Statements and INFORMATION_SCHEMA
10.7. Unicode Support
10.8. UTF-8 for Metadata
10.9. Character Sets and Collations That MySQL Supports
10.9.1. Unicode Character Sets
10.9.2. West European Character Sets
10.9.3. Central European Character Sets
10.9.4. South European and Middle East Character Sets
10.9.5. Baltic Character Sets
10.9.6. Cyrillic Character Sets
10.9.7. Asian Character Sets
10.10. FAQ: MySQL Chinese, Japanese, and Korean Character Sets
10.10.1. SELECT shows non-Latin characters as "?"s. Why?
10.10.2. Troubles with GB character sets (Chinese)
10.10.3. Troubles with big5 character set (Chinese)
10.10.4. Troubles with character-set conversions (Japanese)
10.10.5. The Great Yen Sign problem (Japanese)
10.10.6. Troubles with euckr character set (Korean)
10.10.7. The “Data truncated” message
10.10.8. Troubles with Access, Perl, PHP, etc.
10.10.9. How can I get old MySQL 4.0 behaviour back?
10.10.10. Why do some LIKE and FULLTEXT searches fail?
10.10.11. What CJK character sets are available?
10.10.12. Is character X available in all character sets?
10.10.13. Strings don't sort correctly in Unicode (I)
10.10.14. Strings don't sort correctly in Unicode (II)
10.10.15. My supplementary characters get rejected
10.10.16. Shouldn't it be CJKV (V for Vietnamese)?
10.10.17. Will MySQL fix any CJK problems in version 5.1?
10.10.18. When will MySQL translate the manual again?
10.10.19. Whom can I talk to?

MySQL includes character set support that enables you to store data using a variety of character sets and perform comparisons according to a variety of collations. You can specify character sets at the server, database, table, and column level. MySQL supports the use of character sets for the MyISAM, MEMORY, NDBCluster, and InnoDB storage engines.

This chapter discusses the following topics:

Character set issues affect data storage, but also communication between client programs and the MySQL server. If you want the client program to communicate with the server using a character set different from the default, you'll need to indicate which one. For example, to use the utf8 Unicode character set, issue this statement after connecting to the server:

SET NAMES 'utf8';

For more information about character set-related issues in client/server communication, see Section 10.4, “Connection Character Sets and Collations”.

10.1. Character Sets and Collations in General

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set. Let's make the distinction clear with an example of an imaginary character set.

Suppose that we have an alphabet with four letters: ‘A’, ‘B’, ‘a’, ‘b’. We give each letter a number: ‘A’ = 0, ‘B’ = 1, ‘a’ = 2, ‘b’ = 3. The letter ‘A’ is a symbol, the number 0 is the encoding for ‘A’, and the combination of all four letters and their encodings is a character set.

Suppose that we want to compare two string values, ‘A’ and ‘B’. The simplest way to do this is to look at the encodings: 0 for ‘A’ and 1 for ‘B’. Because 0 is less than 1, we say ‘A’ is less than ‘B’. What we've just done is apply a collation to our character set. The collation is a set of rules (only one rule in this case): “compare the encodings.” We call this simplest of all possible collations a binary collation.

But what if we want to say that the lowercase and uppercase letters are equivalent? Then we would have at least two rules: (1) treat the lowercase letters ‘a’ and ‘b’ as equivalent to ‘A’ and ‘B’; (2) then compare the encodings. We call this a case-insensitive collation. It's a little more complex than a binary collation.

In real life, most character sets have many characters: not just ‘A’ and ‘B’ but whole alphabets, sometimes multiple alphabets or eastern writing systems with thousands of characters, along with many special symbols and punctuation marks. Also in real life, most collations have many rules, not just for whether to distinguish lettercase, but also for whether to distinguish accents (an “accent” is a mark attached to a character as in German ‘Φ’), and for multiple-character mappings (such as the rule that ‘Φ’ = ‘OE’ in one of the two German collations).

MySQL can do these things for you:

  • Store strings using a variety of character sets

  • Compare strings using a variety of collations

  • Mix strings with different character sets or collations in the same server, the same database, or even the same table

  • Allow specification of character set and collation at any level

In these respects, MySQL is far ahead of most other database management systems. However, to use these features effectively, you need to know what character sets and collations are available, how to change the defaults, and how they affect the behavior of string operators and functions.