Databases

Text Encoding and Unicode

Course 1 · Chapter 8 · Databases

A database is another boundary where text meets bytes, and it adds questions of its own. How is the text stored? What does "length" count? Is "É" equal to "é"? In what order do names sort? Is "Zoë" a duplicate of "Zoë"? Each database answers these through settings called the character set and the collation, and a wrong answer silently corrupts data or loses search results. This chapter uses SQLite (which ships with Python, so every example can be run) to show the behaviours, and the MySQL documentation to explain a famous trap.

What was run, and what was not

Every program in this chapter was run on Python 3.14 with its bundled SQLite, and the output shown is the real output. No other database server was run: statements about MySQL come from its documentation, and statements about other products are marked "(check)". The SQLite and MySQL facts come from their official documentation pages, each read through a summarising tool (see "Not Yet Verified" at the end). Details not checked against a source are marked "(check)".

How Text Is Stored and Counted

SQLite's documentation says a text value is "stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE)". Its length() function returns, for a string, "the number of Unicode codepoints (not bytes)" (up to the first NUL character), and for a blob the number of bytes. Casting to a blob therefore gives the byte count, and length() of the text matches Python's len():

import sqlite3

con = sqlite3.connect(":memory:")
samples = ["e", "\u00e9", "e\u0301", "\u20ac", "\U0001F600", "\U0001F468\u200D\U0001F469\u200D\U0001F467"]
for s in samples:
    n_chars, n_bytes = con.execute("SELECT length(?), length(CAST(? AS BLOB))", (s, s)).fetchone()
    print(f"{ascii(s):48} sql length={n_chars}  bytes={n_bytes}  python len={len(s)}")

'e' sql length=1 bytes=1 python len=1 '\xe9' sql length=1 bytes=2 python len=1 'e\u0301' sql length=2 bytes=3 python len=2 '\u20ac' sql length=1 bytes=3 python len=1 '\U0001f600' sql length=1 bytes=4 python len=1 '\U0001f468\u200d\U0001f469\u200d\U0001f467' sql length=5 bytes=18 python len=5

The database's "length" is the code point count from Text Encoding and Unicode 1: the decomposed é is 2, the family emoji is 5, and neither matches what a reader sees (Text Encoding and Unicode 6). Column definitions such as "up to 255 characters" in other products usually count code points too, but storage and index limits are often in bytes (check for your product). The difference matters when a character can take up to four bytes:

def fits_utf8mb3(s):
    return all(ord(c) <= 0xFFFF for c in s)

for s in ["caf\u00e9", "\u20ac100", "\u8a9e", "\U0001F600", "\U00010437"]:
    print(f"{ascii(s):16} fits 3-byte utf8: {fits_utf8mb3(s)!s:5} max bytes per char: {max(len(c.encode('utf-8')) for c in s)}")

name = "\U0001F600" * 255
print(len(name), len(name.encode("utf-8")), len(("a" * 255).encode("utf-8")))

'caf\xe9' fits 3-byte utf8: True max bytes per char: 2 '\u20ac100' fits 3-byte utf8: True max bytes per char: 3 '\u8a9e' fits 3-byte utf8: True max bytes per char: 3 '\U0001f600' fits 3-byte utf8: False max bytes per char: 4 '\U00010437' fits 3-byte utf8: False max bytes per char: 4 255 1020 255

The last line is the arithmetic of a 255-character name: 255 bytes if all ASCII, 1,020 bytes if every character is outside the BMP. Space planning must allow for the worst case (check how your product measures index key length).

The "utf8" Trap in MySQL

The MySQL documentation describes two different Unicode character sets built on UTF-8. utf8mb4 "requires a maximum of four bytes per multibyte character" and supports the BMP and supplementary characters. utf8mb3 "supports only BMP characters and uses a maximum of three bytes per character"; for a supplementary character, "utf8mb4 requires four bytes to store it, whereas utf8mb3 cannot store the character at all." The function above is exactly that test: an emoji or a rare script fails in a three-byte set. MySQL's name utf8 is documented as a deprecated alias for utf8mb3, and the documentation says utf8mb3 is deprecated, to be removed in a future major release, and that utf8mb4 is recommended for all new applications. So a column declared as plain "utf8" in a MySQL database may not be UTF-8 in the full sense. Whether it errors, truncates or substitutes when you insert an emoji depends on the server's strict-mode settings (check; I have not run MySQL here). Moving from utf8mb3 to utf8mb4 is described as safe for existing data, since the old columns contain no supplementary characters.

Case, Equality and the ASCII-Only Default

