HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlModerate

Best General Purpose Character Set and Collation for MySQL

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
charactergeneralmysqlforcollationandpurposesetbest

Problem

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.

Solution

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'.

Context

StackExchange Database Administrators Q#292687, answer score: 13

Revisions (0)

No revisions yet.