After doing much research I am at a loss how really utf8_bin and utf8_general_ci differ, besides the obvious -- sort order.

What I inferred from MySQL docs and some online articles is the following.

utf8_bin is pretty much what it suggests, utf8 strings are stored as bytes and MySQL server compares/sorts these on single byte basis, where the value of each byte determines sort order. Make sense to me as utf8 is multi-byte encoding anyway.

So what is utf8_general_ci collation all about, since it is DEFAULT collation for utf8 character set. But here is the kicker, it does not implement all utf8 characters, especially for languages like jp, ko, arabic, and other right to left languages.

I know for fact ucs2_general_uca (default collation for ucs2 Unicode encoding) has only partial Unicode support, so my guess is, the same may be true for utf8_general_ci collation.

The only way I can get full Unicode support is to use utf8_bin collation as utf8_general_ci messes some characters? (chars ?? are returned from the db)

My question is, has anyone else run into similar issues with collations?

Especially, how can utf8_general_ci not support all characters but be the default collation for utf8?

The only way to get things to work is to set charset to utf8 for everything, and utf8_bin collation for everything as well. (connection, db, tables)

I had to modify the mysql connector 1.0.4 to include:

SET collation_connection = @@collation_database;

# database collation is utf8_bin

Cheers,

-Daniel.