Skip to content
Tutorials Center
My learning

Topics

Lesson 10 of 75 ยท Data Types

Character Sets and Collations

Understand MySQL character sets and collations: utf8mb4 vs utf8mb3, names like utf8mb4_0900_ai_ci, case and accent sensitivity, per-column settings and index impact.

A character set defines which characters a text column can store and how each one is encoded as bytes. A collation defines how those characters are compared and sorted: is a equal to A, does รฉ sort next to e, which comes first, Z or a? Every text column in MySQL has both, and the pair decides whether a user can register [email protected] after [email protected], whether your emoji survive a save and whether an index is used by a join.

Most of the time the defaults of MySQL 8 do the right thing. This lesson shows what those defaults are, how to read collation names, where settings can be overridden and what goes wrong when two sides of a comparison disagree.

Two settings for every text column

Character set Which characters exist and how they become bytes.
Collation How strings are compared and sorted.

The collation always belongs to one character set.

1 / 2

utf8mb4, utf8mb3 and the name "utf8"

MySQL's original utf8 character set stores at most 3 bytes per character. That covers most scripts but not emoji or some rare characters, which need 4 bytes. The full encoding is called utf8mb4.

  • utf8mb4 is the full UTF-8: 1 to 4 bytes per character. This is the default in MySQL 8.0 and later, and the one you should use.
  • utf8mb3 is the 3-byte subset. In MySQL 8 the name utf8 is an alias for utf8mb3, and it is deprecated. A column declared CHARACTER SET utf8 therefore cannot hold ๐Ÿ˜€.
  • latin1 (single byte, Western European) was the default before 8.0. It still appears in old databases.

On an old server, inserting an emoji into a utf8mb3 column either fails with an "Incorrect string value" error under strict mode, or silently cuts the text at the first unsupported character without strict mode. Migrating such columns to utf8mb4 is a classic upgrade task, and the connection must also use utf8mb4 or the application will still lose characters on the way in. The migration pitfalls reappear in Common MySQL Pitfalls and Anti-Patterns.

You can list what a server supports with read-only commands:

SQL
SHOW CHARACTER SET LIKE 'utf8%';
-- Charset  | Description           | Default collation  | Maxlen
-- utf8mb3  | UTF-8 Unicode         | utf8mb3_general_ci | 3
-- utf8mb4  | UTF-8 Unicode         | utf8mb4_0900_ai_ci | 4

Collation names

A collation name has the form charset_family_flags. For example:

Collation Meaning
utf8mb4_0900_ai_ci Unicode 9.0 algorithm, accent-insensitive, case-insensitive. The default in 8.x.
utf8mb4_0900_as_cs Same algorithm, but accent- and case-sensitive.
utf8mb4_0900_as_ci Accents matter, case does not.
utf8mb4_0900_bin Compares the encoded bytes, so A and a are different and sorting follows code points.
utf8mb4_unicode_ci An older Unicode algorithm (UCA 4.0.0). Very common in existing databases.
utf8mb4_general_ci A fast, simplified legacy collation with less accurate sorting.
utf8mb4_bin Binary comparison with the older PAD SPACE rule.

Two pieces of this table matter in practice.

Sensitivity. With ai_ci, 'a' = 'A' is true and so is 'e' = 'รฉ'. Searches for "cafe" find "Cafรฉ" without any extra code. With as_cs, all of these comparisons are false.

Padding. The 0900 collations are NO PAD: trailing spaces are significant, so 'a' = 'a ' is false. The older collations (unicode_ci, general_ci, bin) are PAD SPACE and ignore trailing spaces. This is the same behavior difference that appeared in the String Types lesson.

You can list collations with SHOW COLLATION WHERE Charset = 'utf8mb4'. A real server returns more than a hundred.

Where character sets and collations are set

Settings cascade from the broadest to the narrowest level, and the narrowest wins:

  1. Server: character_set_server and collation_server give the defaults for new databases.
  2. Database: CREATE DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci.
  3. Table: CREATE TABLE ... DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci.
  4. Column: name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs.
  5. Expression: WHERE name = 'x' COLLATE utf8mb4_bin for one comparison.
SQL
CREATE TABLE users (
  id       BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email    VARCHAR(190) NOT NULL,                          -- inherits the table default
  username VARCHAR(40)  COLLATE utf8mb4_0900_as_cs NOT NULL, -- case-sensitive handles
  UNIQUE KEY uq_email (email),
  UNIQUE KEY uq_username (username)
) DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;

A separate setting controls the connection. The client sends text in the connection character set, which SET NAMES utf8mb4 selects (most drivers do it for you, for example the charset option of a PDO DSN). If the connection says latin1 while your tables are utf8mb4, text is converted on the way and non-Latin characters turn into question marks. Always check three things together: table columns, connection and the application's own encoding.

Case sensitivity in practice

