patternsqlModerate
Best General Purpose Character Set and Collation for MySQL
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.:
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.
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
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
For some others, such as BASE64, use
Be aware that
The settings on
The settings on
The connection settings are also very important in getting things 'right'.
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.