MariaDB Uca 14 0 Collations Utf8mb3_uca1400_ai_ci

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up mariadb uca 14 0 collations utf8mb3_uca1400_ai_ci at the server, database, table, or column level. This rule sorts three-byte text using the Unicode 14.0 standard while ignoring accents and letter case. Database teams often apply this rule when they build new tables or migrate legacy text columns. Still, teams often grant broad alter privileges to developers so they can fix sorting issues fast. Broad permissions let any user change table structures or read sensitive rows across the wh

Your coding agents run with your access.

Claude Code reaches prod with a developer's credentials, and approval prompts get clicked through. hoop.dev checks every query, masks sensitive data, and holds risky changes for a human.

You set up mariadb uca 14 0 collations utf8mb3_uca1400_ai_ci at the server, database, table, or column level. This rule sorts three-byte text using the Unicode 14.0 standard while ignoring accents and letter case. Database teams often apply this rule when they build new tables or migrate legacy text columns. Still, teams often grant broad alter privileges to developers so they can fix sorting issues fast. Broad permissions let any user change table structures or read sensitive rows across the whole database.

How mariadb uca 14 0 collations utf8mb3_uca1400_ai_ci works in practice

A collation is a set of rules that tells the database how to compare and sort text. The mariadb uca 14 0 collations utf8mb3_uca1400_ai_ci option pairs a three-byte character set with modern sorting rules. The UCA 14.0 part means the database uses the Unicode Collation Algorithm version fourteen for comparisons. The AI part stands for accent insensitive, and CI stands for case insensitive.

When a query sorts or filters text, the database treats accented letters just like plain letters. It also treats uppercase letters and lowercase letters as the exact same character during a search. Because this rule uses the three-byte character set, it only supports characters in the Basic Multilingual Plane. It cannot store four-byte characters like modern emojis or rare historic scripts.

Admins can set this sorting rule as the default when they create a new database schema. If a table does not specify its own rule, it inherits the rule from the database. If a text column does not specify a rule, it inherits the rule from the parent table. When you change the rule on an existing column, the database must rebuild the text indexes.

Planning character set updates and index rebuilds

Changing a column sorting rule in a live database requires careful planning by the engineering team. When you alter a table to use a new sorting rule, the database re-sorts indexed values. On large tables, this index rebuild uses high disk input and output and locks write queries. Teams must check if unique indexes will fail when the new rule ignores accents and case.

For example, two words that differed only by an accent now look identical to the database. If a column has a unique rule, the table change will fail when it finds duplicates. You must run a check query first to find rows that will clash under the new rule. Mixing three-byte and four-byte sorting rules across joined tables also stops the database from using indexes. When joined columns use different rules, the database scans the whole table and slows down apps.

GitLab CI Security + AI Agent Security: architecture patterns and best practices

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

What native database roles solve and what they leave open

MariaDB lets admins control who can change character sets and sorting rules through native role grants. You can give a user the permission to alter tables inside one specific database schema only. This native setup stops unauthorized users from logging in and changing table settings in other schemas. It works well for basic login control and separates app accounts from admin accounts.

Still, native database roles only check who logs in and which broad command types they can run. Once a developer has the alter permission for a schema, they can run it at any time. The native role cannot require a peer review before a large table rebuild runs in production. It also cannot rewrite sensitive fields like emails, phone numbers, or card numbers in query results. A developer who checks for duplicate text rows before a change will see raw user data. Native grants decide who enters the database, not what each statement does in flight.

Add wire guardrails and masking beside MySQL

Database grants and TLS setup decide who can reach MySQL. Even so, an open session can run broad updates or read raw personal data from allowed tables. In practice, hoop.dev runs beside the database as a sidecar for your MySQL. It keeps your login setup and drivers as they are while checking queries on the wire.

With hoop.dev MySQL support, guardrails refuse bad queries before they run and return a readable MySQL error. Data masking rewrites matched sensitive values in result rows in memory. Furthermore, hoop.dev holds SOC 2 Type II and generates evidence for GDPR, HIPAA, and PCI DSS programs. Read the docs and source on GitHub.

Frequently asked questions

What do the ai and ci parts mean in this collation name?

The ai label means accent insensitive, so the database treats accented and plain letters as equal. The ci label means case insensitive, so uppercase and lowercase letters match during searches and sorts.

When should a team choose utf8mb4 instead of utf8mb3?

Teams should choose the four-byte utf8mb4 character set for almost all new apps and tables today. The three-byte utf8mb3 set is mainly for older systems that cannot store emojis or four-byte characters.

Why do joins slow down after changing a column collation?

If two joined columns use different collations, the database must convert text values on the fly. This conversion stops the query engine from using existing indexes and forces a full table scan.

The query runs. The emails stay hidden.