0

Suppose you've got a mySql database like this:

CREATE TABLE animals (`name` varchar(7));

INSERT INTO animals (`name`) VALUES ('häst'), ('apa'), ('orm'), ('ödla');

Now, if I would like to retrieve all animals with the character 'ä', I would use the query:

select * from animals where name like "%ä%";

Except this returns the animals "häst", "apa" and "ödla". It seems like it searches for the letter "a" instead, probably because it doesn't support UTF-8.

How can you make it support UTF-8?

I have character-set-server set to utf8mb4.

sqlFiddle

Istlemin
  • 131
  • 2
  • 11
  • That's likely not an ideal duplicate in this case - it won't solve the problem, which has to do with the utf8mb4 collation being too liberal with umlauts. I think the only solution is to use utf8_bin – Pekka Apr 23 '15 at 19:34
  • 2
    Better duplicate: [MySQL treats ÅÄÖ as AAO?](http://stackoverflow.com/questions/2607130/mysql-treats-%C3%85%C3%84%C3%96-as-aao) – Pekka Apr 23 '15 at 19:34
  • 1
    [There you go](http://sqlfiddle.com/#!9/4079c/2/0) - but note that using a different collation on the fly isn't optimal for performance. It will probably take many many records until you feel the effects though. If you need to be very performance aware, create a separate column with utf8_bin collation for comparisons – Pekka Apr 23 '15 at 19:37
  • @ Pekka 웃:- agreed .. on duplicate – HaveNoDisplayName Apr 23 '15 at 19:37

0 Answers0