CODEWITHSUNDEEP
mysqlcollation
eengebruiker
eengebruiker

Reputation: 27

Confused about collation in mysql

In spite of all discussions about this subject, I can not solve the puzzle that I have with collation.

  1. Created a DB with:

    CREATE DATABASE Desk_Database CHARACTER SET = utf8 COLLATE = utf8_unicode_ci;

  2. Created a table called Users and inserted the following data:

    INSERT INTO  Users
    (KlantGroep, TypeUser, Naam, Password)
VALUES ("BNOF", "Manager", "André", "kkk");

INSERT INTO  Users
    (KlantGroep, TypeUser, Naam, Password)
VALUES ("BNOF", "User", "Ingrid", "ppp");

Now when using the following query it gives one row back, but it shouldn't find any row since André is different from Andre:

SELECT klantgroep, typeuser FROM processors 
WHERE naam = 'Andre' AND password = 'kkk';

What is it that I don't understand. I thought that utf8_unicode_ci will not throw away the accent on e, but it apparently does.

Upvotes: 1

Views: 156

Answers (2)

Joni
Joni

Reputation: 111219

This is the expected behaviour: "Andre" and "André" are equivalent according to utf8_unicode_ci. The accent is not thrown away, it's just that e and é are considered the same when comparing two strings for equality.

If you want to consider the two different you have to use some other collation, such as utf8_bin.

Upvotes: 2

davidethell
davidethell

Reputation: 12018

See this discussion here. Based on that discussion it might help to set the collation to utf8_bin but there is also the possibility of a conflict with settings in the underlying OS itself.

HTH

Upvotes: 2

Related Questions