How character encodings work in MySQL seem to continue baffle people, at least based on the number of questions posted on Stackoverflow. Read on if you have an application that still outputs funny text like “seÃ±or” or “se�or” where “señor” is expected.
The truth about text in MySQL
Strings in MySQL consist of an encoding marker and a list of bytes. The
CHARSET function reads the encoding while
HEX can be used to read bytes:
SELECT CHARSET("€"), HEX("€") => latin1, 80
This enables MySQL to use different encodings while working on a query. You can write a query that compares text columns in different encodings and returns the results where the text is the same, rather than the bytes that are stored.
I have everything set to
utf8 and still the database gives me garbage!
That’s because of the MySQL client encoding. One query can return text in several different encodings, it’s unreasonable to expect the user software to check the encoding of each individual string. So all text returned by the MySQL client is encoded in only one encoding.
The fast and easy solution to encoding problems: set the MySQL client encoding.
How this is done depends on what you use to connect. If you work in PHP and the old MySQL functions you should use
mysql_set_charset("utf8"). If you have an old version try
mysql_query("SET NAMES utf8"). If you use MySQLi it would be
mysqli_set_charset($link, "utf8") (or
$link->set_charset("utf8") if you use the OO API).
What are the encodings in the tables good for, then?
When you create a table you can specify a character encoding for a column. It means how the MySQL server will convert text like “abc123” to bytes that can be written to disk. The encoding affects two things:
- the range of characters that can be stored, and
- the number of bytes the text will occupy on disk.
- latin1: the characters that you can store cover most European languages, and the text occupies 1 byte per character.
- utf8: you can store any(*) Unicode characters, and the text occupies from 1 to 3 bytes per character, depending on the character.
Other encodings exist, of course. For example latin2 has characters used in Eastern European languages. If you use ucs2 you can store any Unicode characters and the text occupies 2 bytes per character. If you are storing a lot of text in Japanese you may want to use ucs2 instead of utf8 because you are saving a third in the storage.
You can also specify a collation like
utf8_general_ci for a column. It means how MySQL will sort the data. This affects indices and ORDER BY clauses. Cultures have different rules for alphabetic order: for example in Swedish Ä is the second to last letter of the alphabet, while in English it’s equivalent to A. So with Swedish collation you get a < b < ä and with English collation you get a = ä < b. Use the ordering your users expect. You can read more about collations in my other post.
What if you don’t specify an encoding and collation for the column? Then MySQL will use the default specified for the table, the database, or the server.
Strings in MySQL consist of an encoding marker and a list of bytes. To convert strings from one encoding to another you can use the CONVERT(… USING …) function. What CONVERT actually does is change both the marker and the bytes so that the resulting string is the same as the original; not very useful for fixing broken text.
The solution is stripping the encoding marker from the string before passing it to CONVERT. This is done by converting the string to “binary” first:
CONVERT(BINARY broken_text USING utf8)
I’ve written a detailed case study on fixing a particularly complicated MySQL encoding problem in another post.
Let’s create a table with columns in different encodings and fill it with some data.
CREATE TABLE test_encoding ( utf8 varchar(4) CHARACTER SET utf8, latin1 varchar(4) CHARACTER SET latin1, latin2 varchar(4) CHARACTER SET latin2 ); INSERT INTO test_encoding (utf8,latin1,latin2) values ('A','A','A'); INSERT INTO test_encoding (utf8,latin1,latin2) values ('Ñ','Ñ','Ñ'); INSERT INTO test_encoding (utf8,latin1,latin2) values ('Ŕ','Ŕ','Ŕ'); INSERT INTO test_encoding (utf8,latin1,latin2) values ('☺','☺','☺'); INSERT INTO test_encoding (utf8,latin1,latin2) values ('€','€','€');
When you select the data from this table with
select * from test_encoding you get the following results:
+------+--------+--------+ | utf8 | latin1 | latin2 | +------+--------+--------+ | A | A | A | | Ñ | Ñ | ? | | Ŕ | ? | Ŕ | | ☺ | ? | ? | | € | € | ? | +------+--------+--------+
Even though each column is encoded differently everything is still readable. This is thanks to the MySQL client encoding. The characters that could not be encoded are replaced with question marks: there is no Ñ in latin2, nor is there a Ŕ in latin1. The smiley face is not present in either.
The last line is interesting: the euro sign can be encoded in latin1 although technically it’s not really a latin1 character. This shows that MySQL’s latin1 is not really the standard ISO-8859-1 encoding, commonly known as “ISO latin 1”. In reality it is Windows-1252, the “Western European” encoding that also has curly quotes and other niceties where ISO-8859-1 has unprintable control characters.
To see that MySQL really is storing the columns in different encodings you could do this:
mysql> select utf8,hex(utf8),hex(latin1) from test_encoding; +------+-----------+-------------+ | utf8 | hex(utf8) | hex(latin1) | +------+-----------+-------------+ | A | 41 | 41 | | Ñ | C391 | D1 | | Ŕ | C594 | 3F | | ☺ | E298BA | 3F | | € | E282AC | 80 | +------+-----------+-------------+
What happens when we compare two columns encoded differently?
mysql> select utf8,latin1 from test_encoding where utf8=latin1; +------+--------+ | utf8 | latin1 | +------+--------+ | A | A | | Ñ | Ñ | | € | € | +------+--------+
MySQL knows that the character repertoire of latin1 is a subset of utf8, so it converts the latin1 column into unicode so that it can be compared. If however you try to compare latin1 with latin2 this is what happens:
mysql> select latin1,latin2 from test_encoding where latin1=latin2; ERROR 1267 (HY000): Illegal mix of collations (latin1_bin,IMPLICIT) and (latin2_bin,IMPLICIT) for operation '='
Since neither latin1 and latin2 is a subset of the other, MySQL cannot compare the columns. To be able to compare them you have to convert at least one to unicode, e.g. by using
CONVERT(.. USING ..):
select latin1, latin2 from test_encoding where convert(latin1 using utf8)=latin2;
I hope this information is enough to solve any character encoding problems you may have with MySQL once and for all.
(*) Did I say
utf8 can store any Unicode characters? That’s not really true. MySQL’s utf8 and ucs2 only store unicode from the basic multilingual plane (BMP), which includes “only” the characters U+0000 through U+FFFF. If you have MySQL 5.5 or later you have additional encodings to choose from: utf16, utf32, utf8mb4. These encodings You can read the details, as usual, in the official MySQL documentation.