SQLite is blunt about case. Its documentation says lower() "works for ASCII characters only" by default, that LIKE is case-insensitive only for ASCII ('a' LIKE 'A' is true, 'æ' LIKE 'Æ' is false), and that the NOCASE collation folds only the 26 ASCII capitals. Non-ASCII case handling needs the ICU extension. These behaviours, confirmed here:

import sqlite3

con = sqlite3.connect(":memory:")
q = lambda sql: con.execute(sql).fetchone()[0]
print(q("SELECT 'a' LIKE 'A'"), q("SELECT '\u00e9' LIKE '\u00c9'"))
print(q("SELECT lower('\u00c9COLE ABC')") == "\u00c9cole abc")
print(q("SELECT '\u00e9' = '\u00c9' COLLATE NOCASE"), q("SELECT 'e' = 'E' COLLATE NOCASE"))
print(ascii("\u00c9COLE ABC".casefold()))

1 0 True 0 1 '\xe9cole abc'

Reading it: ASCII letters compare case-insensitively (1) but é and É do not (0); lower() lowered the ASCII letters and left the É upper case (the True line asserts that); the same ASCII-only rule applies to NOCASE; Python's casefold(), by contrast, handles all of it. Other products ship full Unicode collations (check which and how they are named). The general lesson is to find out, for your database, what its case-insensitive comparison really covers.

Equal to the Eye, Different to the Database

A database compares what it is given. The two spellings of "Zoë" from Text Encoding and Unicode 5 are different values, so a uniqueness constraint accepts both, and a lookup by one does not find the other, unless the application normalizes on the way in:

import sqlite3
import unicodedata as ud

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE users (name TEXT UNIQUE)")
composed, decomposed = "Zo\u00eb", "Zoe\u0308"
con.execute("INSERT INTO users VALUES (?)", (composed,))
con.execute("INSERT INTO users VALUES (?)", (decomposed,))
print(con.execute("SELECT count(*) FROM users").fetchone()[0])
print(con.execute("SELECT count(*) FROM users WHERE name = ?", (composed,)).fetchone()[0])

con.execute("DELETE FROM users")
def add(name):
    try:
        con.execute("INSERT INTO users VALUES (?)", (ud.normalize("NFC", name),))
        return "added"
    except sqlite3.IntegrityError:
        return "duplicate"
print(add(composed), add(decomposed))

2 1 added duplicate

The table holds two "Zoë" rows, and a search for one finds only one. With NFC applied at the door the second insert is correctly rejected. Normalize where text enters, not only where it is compared.

Sorting: Binary Is Not Alphabetical

SQLite's default collation, BINARY, "compares string data using memcmp(), regardless of text encoding": it orders by bytes, not by what a dictionary does. That puts every capital before every lowercase letter and every accented letter after "z". You can register your own collation, here a rough one based on the folding from Text Encoding and Unicode 5:

import sqlite3
import unicodedata as ud

words = ["zebra", "\u00c9clair", "apple", "\u00e9cole", "Zoo", "Eagle"]
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE w (word TEXT)")
con.executemany("INSERT INTO w VALUES (?)", [(w,) for w in words])
print([r[0] for r in con.execute("SELECT word FROM w ORDER BY word")])

def fold_key(s):
    return ud.normalize("NFD", s).casefold()

con.create_collation("folded", lambda a, b: (fold_key(a) > fold_key(b)) - (fold_key(a) < fold_key(b)))
print([r[0] for r in con.execute("SELECT word FROM w ORDER BY word COLLATE folded")])

['Eagle', 'Zoo', 'apple', 'zebra', 'Éclair', 'école'] ['apple', 'Eagle', 'Éclair', 'école', 'zebra', 'Zoo']

The first line is the binary order; the second sorts "Éclair" and "école" with the E words. Even so, this key is crude: real languages differ (some sort accented letters as separate letters, and the right order for "ä" differs between languages, check). Serious applications use the database's own locale-aware collation or the ICU library rather than a hand-made key, and the collation also decides what = and UNIQUE mean.

Searching: Folded Shadow Columns

When you need "ecole" to find "École", a common pattern is to store a second, folded copy of the text for searching and match against that. This reuses the accent-stripping function from Text Encoding and Unicode 5:

import sqlite3
import unicodedata as ud

def fold(s):
    d = ud.normalize("NFD", s)
    return "".join(c for c in d if not ud.combining(c)).casefold()

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE t (title TEXT, search TEXT)")
titles = ["\u00c9cole de Paris", "Ecole du soir", "Cr\u00e8me br\u00fbl\u00e9e", "Creme fraiche", "Zo\u00eb", "stra\u00dfe"]
con.executemany("INSERT INTO t VALUES (?, ?)", [(x, fold(x)) for x in titles])

