utf8_unicode_ci with multilingual content column

[Mike Jorgenstam]

I am probably going to fall over from sleep deprivation very soon.

Yes, with or without SET NAMES 'utf8'; ...same results...

Table is created using utf8_unicode_ci

Data enters fine, but the selection process and searching is something from a bad hitchcook movie.


SELECT * FROM mytable ORDER BY CONVERT (myfield USING latin1)

Result:
Yippie, it works... but hey... where did all my Japanese entries go?


SELECT * FROM mytable ORDER BY CONVERT (myfield USING latin1) WHERE myfield LIKE 'Håbo';


Great works fine... except no sorting

SELECT * FROM mytable ORDER BY myfield WHERE myfield LIKE 'Håbo';

Hmmmm not so great, why is there Habo, Häbo and Håbo in the same result.
a is not an å nor an ä. Anyone with a nibble of a braincell can see that.

At least my Japanese returned.


So the question is...
-----------------------------------------------------------------------------
How do I correctly return accurate search results when allowing more than
one language base in the same utf8 column??? Is it even possible?



Edited 1 time(s). Last edit at 04/02/2010 09:18AM by Mike Jorgenstam.