Best General Purpose Character Set and Collation for MySQL

Question

Currently, whenever I create a new MySQL database, I use utf8mb4 as a character set and utf8mb4_unicode_520_ci for the collation, e.g.:

CREATE DATABASE IF NOT EXISTS db_name
    DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Is there a newer/upgraded general purpose collation or character set for MySQL?

For example if there's a collation that superseded utf8mb4_unicode_520_ci, like utf8mb4_unicode_800_ci or something like that?

Thanks for your help.

score 13 · Answer 1 · answered Jun 03 '21 at 00:29

For any version of MySQL or MariaDB, use utf8mb4 with its default COLLATION. (Well, that was not available before 5.5.3.)

0900 refers to Unicode's 9.00.
520 refers to Unicode's 5.20.
No number refers to Unicode's 4.00.
(The higher the number the "better".)

For columns like postal_code, country_code, hex strings, hashes, etc., consider using CHARACTER SET ASCII and COLLATE ascii_general_ci to get case folding.
For some others, such as BASE64, use COLLATE ascii_bin because case-folding would be 'wrong'.

Be aware that
The settings on CREATE DATABASE are defaults for CREATE TABLE, and for stored routines.
The settings on CREATE TABLE are defaults for the columns in that table.
The connection settings are also very important in getting things 'right'.

score 7 · Answer 2 · answered Jun 03 '21 at 17:49

I'll answer my own question in hopes it can help someone in the future. With following query, I was able to figure out what was the latest collation availalble on my server:

SELECT COLLATION_NAME, CHARACTER_SET_NAME
FROM information_schema.COLLATIONS
WHERE
    ( CHARACTER_SET_NAME = 'utf8mb4' ) AND
    ( COLLATION_NAME LIKE '%utf8mb4_unicode%' )
ORDER BY COLLATION_NAME;

Output:

+-------------------------------+-----------------------+
| COLLATION_NAME                | CHARACTER_SET_NAME    |
+-------------------------------+-----------------------+
| utf8mb4_unicode_520_ci        | utf8mb4               |
| utf8mb4_unicode_520_nopad_ci  | utf8mb4               |
| utf8mb4_unicode_ci            | utf8mb4               |
| utf8mb4_unicode_nopad_ci      | utf8mb4               |
+-------------------------------+-----------------------+

With the choices available, I went with:

utf8mb4_unicode_520_ci

I wanted to make sure there wasn't a newer collation available, like utf8mb4_unicode_800_ci. On my system, utf8mb4_unicode_520_ci was the latest.

Best General Purpose Character Set and Collation for MySQL

2 Answers2