What effects does using a binary collation have?
Binary collation compares your string exactly as strcmp() in C would do, if characters are different (be it just case or diacritics difference). The downside of it that the sort order is not natural.
An example of unnatural sort order (as in "binary" is) : A,B,a,b
Natural sort order would be in this case e.g : A,a,B,b (small and capital variations of the same letter are sorted next to each other)
The practical advantage of binary collation is its speed, as string comparison is very simple/fast. In general case, indexes with binary might not produce expected results for sort, however for exact matches they can be useful.
utf8_bin: Compares strings by the binary value of each character in the string.
utf8_general_ci: Compares strings using general language rules and using case-insensitive comparisons.
utf8_general_cs: Compares strings using general language rules and using case-sensitive comparisons.
For example, the following will evaluate at true with either of the UTF8_general collations, but not with the utf8_bin collation:
Ä = A
Ö = O
Ü = U
With the utf8_general_ci collation, they would also return true even if not the same case.
http://www.phpbuilder.com/board/showpost.php?s=2e642ac7dc5fceca2dbca1e2b9c424fd&p=10820221&postcount=2
The other answers explain the differences well.
Binary collation can be useful in some cases :
- column contains hexadecimal data like password hashes
- you are only interested in exact matches, not sorting
- for identifiers with only [a-z0-9_] characters, you can even use it for sorting
- for some reason you store numbers in CHAR() or VARCHAR columns (like telephones)
- zipcodes
- UUIDs
- etc
In all those cases you can save a (little) bit of cpu cycles with a binary collation.
With utf8_general_ci, matches occur without taking case and accentuation into account. It may be a good thing when you need to perform queries on words.
In utf8_bin, the match only occurs when strings are strictly the same. Queries are faster this way.