This chapter is from the book

This chapter is from the book

Improved support for character set handling was added to MySQL in Version 4.1. The features described here are as implemented in MySQL 4.1.1. (MySQL 4.1.0 has some but not all of these features, and some of them are implemented differently.)

This chapter discusses the following topics:

What are character sets and collations?

The multiple-level default system

New syntax in MySQL 4.1

Affected functions and operations

Unicode support

The meaning of each individual character set and collation

Character set support currently is included in the MySISAM, MEMORY (HEAP), and (as of MySQL 4.1.2) InnoDB storage engines. The ISAM storage engine does not include character set support; there are no plans to change this, because ISAM is deprecated.

3.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, 'c' = 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.

Now, 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'. Now, 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 case insensitivity but also accent insensitivity (an
"accent" is a mark attached to a character as in German
'ö') and multiple-character mappings (such as the rule that
'ö' = 'OE' in one of the two German collations).

MySQL 4.1 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, not only is MySQL 4.1 far more flexible than MySQL 4.0, it
also is far ahead of other DBMSs. However, to use the new features effectively,
you will need to learn what character sets and collations are available, how to
change their defaults, and what the various string operators do with them.