Character Sets and Collations

MySQL 8.0 defaults: character set utf8mb4, collation utf8mb4_0900_ai_ci (Unicode 9.0 rules, accent-insensitive, case-insensitive).

Concept

A character set defines which characters can be stored and how they are encoded as bytes (e.g., utf8mb4, latin1).

A collation defines how strings are compared and sorted (case sensitivity, accent sensitivity, language rules).

MySQL 8.0 defaults: character set utf8mb4, collation utf8mb4_0900_ai_ci (Unicode 9.0 rules, accent-insensitive, case-insensitive).

Character set and collation can be set at server, database, table, column, and even expression level.

Why It Matters

Wrong character sets cause "mojibake" (garbled text like é), failed inserts of emoji or some Asian characters, and broken joins.

Collations decide whether 'ravi' = 'RAVI', whether 'Jose' = 'José', and how names sort — which affects UNIQUE constraints and search results.

Syntax

SHOW CHARACTER SET LIKE 'utf8%';
SHOW COLLATION WHERE Charset = 'utf8mb4' AND Collation LIKE '%0900%';
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

CREATE DATABASE db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
CREATE TABLE t (name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_as_cs);
SELECT ... WHERE name COLLATE utf8mb4_bin = 'Ravi';

Example

6.1 utf8 vs utf8mb4 Bytes per character Can store emoji / all Unicode?

utf8mb3 (old alias utf8) No (only the Basic Multilingual Plane) Western European only 6.2 Collation suffixes

Binary: compares byte values (case- and accent-sensitive) Unicode Collation Algorithm 9.0.0 (MySQL 8) language (e.g., _de_pb_, _hi_, _ja_) Language-specific sorting rules

With a _ci collation, a UNIQUE index on email treats [email protected] and [email protected] as duplicates (usually desirable).

A case-sensitive search for part numbers or VINs may need _bin or _as_cs.

6.3 Connection character set The client tells the server which encoding it sends (character_set_client), how the server should interpret it (character_set_connection), and how results should be returned (character_set_results).

Most modern connectors default to utf8mb4; in connection strings you can set it explicitly (e.g., ?characterEncoding=UTF-8 in JDBC, charset='utf8mb4' in Python).

6.4 Converting existing tables Test on a copy first: conversion rebuilds the table, may increase row size, and can break indexes that exceed the maximum key length (3072 bytes for InnoDB with DYNAMIC row format).

6.5 Collation mismatches in joins Fix: make related columns use the same character set and collation.

CREATE TABLE cs_demo (txt VARCHAR(20) CHARACTER SET utf8mb3);
INSERT INTO cs_demo VALUES ('Engine OK 👍');
-- ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x91\x8D' for column 'txt'

SELECT 'ravi' = 'RAVI'                                   AS ci_equal,     -- 1 with default collation
       'ravi' = 'RAVI' COLLATE utf8mb4_0900_as_cs        AS cs_equal,     -- 0
       'Jose' = 'José'                                    AS ai_equal,    -- 1 (accent-insensitive)
       'Jose' = 'José' COLLATE utf8mb4_0900_as_ci         AS as_equal;    -- 0

SET NAMES utf8mb4;          -- sets all three for this connection

ALTER DATABASE legacy_db CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;   -- default for NEW tables only
ALTER TABLE legacy_customers CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;   -- converts data (rebuilds table)

-- If orders_legacy.vin uses latin1_swedish_ci and vehicles.vin uses utf8mb4_0900_ai_ci:
SELECT ... FROM vehicles v JOIN orders_legacy o ON o.vin = v.vin;
-- may raise ERROR 1267: Illegal mix of collations, or convert values and skip the index

Expected Result

SELECT @@character_set_server, @@collation_server;

+------------------------+----------------------+
| @@character_set_server | @@collation_server   |
+------------------------+----------------------+
| utf8mb4                | utf8mb4_0900_ai_ci   |
+------------------------+----------------------+

Common Mistakes

Using utf8 (utf8mb3) and failing on emoji or rare characters.

Mismatched collations between joined columns (slow joins or errors).

Assuming comparisons are case-sensitive (they are not with _ci).

Client sending one encoding while the connection declares another (garbled text).

Best Practices

utf8mb4 everywhere: server, databases, tables, connections.

Choose collation deliberately: utf8mb4_0900_ai_ci for general text, _bin/_as_cs for case-sensitive codes.

Keep foreign key and join columns identical in type, charset, and collation.

Interview Relevance

Medium: "utf8 vs utf8mb4?", "What is a collation?", "Why is my comparison case-insensitive?", "How do you fix garbled characters?".

Practice Questions

Show your server's default character set and collation.

Create a table where part_no comparisons are case-sensitive.

Explain why a UNIQUE index rejected '[email protected]' after '[email protected]' was inserted.

Convert a latin1 table to utf8mb4 and list the risks.