Category: PHP

When it comes to character support I think the only thing that should ever be used is Unicode. That's right, I said it. However, when it comes to support in MySQL, things get a little bit murky.

I never had too much of an issue using plain ol' utf8_general_ci, however when trying to add language support for Gothic (tested because it's rare), I ran into a serious issue:

Incorrect string value: '\xF0\x90\x8C\xB0\xF0\x90...'

Son of a …

This issue is caused by the fact UTF-8 in MySQL isn't fully supported by utf8 the character set, it only supports a maximum of 3-byte characters. If you want something more realistic you're going to need to have at least MySQL 5.5.3 and you're going to have to use utf8mb4 not regular utf8. Yes, seriously.

Make sure you read through all this before trying anything, because there are edge issues, especially with indices (indexes) which you may need to consider.

Also back up your data first.

Updating Database

I'll be working under the assumption that you want your entire database to be utf8mb4, but if you don't then you'll have to adjust a bit, but seriously reconsider joining the 21st century if you're not using unicode. I'm also assuming you want case insensitive text, and if you don't, replace utf8mb4_unicode_ci with utf8mb4_bin — most people want case insensitive text in most cases.

Update the default character set and collation for your database:

ALTER DATABASE `mydatabase` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Updating Tables

First we need to change the default character set, this way when you add new columns in the future, or whatever, you don't need to worry about adding all of the character set specification:

ALTER TABLE `mytable1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Updating Table Columns

Now, you can convert one column at a time, and this may be what you wish to do if you require different character sets for your CHAR, VARCHAR, and TEXT columns, here's how you do that:

ALTER TABLE `mytable1` CHANGE `mycolumn` `mycolumn` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT  '';

Now obviously you're going to want to make sure that you're converting to the same column type and length, etc, the above is for example only and if you copy/paste it, you may screw up your column schema. Essentially you're just using ALTER TABLE CHANGE on the column in order to change the character set to utf8mb4 and collation to utf8mb4_unicode_ci.

If on the other hand you just want to change the entire the entire table at once, you can do:

ALTER TABLE `mytable1` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Updating Table Indices

When changing the character type, you may run into this on InnoDB:

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

or this on MyISAM:

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

Ah, CRAP!

Oh snippy snap, snap, there are solutions:

If you're using InnoDB on MySQL 5.6.3 or higher you can enable innodb_large_prefix in your MySQL config file (more information in manual here), but if you aren't you can take a few steps to work it out the old way:

1. Make note of the conflicting index, it's going to likely be one which is something like VARCHAR(255) or an index across multiple columns which includes VARCHAR. Make note of the index name, type, and which column(s) it crosses.
2. In my own scenario, I had a lot of columns which included some sort of VARCHAR(254) and ID which was binary(20).  Now it seems like 254+20 = 274, and hey that's less than 767 (or 1000) so what's the deal?Well, not so fast there, Professor.MySQL doesn't count literal bytes in VARCHAR when it comes to Unicode, rather potential Unicode bytes are themselves counted as a byte (wait, what?).So if the column is 254 and it's utf8 that means the actual potential length is literally (254 * 3) bytes, and with utf8mb4 it's (254 * 4). So really the length of the key you're trying to create is ((254 * 4) + 20).InnoDB only allows a maximum of 255 bytes for the column in an index with utf8 and 191 bytes for utf8mb4.So if you need the entire column indexed, you aren't going to want to change the character set for that column(s), and instead I recommend changing all others one by one (as seen in the Table Columns section) rather than trying to convert the entire table. However if you do not need the entire column to be index, and in certain cases I did not.Drop the index:

   ALTER TABLE `mytable1` DROP INDEX `theindex`;

   Then recreate it with the offending column(s) limited to 191:

   ALTER TABLE `mytable1` ADD INDEX `theindex` (`mycolumn` (191));

   or if across multiple columns (assuming mycolumnb is not utf8 for example):

   ALTER TABLE `mytable1` ADD INDEX `theindex` (`mycolumn` (191),`mycolumnb`);

As long as the indices are the same, and in the same column order, you should receive the same benefits for the indices without worrying about redoing your queries.

Additional Notes and Considerations

If a column is not being used for search and case insensitivity isn't an issue, instead of using CHAR or VARCHAR, I suggest using BINARY and VARBINARY. Not only is comparison vastly faster, but also there's less to worry about as far as character set issues go, i.e. they don't matter. Further also VARBINARY is literal length so the UTF-8 limitations described in the index section of this post do not apply, so you can get the full width for your index.

Additionally instead of using TEXT, use BLOB, for the same reasons, but also realise the same limitations apply, such as no fulltext searching.

In summation, if you don't need case sensitivity and you don't need fulltext search, consider BINARY, VARBINARY, and BLOB over CHAR, VARCHAR, and TEXT, it'll be a lot easier to deal with when it comes to Unicode.

You can learn more about this on my MySQL performance, using case insensitive columns post.

Database Connections

Depending on your programming language, you may need to specify when connecting which chartype to use (you can also, in most cases, specify this on configuration, see that section at the bottom), this usually can be done by sending this query  right after connection:

SET NAMES utf8mb4;

Configuration

You can edit your my.cnf (or my.ini on Windows) and make these changes to the appropriate sections of the configuration file (applicable to MySQL 5.6, older versions may need adjusted configuration):

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci