Non-convertible unicode encoding in MySQL

Question

I have a database in which we store user names with the first capital letter of each name, i.e.IsaacSparling. I am trying to make case-insensitive autocomplete against my MySQL (v5.1.46) db. The table has UTF8 encoding and utf8_unicode_ci mapping. I also did these tests compared to setting utf8_general_ci.

Plain ASCII text works fine:

mysql> select username from users where username like 'j%'; +----------------+ | username | +----------------+ | J******** | | J*********** | | J************* | +----------------+ 3 rows in set (0.00 sec) mysql> select username from users where username like 'J%'; +----------------+ | username | +----------------+ | J******** | | J*********** | | J************* | +----------------+ 3 rows in set (0.00 sec) 

(names changed, but they are).

However, when I try to do the same for Unicode characters outside of ASCII, there is no such luck:

 mysql> select username from users where username like 'ø%'; Empty set (0.00 sec) mysql> select username from users where username like 'Ø%'; +-------------+ | username | +-------------+ | Ø********* | +-------------+ 1 row in set (0.00 sec) 

Some research led me to the following: http://bugs.mysql.com/bug.php?id=19567 (tl; dr, this is a known error with Unicode sorts and the correction is in the priority of the "new function", i.e. will not be completed in any reasonable timeframe).

Has anyone found any efficient workarounds that allow case-insensitive search for Unicode characters in MySQL? Any thoughts appreciated!

Answer 1

Works well for me with version 5.1.42-community

Your mysql client may not have sent Unicode characters properly. I tested with sqlYog and it did a great job with utf8_unicode_ci and utf8_general_ci wallpapers

Answer 2

IF you care about the fact that you can order field values ​​with text without worrying about whether it is in upper or lower case. I think the best thing you can do is turn to the field instead of entering only username , enter LOWER(username) username , and then you can perfectly use the order in this field by calling it by name

Answer 3

Have you tried using CONVERT? Sort of

 WHERE `lastname` LIKE CONVERT( _utf8 'ø%' USING latin1 ) 

may work for you.

Answer 4

I just solved the same problem using request

 show variables like '%char%'; 

My character_set_client was set to 'utf8', but character_set_connection and character_set_results were set to 'latin1'. Thus, UPPER, LOWER, LIKE functions do not work properly.

I just inserted a row

 mysql_query("SET NAMES utf8"); 

immediately after connecting to get a case-insensitive job.