6

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)

and

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: enter image description here

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.

  • As I look at this page, I see Firefox is trying to use surrogates for the SMP characters when it displays the URL. I guess I can't blame MySQL for having non-BMP character bugs... – morganwahl Jan 9 '15 at 20:09
  • In SQL Server, we have to use a Supplementary Characters (_SC) collation to handle this correctly. I have no idea if MySQL has SC collations though. – Paul White Jan 10 '15 at 13:52
  • Thanks @PaulWhite ! The only additional links I wanted to use were for those other example characters at the bottom, and it's not really worth the trouble since you can easily guess their URLs on codepoints.net. – morganwahl Jan 12 '15 at 16:29
  • For comparison, postgres seems quite happy with the same test (SQLFiddle is slow right now but the link seems to work after 20s or so). – Jack Douglas Jan 12 '15 at 16:33
3

From 10.1.14.1 Unicode Character Sets in the MySQL 5.5 Reference Manual (emphasis added):

For supplementary characters in general collations, the weight is the weight for 0xfffd REPLACEMENT CHARACTER. For supplementary characters in UCA collations, their collating weight is 0xfffd. That is, to MySQL, all supplementary characters are equal to each other, and greater than almost all BMP characters.

and:

The current rule that all supplementary characters are equal to each other is nonoptimal but is not expected to cause trouble. These characters are very rare, so it will be very rare that a multi-character string consists entirely of supplementary characters.

So the answer to your question appears to be "no".

finally:

If you really want rows sorted by MySQL's rule and secondarily by code point value, it is easy:

ORDER BY s1 COLLATE utf32_unicode_ci, s1 COLLATE utf32_bin

Though it is not clear to me how this should be applied for comparisons.

The documentation extracts quoted above are unchanged for MySQL 5.7.

  • Thanks for the reference, I hadn't seen that in the docs. – morganwahl Jan 13 '15 at 18:56
  • And the workaround is a good idea for sorting, but my ultimate problem here is mysql using the collation for comparisons at all. I can get why it's a feature, but I wish it was optional. – morganwahl Jan 13 '15 at 19:04
2

could you compare using binary but return the string? Something like

select * from math where symbols = '𝕂' COLLATE utf32_bin

is how it would be done in MSSQL.

  • Thanks for the suggestion, but like I said, I'm using Django to access the database; so the best workaround is to just use the binary collation in the schema, and do all the text-handling in my Python code. – morganwahl Jan 16 '15 at 22:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.