Character Sets and Collations
What a Character Set and Collation Do
A utf8mb4 collation is the set of rules a database uses to compare and sort text that is stored with the utf8mb4 character set. The two settings work together. The character set defines which characters can be stored and how each one is turned into bytes. The collation defines how characters are compared, including whether “A” and “a” count as equal and how accented letters are ordered.
Both settings apply at several levels: the server, the database, the table and individual columns. A column can override its table’s default, which is why two tables in the same MySQL database can behave differently.
Why utf8mb4 Replaced utf8 in MySQL
The older utf8 setting in MySQL is a limited version of UTF-8. It stores at most three bytes per character, which covers most common letters and symbols but not emoji or many rarer characters. The setting that supports the full UTF-8 range is utf8mb4, where “mb4” means up to four bytes per character. MariaDB uses the same naming.
A common problem appears when a table is set to utf8 and a visitor posts an emoji. The insert then fails or the text is altered. Using utf8mb4 for new databases avoids this.
How Collation Changes Search Results
Suppose a customer table has an email column. With a case-insensitive collation, a search for [email protected] also finds [email protected]. With a case-sensitive or binary collation, the two are treated as different values. Neither behaviour is wrong, but the choice changes which records match and therefore what an application shows. Comparing columns that use different collations can also cause errors, such as “illegal mix of collations”.
Frequently asked questions
Why do my special characters show as question marks?
Usually because the character set in use does not match the one the data was saved with. The connection between the application and the database, the table itself and the web page must all agree. A mismatch can turn accented letters into garbled strings such as é or replace characters with question marks. Settings in the database schema define these choices.
Should I use utf8mb4 for every new database?
In most cases, yes. utf8mb4 stores the full range of Unicode characters, including emoji, and is the recommended choice for new work in MySQL and MariaDB. The main costs are slightly more storage for some text and index size limits on very long text columns, which are worth checking in table design reviews.