The default collation is case-insensitive, and that affects uniqueness. With utf8mb4_0900_ai_ci, a UNIQUE key on email treats [email protected] and [email protected] as the same value, so the second insert fails. Many applications want exactly that for emails. For usernames, case-sensitive handles or a separate normalized column are alternatives.

SQLite compares text with a BINARY collation by default (case-sensitive, accent-sensitive), and offers NOCASE for ASCII case-insensitive comparisons. It cannot do accent-insensitive comparison out of the box, so this live stand-in contrasts the two behaviors that SQLite can show:

SQL Case sensitivity: BINARY versus NOCASE

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

The first two columns are 0 and 1. The third is 0, because SQLite's NOCASE only folds ASCII letters, while MySQL's utf8mb4_0900_ai_ci would return 1 for the same test. When you test "accent-insensitive" behavior, use a real MySQL server.

Uniqueness and sorting depend on the collation

A column collation also decides which rows violate a UNIQUE index and how ORDER BY arranges them. The next example declares one column with COLLATE NOCASE (a stand-in for a _ci collation) and one with the default BINARY (a stand-in for _bin), then compares sorting and a duplicate check:

SQL Sorting and uniqueness under different collations

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

The first query shows apple banana Banana Cherry for the case-insensitive column and Banana Cherry apple banana for the binary one. The final query returns 3 and 4. Under the case-insensitive collation banana and Banana are the same value, so a UNIQUE index would reject the second one, and they tie in the sort (the query breaks the tie by row id). Under a binary collation they are different values, and every capital letter sorts before every lowercase one.

Collations and indexes

A B-tree index stores values sorted by the column's collation. That has three consequences:

  • Comparing columns with different collations can prevent the optimizer from using the index. Joining a.code (utf8mb4_0900_ai_ci) to b.code (utf8mb4_unicode_ci) forces a conversion, and MySQL may fall back to scanning. In some cases it simply raises Illegal mix of collations. The fix is to give related columns the same collation.
  • Comparing different character sets is worse: joining a utf8mb3 column to a utf8mb4 one converts every row on one side, so the index on that side is useless. This shows up after a half-finished migration.
  • Index length limits. InnoDB indexes can cover at most 3,072 bytes per key with the default DYNAMIC row format. In utf8mb4 that is 768 characters, so VARCHAR(255) fits comfortably while VARCHAR(1000) does not. A composite index shares that limit among its columns.

A utf8mb4_0900_* collation is also faster for sorting than older ones in many workloads, and it handles the modern Unicode repertoire properly, so prefer it for new tables.

A migration checklist

Converting a table from utf8/latin1 to utf8mb4 is a data and schema change that locks or copies the table, so test it on a copy first:

SQL
ALTER TABLE articles CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Before running it, check indexes on long VARCHAR columns (they may now exceed the key limit), foreign keys between tables (all must change together), the connection charset in the application and any stored procedures that declare their own character sets. The online options are covered in ALTER TABLE and Online DDL.

What ran live here

The server settings, SHOW commands and ALTER TABLE above are MySQL features and are shown read-only. The two live examples use SQLite's BINARY and NOCASE collations to show how case sensitivity changes equality, sorting and uniqueness. SQLite has no accent-aware or 0900 collations, and it says so with the 'รฉ' = 'e' check.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which character set should new MySQL tables use for general text?

    utf8mb4 is full UTF-8 (up to 4 bytes) and the default in MySQL 8. In MySQL 8 the name utf8 is a deprecated alias for the 3-byte utf8mb3, which cannot store emoji.
  2. 2. What do the letters in utf8mb4_0900_as_cs tell you?

    as means accent-sensitive and cs means case-sensitive. The _ai_ci variants ignore both accents and case.
  3. 3. With the default collation utf8mb4_0900_ai_ci, what happens when you insert '[email protected]' after '[email protected]' into a UNIQUE column?

    The collation is case-insensitive, so the two values compare as equal and violate the unique index.
  4. 4. Why can joining columns with different collations be slow?

    A comparison needs both sides in one collation. Converting one side per row prevents an index lookup on that side, or raises an Illegal mix of collations error.
  5. 5. What is the practical meaning of the 3,072-byte index key limit in utf8mb4?

    3,072 bytes divided by up to 4 bytes per utf8mb4 character is 768 characters, so very long VARCHAR columns need a prefix index or a different design.

Summary

  • A character set decides which characters can be stored, a collation decides how they compare and sort.
  • Use utf8mb4. In MySQL 8 utf8 means the 3-byte utf8mb3, which loses emoji and is deprecated.
  • utf8mb4_0900_ai_ci is the default: accent-insensitive, case-insensitive and NO PAD. _as_cs and _bin are stricter.
  • Settings cascade server, database, table, column and expression, and the connection character set must match.
  • A case-insensitive collation also makes UNIQUE keys case-insensitive.
  • Mismatched collations or character sets can break index use. Key length is limited to 3,072 bytes, or 768 utf8mb4 characters.

Next: ENUM and SET in Practice.