Hello,
I've just read through the MySQL documentation about Unicode support,
collations and how it affects sorting and comparison of strings. And I
find it horrible, at least. I feel like I'm back in the MySQL 3.x days
where I used UTF-8 in my application and MySQL treated it binary. The
only problem was incorrect sorting of things. Today we have UTF-8
support in MySQL, which brings correct sorting (for whatever definition
of "correct") but has taken correct comparison again.
When I have three strings, e.g. "Handel", "Händel" and "Hendel", I'd
like to have them sorted correctly. Using the utf8_{general,unicode}_ci
collation seems the only way. Now when I want the row with "Handel" in
it, I'll get two rows back. One of them is not what I wanted. So
strictly, the result is incorrect. The only way to get this right is
using the utf8_bin collation. But this again makes correct sorting
impossible.
It's a nightmare. Why can't I get correct sorting *and* correct (i.e.
precise) comparison in one?
If I cannot even rely on the = operator, what good is a text-storing
database? There even isn't a case-sensitive unicode collation other than
utf8_bin. This means that in every database application that uses
unicode, I cannot separate lower from uppercase when retrieving stuff.
MySQL is simply blind for that. Not to mention different characters that
Unicode, MySQL, DIN, ISO or whoever think are the same, but they aren't.
If they were the same, you wouldn't need both of them.
Finally, my application should really be portable. I haven't looked into
how other DBMS handle it and whether the SQL syntax would be the same,
should there be any method on the language layer to do it right. I only
know that SQLite stores in UTF-8 but otherwise doesn't care about
Unicode, i.e. sorting should be broken, comparison is correct.
PostgreSQL didn't find its own columns again, so I cancelled the test.
--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]