Skip to content
Tutorials Center
My learning

Topics

Lesson 15 of 40 · Data Types and Storage Engines

Character Sets and Collations in MariaDB

Learn how MariaDB stores and compares text: character sets such as utf8mb4, collations like utf8mb4_unicode_ci and utf8mb4_bin, the four levels they are set at, converting tables and fixing garbled text.

Text is more than letters. A computer stores characters as numbers, and a character set is the table that maps characters to those numbers and decides how they are encoded in bytes. A collation is a set of rules for comparing and sorting those characters: is a equal to A, does é sort next to e, does ß equal ss? Mistakes here cause garbled names (Müller), broken searches and surprising unique-key violations.

Most of this lesson is configuration you run on a server, so it is read-only; the sorting and comparison ideas have runnable stand-ins.

Character sets

SQL
SHOW CHARACTER SET;                    -- every set the server knows
SHOW CHARACTER SET LIKE 'utf8%';
Name Bytes per char Notes
utf8mb4 1 to 4 Full UTF-8. Use this.
utf8 / utf8mb3 1 to 3 Old "UTF-8" that cannot store emoji or some CJK characters
latin1 1 Western European; the old MySQL default
ascii 1 English letters only
binary 1 Raw bytes, no character meaning

The rule is simple: use utf8mb4 for everything. Recent MariaDB versions make it the default; older ones default to latin1, which you must override.

Collations

Every character set has several collations. The name tells you the rules:

Suffix Meaning
_ci case insensitive: 'a' = 'A'
_cs case sensitive
_ai / _as accent insensitive / sensitive
_bin compare raw byte values; case and accent sensitive, fastest

Common choices:

  • utf8mb4_general_ci: fast, simple, slightly inaccurate for some languages.
  • utf8mb4_unicode_ci: follows the Unicode standard, more correct, a bit slower.
  • utf8mb4_bin: exact comparison, good for tokens and case-sensitive codes.
  • uca1400_ai_ci and friends (MariaDB 11.3+): modern Unicode 14 rules, accent-insensitive and case-insensitive.
SQL
SHOW COLLATION WHERE Charset = 'utf8mb4' AND Collation LIKE '%unicode%';

SELECT 'a' = 'A' COLLATE utf8mb4_unicode_ci;   -- 1
SELECT 'a' = 'A' COLLATE utf8mb4_bin;          -- 0
SELECT 'e' = 'é' COLLATE utf8mb4_unicode_ci;   -- 1 (accents ignored)
SELECT 'e' = 'é' COLLATE utf8mb4_bin;          -- 0

The effect shows up in unique keys: with a _ci collation, UNIQUE (email) treats [email protected] and [email protected] as the same value, which is usually what you want for emails. Use COLLATE utf8mb4_bin on a column when case really matters, such as API tokens.

Where you can set them

Settings cascade from the broadest to the narrowest:

flowchart TD
  S["Server defaults: character-set-server"] --> D["Database default"]
  D --> T["Table default"]
  T --> C["Column"]
  C --> E["Expression: value COLLATE x"]

A narrower level overrides the broader one.

SQL
CREATE DATABASE blog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE posts (
  id    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  slug  VARCHAR(100) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,   -- column level
  UNIQUE KEY uq_slug (slug)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;                  -- table level

Columns that only ever hold ASCII (slugs, hashes, hex ids) can use ascii to save space and make index keys smaller.

The connection matters too

Three settings describe how your client talks to the server: character_set_client, character_set_connection and character_set_results. If they disagree with the data's real encoding, you get mojibake (garbled text). The shortcut:

SQL
SET NAMES utf8mb4;          -- sets all three
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

In applications, put the charset in the connection string (charset=utf8mb4 in PDO's DSN, for example) and make the page itself UTF-8.

Converting existing tables

ALTER DATABASE only changes the default for new tables. Convert existing data like this:

SQL
ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This re-encodes every text column. It rewrites the table, and can fail if a unique index now sees two values as equal (for instance a and A after switching to _ci) or if an index key becomes too long. Do it on a copy first. Never use ALTER TABLE ... CHARACTER SET without CONVERT TO on a table with data: that only changes the default for new columns and leaves existing text interpreted wrongly.

A typical symptom of double encoding is é where é should be. The repair is subtle (convert to binary, then to the correct charset), so test on a copy and have backups.

Comparison and sorting rules in action

SQLite's COLLATE NOCASE and BINARY behave like _ci and _bin, so you can feel the difference:

SQL Case sensitivity depends on the collation

Press Run (or Ctrl + Enter) to see the result here.

Sorting also depends on the collation. With a binary rule, all uppercase letters sort before lowercase; with a case-insensitive rule they mix as people expect:

SQL Binary versus case-insensitive sort

Press Run (or Ctrl + Enter) to see the result here.

Finally, length can mean characters or bytes. MariaDB has both CHAR_LENGTH() and LENGTH():

SQL
SELECT CHAR_LENGTH('café'), LENGTH('café');   -- 4, 5 in utf8mb4
SQL Characters versus bytes

Press Run (or Ctrl + Enter) to see the result here.

A checklist

  1. Server, database, table and client: all utf8mb4.
  2. Choose one collation family per project (for example utf8mb4_unicode_ci) and use it everywhere, so joins between text columns do not fail with "illegal mix of collations".
  3. Use _bin or an explicit COLLATE only where exact matching is needed.
  4. Keep index key lengths in mind; VARCHAR(190) fits older limits.

Check your understanding

Quick check · 5 questions

of 5 answered You scored / 5 · perfect!

  1. 1. Why is the plain character set name utf8 a poor choice in MariaDB?

    Use utf8mb4 for complete Unicode support.
  2. 2. What does the suffix _ci in a collation name mean?

    With _ci collations 'a' and 'A' compare as equal; _bin compares raw bytes.
  3. 3. What does ALTER DATABASE ... CHARACTER SET utf8mb4 change for existing tables?

    Use ALTER TABLE ... CONVERT TO CHARACTER SET to convert existing tables.
  4. 4. What does SET NAMES utf8mb4 do?

    It makes the three connection settings agree, which prevents garbled text.
  5. 5. Which comparison treats 'e' and 'é' as different?

    Binary collations compare byte values, so accents matter. The accent-insensitive collations treat them as equal.

Summary

  • A character set encodes characters; a collation decides equality and sorting. Use utf8mb4 with one consistent collation.
  • Settings cascade: server, database, table, column, expression.
  • SET NAMES utf8mb4 and the driver's charset keep the connection consistent; CONVERT TO CHARACTER SET fixes existing tables.
  • _ci is case insensitive, _bin exact; collations affect unique keys, joins and sort order.

Next: Generated columns, sequences and system versioning