Skip navigation links
9.1.2 Character Sets and Collations in MySQL »
Section Navigation      [Toggle]

9.1.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 is 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 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 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.


User Comments

Posted by David L Lambert on December 28 2004 3:33pm[Delete] [Edit]

Note that a MySQL database is only one part of a system for storing data that includes extended characters, and your client tools and languages will also need to support the characters you want in your application. For instance, the MySQL comand line client [1] does not allow direct entry of latin-1 characters, let alone Unicode. Furthermore, it doesn't automatically detect the language-environment, so there are some cases where properly entered characters get displayed as soething else; for instance, in US Win95, the command-prompt boxes use CP437, and a lot of printers use that same character set by default.

The MySQL Control Center under MS Windows and Microsoft Access are pretty good about representing international characters faithfully. On RedHat Linux 9, the Control Center displays each non-ASCII character as two boxes (presuably because it internally converts to UTF8, but the X server expects latin1).

JDBC [2] does a good job of handling international characters, because Java uses Unicode internally and the MySQL driver picks the server character set automatically.

Perl supports Unicode internally, thus DBI and CGI [3] would seem like a good set of tools to get at multilingual data. However, DBD::MySQL doesn't actually do any conversion, so in perl 5.6 you might need to do something like

($name) = $dbh->selectrow_array( ... );
$name = pack("U*",unpack("C*",$name));
print "$name\n";

to get the characters in the proper format, or you could add an explicit

$dbh->do("SET character_set_results="utf8"');

before doing anything else.

Note also that CGI.pm (most recent version) sets the output encoding to iso-8859-1 by default, so a CGI script will need to convert back from UTF on output. Unicode::Lite might prove useful in some cases. Perl 5.8 [4] has a slightly different model for handling Unicode that might (untested) require a line similar to the following when the server has been set to respond with Unicode:

use Encode 'decode_utf8';
$name = decode_utf8($name);

References:

1. http://bugs.mysql.com/bug.php?id=7491
2. http://java.sun.com/j2se/1.4.2/docs/api/java/sql/package-summary.html
3. http://search.cpan.org/~lds/CGI.pm-3.05/CGI.pm
4. http://www.perldoc.com/perl5.8.4/pod/perluniintro.html

Posted by Satanic Puppy on October 2 2007 9:00pm[Delete] [Edit]

I had a data translation issue between a mysql database running remotely and a local instance of Perl and the previous posters suggestion of adding:

$dbh->do("SET character_set_results='utf8'");

to the code cleared it right up. Thanks a bunch; I knew what the problem was, but damned if I could find the solution.

Add your own comment.