mysql utf8 character problem

Question

I set all character set as "utf8" in pages, I set all collation (also fields collation) as utf8_general_ci in database, and I add this code in connect.php

mysql_set_charset('utf8',$connect); mysql_query("SET NAMES 'utf8'");

Although everything is utf, when i run this query:

"SELECT * FROM titles WHERE title='toruń'"

Result: it returns "toruń" and "torun" which's are different words.

So what do you think?
What is the problem?

Thanks!

EDIT:

CREATE TABLE IF NOT EXISTS titles
(
id int(11) NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL,
PRIMARY KEY (id),
KEY title (title),

) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=37 ;

can you test it in phpmyadmin or the mysql command line

user557846
– user557846

2011-03-10 19:20:22 +00:00
Commented Mar 10, 2011 at 19:20 — user557846
– user557846, Commented Mar 10, 2011 at 19:20
dump the table schema and add it to the question.

user557846
– user557846

2011-03-10 19:32:40 +00:00
Commented Mar 10, 2011 at 19:32 — user557846
– user557846, Commented Mar 10, 2011 at 19:32

Michael Madsen · Accepted Answer · 2011-03-10 19:33:25Z

The problem is that the collation you have chosen is designed to ignore that particular accent (and, most likely, accents in general).

If you expect to be storing a particular language, rather than a number of different languages, try using utf8_(language)_ci (if that language is not present, there might be another language which is similar to yours). Otherwise, you could try utf8_unicode_ci, which uses the Unicode Collation Algorithm, but I'm not sure if that one makes this distinction.

You can also use utf8_bin, which is guaranteed to consider them different, but that comes at the expense of losing case insensitivity, which is most likely worse.

Having said that, this is not necessarily a bad thing: by ignoring the accents, the search will be more flexible, and easier to use for people who are unable to type a specific character.

i'm working on a polish website, but it's not local. yes maybe in search query it's good, but in inserting that's so bad. because my pseudo code is like "if(!exist_in_table('torun')) then create_row("torun").

krtek · Accepted Answer · 2011-03-10 19:16:51Z

0

try using utf8_encode.

answered Mar 10, 2011 at 19:16

krtek

26.7k5 gold badges60 silver badges84 bronze badges

Comments

user557846 · Accepted Answer · 2011-03-10 19:40:20Z

0

you want utf8_bin, *_ci is case insensitive, so accents are treated as the regular letter

answered Mar 10, 2011 at 19:40

user557846

1 Comment

Okan Kocyigit Over a year ago

when I change the collation of 'title' filed as utf8_polish_ci, have to i change the collation for all tables or just fields.

Collectives™ on Stack Overflow

mysql utf8 character problem

3 Answers 3

1 Comment

Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Related