Master MySQL Character Sets & Collations: When and How to Configure Them

Overview

MySQL stores text using character sets (encoding) and collations (comparison rules). Misconfiguration leads to garbled text.

Key concepts

Character set defines the set of characters and their byte representation (e.g., utf8). Collation defines how characters are compared and sorted (e.g., utf8_general_ci, case‑insensitive).

Viewing supported character sets and collations

List all character sets: SHOW CHARACTER SET; Filter by name:

SHOW CHARACTER SET WHERE Charset='utf8';
SHOW CHARACTER SET LIKE 'utf8%';

List collations for a given set:

SHOW COLLATION WHERE Charset='utf8';
SELECT * FROM information_schema.COLLATIONS WHERE CHARACTER_SET_NAME='utf8';

Server‑level defaults

System variables character_set_server and collation_server define the defaults used when a new database, table or column does not specify its own values. They can be inspected:

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

Set at startup via command line or my.cnf:

mysqld --character-set-server=utf8 --collation-server=utf8_general_ci
# my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

When compiling MySQL, defaults can be baked in:

cmake . -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci

Database‑level settings

Create a database with explicit defaults:

CREATE DATABASE test_schema
  DEFAULT CHARACTER SET utf8
  DEFAULT COLLATE utf8_general_ci;

View current defaults:

Session variables after USE test_schema:

SELECT @@character_set_database, @@collation_database;

Query information_schema.SCHEMATA.

Show creation statement:

SHOW CREATE DATABASE test_schema;

Table‑level settings

Specify defaults in CREATE TABLE or ALTER TABLE:

CREATE TABLE test_table (
  id INT NOT NULL,
  PRIMARY KEY(id)
) DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

Inspect a table’s defaults: SHOW TABLE STATUS FROM test_schema LIKE 'test_table'\G; Query information_schema.TABLES for TABLE_COLLATION.

SHOW CREATE TABLE test_table;

Column‑level settings

For CHAR, VARCHAR, or TEXT columns you can add CHARACTER SET and/or COLLATE clauses:

ALTER TABLE test_table ADD COLUMN name VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci;

Check a column’s settings:

SELECT CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='test_schema'
  AND TABLE_NAME='test_table'
  AND COLUMN_NAME='name';

If only a character set is given, the column inherits the default collation of that set; if only a collation is given, the associated character set is used; otherwise the table’s defaults apply.

When to set character set and collation

Three typical points:

During database creation – the most flexible, propagates to tables and columns that do not override.

When starting the MySQL server – set character-set-server and collation-server in the configuration file or command line; all objects inherit these defaults.

At compile time – use -DDEFAULT_CHARSET and -DDEFAULT_COLLATION to embed defaults in the binary.

Reference

MySQL Character Set and Collation documentation: https://dev.mysql.com/doc/refman/5.7/en/charset.html