MySQL collation for all languages

Question

I'm currently developing a website that is going to show stuff for almost any language in the world. And I'm having problems choosing the best collation to define in the MySQL.

Which one is the best to support all characters? Or the most accurate?

Or is just best to convert all characters to unicode?

Gerbus · Answer 1 · 2019-04-08T20:40:08.773

29

The accepted answer is wrong (maybe it was right in 2009).

utf8mb4_unicode_ci is the best encoding to use for wide language support.

Reasoning and supporting evidence:

You want to use utf8mb4 rather than utf8 because the latter only supports 3 byte characters, and you want to support 4 byte characters. (ref)

and

You want to use unicode rather than general because the latter never sorted correctly. (ref)

edited Apr 08 '19 at 20:40

answered Mar 07 '19 at 16:01

Gerbus

2,406
27
21

2

Thanks! But what is the disadvantage of doing this by default for every db / table? Does it use more space or will it make my queries / searching inefficient compared to using the default mysql setting (latin1 i guess) – supersan Apr 29 '20 at 13:56

stone · Accepted Answer · 2013-06-27T13:07:27.523

23

I generally use 8-bit UCS/Unicode transformation format which works perfect for any (well most) languages

utf8_general_ci

http://dev.mysql.com/doc/refman/5.0/en/charset-unicode.html

edited Jun 27 '13 at 13:07

answered Sep 20 '09 at 12:08

stone

2,183
16
26

6

I'd like to suggest using utf8_unicode_ci instead of utf8_general_ci. For more information about why unicode is better than general @ http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci – Aistis Aug 07 '14 at 07:56

score 0 · Answer 3 · answered Jun 03 '21 at 09:11

0

Use utf8mb4 instead of utf8

utf8mb4_general_ci => support 1, 2, 3 or 4 bytes

and

utf8_general_ci or utf8mb3_general_ci => support 1, 2 or 3 bytes

It will take space on ur disk as required.

answered Jun 03 '21 at 09:11

Deepak Kumar

423
1
4
11

score 0 · Answer 4 · answered Jul 30 '21 at 09:59

0

Use utf8mb4_unicode_ci or utf8mb4_general_ci can be tricky and cause unexpected behaviors.

Be aware.

Perhaps utf8mb4_unicode_bin can be a good option if you want to avoid cases like this one below.

answered Jul 30 '21 at 09:59

FabianoLothor

2,479
4
23
35

MySQL collation for all languages

4 Answers4