MySQL comparing UTF-8 strings

Question

I have created a table AddictionText.

CREATE TABLE `AddictionText` (
`Condition` VARCHAR(255) NOT NULL);

It's filled with UTF-8 characters, such as

INSERT INTO AddictionText VALUES ('šarec')

When I compare it with the word sarec I still get a single hit. I was expecting no results.

select * from AddictionText where AddictionText.Condition='sarec'  --mind you, first character is S not Š

Can someone please help me out, what am I doing wrong?

Try with `CREATE TABLE AddictionText (...) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci`. — sp00m, Jan 20 '21 at 17:35
@sp00m that's the stuff, thank you so much! I'd accept your answer as the correct one (if you post it I'll do it) but I can only upvote it. — asdfgsdfv, Jan 20 '21 at 17:41
Evert's answer points you to it, I just proposed a collation from the ones available ;) See also https://mysqlserverteam.com/new-collations-in-mysql-8-0-0/ if you're using MySQL 8 (e.g. `utf8mb4_0900_ai_ci` might suit better). Other good reads: https://stackoverflow.com/a/766996/1225328 (plus its comments) on `unicode` vs `general` and `utf8` vs `utf8mb4`, and https://www.monolune.com/what-is-the-utf8mb4_0900_ai_ci-collation/ on `ci` vs `cs` and `ai` vs `as`. — sp00m, Jan 20 '21 at 17:46

score 2 · Accepted Answer · answered Jan 20 '21 at 17:33

2

What is 'equal' in strings depends on language and culture, and this is true for MySQL as well.

I don't know what language šarec is, but to me searching for 'Sarec' and getting a result makes sense.

If that's not the case for you, you want to look for a 'collation' that better matches your expectations.

MySQL documentation has more information:

answered Jan 20 '21 at 17:33

Evert

The desired result would be no results at all, the first characters are not the same (s and š). I'll take a look at collation, ty. – asdfgsdfv Jan 20 '21 at 17:35

1 Answers1