I've run into a real headache with MySQL's collations and non-BMP characters (ones with Unicode codepoints above U+FFFF).
Basically, given a table and data like:
CREATE TABLE `math` ( `id` int(11) NOT NULL AUTO_INCREMENT, `symbols` varchar(32) character set utf8mb4 not null, PRIMARY KEY (`id`), UNIQUE KEY `symbols` (`symbols`) ); INSERT INTO `math` VALUES (1,'𝕂');
(You may not have a font to display the character in the string literal above. It's U+1D542 MATHEMATICAL DOUBLE-STRUCK CAPITAL K)
things look OK:
mysql> select * from math; +----+---------+ | id | symbols | +----+---------+ | 1 | 𝕂 | +----+---------+ 1 row in set (0.00 sec) mysql> select * from math where symbols = '𝕂'; +----+---------+ | id | symbols | +----+---------+ | 1 | 𝕂 | +----+---------+ 1 row in set (0.00 sec)
So far so good. But then there's this crap:
mysql> select * from math where symbols = '𝕃'; +----+---------+ | id | symbols | +----+---------+ | 1 | 𝕂 | +----+---------+ 1 row in set (0.00 sec)
mysql> INSERT INTO `math` VALUES (2,'𝕃'); ERROR 1062 (23000): Duplicate entry '?' for key 'symbols'
(The string literal above has U+1D543 MATHEMATICAL DOUBLE-STRUCK CAPITAL L. Note that MySQL's error message has a
?, but the U+1D542 in the results of the
SELECT above does dispaly correctly for me, so there don't seem to be encoding issues as far as IO with the server.)
(Code above updated; it originally had
1 for the primary key, which fails for obvious reasons.)
Screenshot for those with font issues:
So, MySQL thinks these two characters are the same? I know it case-folds, but this isn't a matter of casing.
Needless to say, I didn't even realize I had this problem till it came up in production, because the real-world data involved rarely differs on only these characters. However, this is totally unacceptable collation behavior.
Switching to the binary collation does fix it, however I'm using Django to access the database, and when I use a binary collation it then gives me bytes instead of characters (I can decode them myself, but that's a big pain).
I'm guessing the issue has something to do with these character being outside the BMP, but it still surprising bad behavior.
Is there a way to get MySQL to use sensible collation, short of writing and installing one myself?
I suspect the non-BMP characters are the problem, since I also tried:
- ⛇ (U+26C7 BLACK SNOWMAN) (a BMP character) works fine.
- 🌀 (U+1F300 CYCLONE) (a SMP character) gives the same error as above
- 🂡 (U+1F0A1 PLAYING CARD ACE OF SPADES) (SMP) same error
- 𠀃 (U+20003 CJK UNIFIED IDEOGRAPH-20003) (SIP) same error
(I can't link these to codepoints.net, since I don't have enough reputation. Should be fairly obvious what their URLs are though.)
I'm using MySQL 5.5.40 on Ubuntu 14.04.