I have a problem with index on a UTF-8 based db/table and mySQL 4.1 (I have tried 5.0 too). I run the mysqld with the --default-character-set=utf8 flag and I'm certain that it's UTF-8 data that I'm putting into the database. To reproduce, follow these simple steps.
#----------------------------------- CREATE DATABASE `database` DEFAULT CHARACTER SET utf8; CREATE TABLE `table` ( `id` VARCHAR( 10 ) NOT NULL , `name` VARCHAR( 10 ) NOT NULL ) CHARACTER SET = utf8; ALTER TABLE `table` ADD INDEX ( `name` ) INSERT INTO `table` ( `id` , `name` ) VALUES ( '1', 'Rene' ); INSERT INTO `table` ( `id` , `name` ) VALUES ( '2', 'René' ); # Now make some queries... SELECT * FROM `table` WHERE `name` = 'Rene'; SELECT * FROM `table` WHERE `name` = 'René'; # These previous two lines work equally, which I reckon is wrong. SELECT * FROM `table` WHERE `name` LIKE 'Rene'; # Seems to work correctly. SELECT * FROM `table` WHERE `name` LIKE 'René'; # This previous line gives no result, and I've understood it is because of how "Rene" and "René" are somehow treated as equal. # Solutions: remove "Rene" and the previous line will work. # Or remove the index. # Or switch to a fulltext index. # Or convert the table to InnoDB. # Of course, all of these options means a huge loss in performance, so they're not to be considered as permanent solutions. # Does anyone know what causes this problem? I reckon this is quite important because data is temporarily "lost". # Is this a known problem, is it to be fixed in a future release? Cheers. Rikard ________________________________________________________________________ http://www.websidorna.com - Gratis e-post, dating, webshop. Tjäna poäng! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
