How to Normalize Unicode Strings in MariaDB

Why normalize Unicode text in MariaDB?

Normalization guarantees that visually identical characters have the same byte sequence, preventing duplicate keys and failed joins.

Which functions perform normalization?

MariaDB offers four scalar functions: NFC(), NFD(), NFKC(), and NFKD(). Each converts a string to the corresponding Unicode Normalization Form.

What is the basic syntax?

Call the function with the target column or literal string. The return value is VARCHAR in the same character set.

How to normalize and store customer names?

Update text columns so future comparisons are reliable:

UPDATE Customers
SET name = NFC(name)
WHERE id > 0;

Query performance tip

Create a generated column that stores the normalized value and index it for fast lookups.

Can I normalize while inserting?

Yes—wrap the value inside the normalization function in INSERT or LOAD DATA statements.

Best practice: validate before updating

Run SELECT id, name FROM Customers WHERE name <> NFC(name); to preview rows that will change.

How to check if data is already normalized?

Compare the original text to its normalized version; a difference means it was not normalized:

SELECT name FROM Customers WHERE name != NFC(name);

Unicode normalization in joins

Use normalization on both sides of a join to avoid mismatches caused by accent composition differences.

Common mistakes

Skipping collation. Always use a Unicode collation (e.g., utf8mb4_unicode_ci) so that comparison rules match normalized data.

Updating without transaction. Wrap bulk normalization in a single transaction to avoid partial updates.