is possible to have accent sensitive and case insensitive utf8 collation in mysql?

Question

How can I perform accent-sensitive but case-insensitive utf8 search in mysql? Utf8_bin is case sensitive, and utf8_general_ci is accent insensitive.

Answer 1

If you want to differ "café" from "cafe" You may use :

Select word from table_words WHERE Hex(word) LIKE Hex("café");

This way it will return 'café'.

Otherwise if you use :

Select word from table_words WHERE Hex(word) LIKE Hex("cafe");

it will return café. I'm using the latin1_german2_ci Collation.

Answer 2

There doesn't seem to be one because case sensitivity is tough to do in Unicode.

There is a utf8_general_cs collation but it seems to be experimental, and according to this bug report, doesn't do what it's expected to when using LIKE.

If your data consists of western umlauts only (ie. umlauts that are included in ISO-8859-1), you might be able to collate your search operation to latin1_german2_ci or create a separate search column with it (that specific collation is accent sensitive according to this page; latin1_general_ci might be as well, I don't know and can't test right now).