MariaDB Supported Collations Utf8mb3_uca1400_ai_ci

Published
Length
3 min read

Luis Felipe Dias

All posts

You set up mariadb supported collations utf8mb3_uca1400_ai_ci at the server, database, or table level. This rule tells the database how to compare and sort three-byte UTF-8 text. Database teams often grant broad write roles so developers can change table collations. Broad roles let anyone alter schemas or read live customer rows by mistake. How mariadb supported collations utf8mb3_uca1400_ai_ci works A collation is a set of rules for comparing characters in a character set. The utf8mb3 prefix

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 supported collations utf8mb3_uca1400_ai_ci at the server, database, or table level. This rule tells the database how to compare and sort three-byte UTF-8 text. Database teams often grant broad write roles so developers can change table collations. Broad roles let anyone alter schemas or read live customer rows by mistake.

How mariadb supported collations utf8mb3_uca1400_ai_ci works

A collation is a set of rules for comparing characters in a character set. The utf8mb3 prefix means each character uses up to three bytes of storage. This covers the Basic Multilingual Plane, which holds most common writing systems. It does not store four-byte characters like emojis or rare historic scripts.

The uca1400 part refers to the Unicode Collation Algorithm version made for sorting. It gives modern, accurate sorting rules across many languages at the same time. The ai tag means accent insensitive, and ci means case insensitive. With these tags, the engine treats uppercase, lowercase, and accented letters as equal.

For example, a search for a plain letter also matches its accented version. This helps users find names and words even when they skip accents. You can apply this rule to a whole server, one schema, or one column. If you skip a table rule, the table uses the schema rule.

When teams change collations on existing tables, the engine must rebuild text indexes. That rebuild locks rows or slows down reads on large production tables. Admins usually need alter permissions on the schema to run these updates.

What native collation settings solve and leave open

Native collation rules solve text sorting and search matching across different languages. They make sure indexes group matching letters together without extra app code. Database roles also let you pick which user accounts can log in. You can grant one role read rights and another role schema change rights.

Still, native database roles leave a big gap during daily operations. A role decides who logs in, not what individual queries do in flight. Once an engineer logs in with an alter role, every table is open. They can run a slow table rebuild during peak traffic hours by mistake.

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

Continue reading? Get the full guide.

Free. No spam. Unsubscribe anytime.

They can also query tables that hold raw emails, phone numbers, or card numbers. Native collation and role settings do not mask sensitive fields in query results. They also do not require peer approval before someone runs a heavy schema change. Teams need a safer way to run queries and schema updates in production.

How to manage collation changes safely in production

Start by checking which tables still use older three-byte character sets in your schema. Many teams move from three-byte sets to four-byte sets during a collation update. If your app must keep three-byte storage, test the new sort order first. Run your test queries on a staging copy with real data volume.

Look for unique index conflicts before you touch your live database tables. Accent-insensitive rules treat accented and plain words as duplicates in unique columns. If two rows only differ by an accent, the index rebuild will fail. Fix those duplicate rows in a small batch before you change the column rule.

Next, lock down who can run schema changes on the live database. Do not give developers standing admin passwords for production servers. Instead, route every human query and schema update through a central control point. Require a quick peer review before any alter statement runs on a big table. Also apply data masking so queries rewrite emails and card numbers in results.

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 parts of utf8mb3_uca1400_ai_ci mean?

The name splits into four clear parts that define storage and sorting. It uses three-byte UTF-8 storage and the Unicode Collation Algorithm 14.0.0 rules. The ai and ci tags mean comparisons ignore both accents and letter case.

Should I use utf8mb3 or utf8mb4 for new tables?

Most teams pick four-byte UTF-8 for new tables so they can store emojis. You only use three-byte UTF-8 when legacy apps or storage limits require it. Both character sets support the exact same Unicode sorting rules in modern releases.

Why did my unique index fail after changing to this collation?

This collation ignores accents and letter case when it checks for duplicate values. Two strings that looked different under a binary rule now look identical. You must clean up those matching rows before you add a unique index.

The query runs. The emails stay hidden.