Polish and German accented letters in mysql

Question

I want to store in unique column polish and german signs. When i alter database:

alter database osa character set utf8 collate utf8_general_ci;

I have a problem with german signs.

sql> insert into company(uuid, name) VALUE ("1","IDE")
[2016-11-27 10:37:35] 1 row affected in 13ms

sql> insert into company(uuid, name) VALUE ("2","IDĘ")
[2016-11-27 10:37:37] 1 row affected in 9ms

sql> insert into company(uuid, name) VALUE ("3","Schuring")
[2016-11-27 10:37:38] 1 row affected in 13ms

sql> insert into company(uuid, name) VALUE ("4","Schüring")
[2016-11-27 10:37:39] [23000][1062] Duplicate entry 'Schüring' for key 'UK_niu8sfil2gxywcru9ah3r4ec5'

Which collate I have to use?

Edit:

Also not works for utf8_unicode_ci

score 2 · Answer 1 · answered Nov 28 '16 at 02:15

The _ci in the COLLATION indicates "character insensitive". Unfortunately, it also means "accent insensitive". So to get E and Ę to be treated differently, you need a _bin collation -- either utf8_bin or utf8mb4_bin.

mb4 is needed for Emoji and Chinese, plus some obscure things.

score 1 · Answer 2 · edited May 23 '17 at 12:13

1

Replace all occurrences of utf8_general_ci with utf8_unicode_ci instead. utf8_general_ci is broken, apparently: What are the diffrences between utf8_general_ci and utf8_unicode_ci?

utf8_general_ci is a very simple — and on Unicode, very broken — collation, one that gives incorrect results on general Unicode text.

edited May 23 '17 at 12:13

Community

1
1

answered Nov 27 '16 at 09:48

Dai

141,631
28
261
374

1

Technically speaking, ut8_unicode_ci is broken, fixed by utf8_unicode_520_ci. With 8.0, there is an even newer standard: utf8mb4_unicode_900_ci. – Rick James Nov 28 '16 at 02:11

score 0 · Answer 3 · answered Nov 27 '16 at 12:05

0

Maybe you should try utf8mb4_unicode_ci ?

Utf8 charset cannot store all utf8 characters.

https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

answered Nov 27 '16 at 12:05

Maciek Bryński

163
1
1
6

sql> insert into company(name) VALUE ("IDE") [2016-11-27 22:45:22] 1 row affected in 13ms sql> insert into company(name) VALUE ("IDĘ") [2016-11-27 22:45:24] [23000][1062] Duplicate entry 'IDĘ' for key 'UK_name' – Piotr Sobolewski Nov 27 '16 at 21:47
All European characters are covered in both character sets. – Rick James Nov 28 '16 at 02:10

score 0 · Accepted Answer · answered Nov 27 '16 at 21:58

0

alter database osa character set utf8mb4 COLLATE utf8mb4_bin;

Works for me. @Maciek Bryński thank you for your hint.

answered Nov 27 '16 at 21:58

Piotr Sobolewski

2,024
4
28
42

The `bin` did it, not the `mb4`. – Rick James Nov 28 '16 at 02:09
@RickJames exactly. I know. – Piotr Sobolewski Nov 29 '16 at 16:54
Just clarifying it for others. – Rick James Nov 29 '16 at 18:17

Polish and German accented letters in mysql

4 Answers4