def plain(term):
    return [r[0] for r in con.execute("SELECT title FROM t WHERE title LIKE ?", (f"%{term}%",))]

def folded(term):
    return [r[0] for r in con.execute("SELECT title FROM t WHERE search LIKE ?", (f"%{fold(term)}%",))]

for term in ["ecole", "\u00e9cole", "creme", "ZOE", "strasse"]:
    print(f"{term:8} plain={plain(term)}  folded={folded(term)}")

ecole plain=['Ecole du soir'] folded=['École de Paris', 'Ecole du soir'] école plain=[] folded=['École de Paris', 'Ecole du soir'] creme plain=['Creme fraiche'] folded=['Crème brûlée', 'Creme fraiche'] ZOE plain=[] folded=['Zoë'] strasse plain=[] folded=['straße']

The plain search misses most of what a user would expect: "école" finds nothing because the stored capital É is not folded to é, and "ZOE" misses "Zoë" because LIKE ignores case only for ASCII. The folded column finds them all, including "strasse" for "straße" (casefold expands ß to "ss"). The price: a second column to maintain, and you must apply the same fold to the search term. Whether folding accents is right depends on the language and the audience: in some languages an accent changes the word.

Common mistake

Treating the database as the place where Unicode "just works". It stores whatever bytes it is given in the encoding its settings dictate; it does not normalize, and its default case and sort rules may be ASCII-only. Check three settings for any database you use: the storage or column character set, the connection character set, and the collation. A mismatch in the connection setting is a classic cause of mojibake (see Text Encoding and Unicode 7).

Exercises

Exercise 1

For five strings (ASCII, accented, euros, emoji, CJK), use SQL to get the character count and the byte count, and test each against a limit of 5 characters and a limit of 5 bytes. Which strings pass one limit but fail the other, and why does it matter when a product measures a column in bytes?

📄 View solution

Exercise 2

Make two tables: one with a UNIQUE name column using COLLATE NOCASE, one with a separate key column filled by NFKC plus casefold(). Insert "Zoe", "ZOE", "Zoë", "ZOË", the decomposed Zoë and a fullwidth-letter Zoë into both. Which inserts does each table reject, and which duplicates does only the key column catch?

📄 View solution

Exercise 3

Build the folded search column from this chapter for six titles (including accented, German sharp-s and capital-accent cases). Compare a plain LIKE search with a folded search for five terms, and explain each place where the plain search fails.

📄 View solution

Chapter 8 Quick Reference

SQLite stores text as UTF-8 or UTF-16; length() counts code points for text and bytes for blobs; cast to a blob for the byte count
MySQL: utf8mb4 is up to 4 bytes and covers everything; utf8mb3 (the deprecated "utf8" alias) is up to 3 bytes and cannot store characters above U+FFFF
SQLite default lower(), LIKE and NOCASE fold ASCII only; the ICU extension adds full Unicode case folding
The default BINARY collation compares bytes: capitals before lowercase, accented letters after z
Databases do not normalize: normalize (NFC) on input, or the same text can be stored twice
Use a folded shadow column (or a proper collation) for accent- and case-insensitive search; apply the same folding to the query
Check character set, connection character set and collation for every database you use
Limits may be in bytes even when lengths are in characters: plan for 4 bytes per character

Not Yet Verified

Checked, through a summarising fetch tool and not by reading line by line, against the SQLite documentation pages on datatypes and collation, expressions (LIKE) and core functions (length, lower, instr, substr), and the MySQL reference manual pages on utf8mb4 and utf8mb3: the storage encodings, length semantics, ASCII-only case behaviour, the BINARY and NOCASE collations, the byte limits of the two MySQL sets, and the deprecation statements. The MySQL page that describes the "utf8" alias itself was not read, so the alias is stated only as the navigation titles of the pages I read show it. What MySQL does on inserting an unsupported character, how other products (and their index limits) handle collation and lengths, language-specific sort rules, and the accuracy of the crude folded sort key are from general knowledge or this one run (check). No MySQL, PostgreSQL or other server was run. Sources: sqlite.org/datatype3.html, sqlite.org/lang_expr.html, sqlite.org/lang_corefunc.html, dev.mysql.com/doc/refman/8.4 (charset-unicode-utf8mb4 and charset-unicode-utf8mb3).

Coming next

Text Encoding and Unicode 9 turns to security: look-alike characters, invisible and direction-changing characters, and why validation must happen after normalization.