{"id":350,"date":"2026-11-18T17:30:00","date_gmt":"2026-11-18T17:30:00","guid":{"rendered":"https:\/\/backendside.com\/blog\/?p=350"},"modified":"2026-11-21T12:19:15","modified_gmt":"2026-11-21T12:19:15","slug":"mysql-character-sets-collations-utf8mb4","status":"publish","type":"post","link":"https:\/\/backendside.com\/blog\/2026\/11\/18\/mysql-character-sets-collations-utf8mb4\/","title":{"rendered":"Character Sets and Collations: Fixing Garbled Text and Emoji for Good"},"content":{"rendered":"<p class=\"lead\">You save a customer named Jos&eacute; and the screen shows <code>Jos&Atilde;&copy;<\/code>. A user posts a thumbs-up emoji and the database stores <code>????<\/code> &mdash; or rejects the row with <code>Incorrect string value<\/code>. A query that worked yesterday fails with <code>Illegal mix of collations<\/code>. These look like three different bugs, but they all come down to two settings that MySQL and MariaDB apply at several levels at once: the <strong>character set<\/strong> and the <strong>collation<\/strong>. Get them consistent and the problems stop for good.<\/p>\n<h2>Character set vs collation<\/h2>\n<p>A <strong>character set<\/strong> decides how text is turned into bytes &mdash; which characters can be stored at all, and how many bytes each takes. A <strong>collation<\/strong> is a set of rules for <em>comparing and sorting<\/em> text in that character set: whether <code>a<\/code> equals <code>A<\/code>, whether <code>e<\/code> equals <code>&eacute;<\/code>, and where &ouml; sorts. Every collation belongs to exactly one character set.<\/p>\n<h2>The big gotcha: utf8 is not UTF-8<\/h2>\n<p>Real UTF-8 uses one to four bytes per character. Years ago MySQL implemented <code>utf8<\/code> with a maximum of <strong>three<\/strong> bytes, which covers most living scripts but not emoji, many rarer CJK characters or various symbols &mdash; all of which need four. That three-byte version is now officially called <strong><code>utf8mb3<\/code><\/strong>, and <code>utf8<\/code> is a deprecated alias for it. Full UTF-8 is <strong><code>utf8mb4<\/code><\/strong>.<\/p>\n<table>\n<thead>\n<tr>\n<th>Character set<\/th>\n<th>Bytes per character<\/th>\n<th>Emoji?<\/th>\n<th>Use it?<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>latin1<\/code><\/td>\n<td>1<\/td>\n<td>No<\/td>\n<td>Only for legacy Western-European data<\/td>\n<\/tr>\n<tr>\n<td><code>utf8<\/code> \/ <code>utf8mb3<\/code><\/td>\n<td>1&ndash;3<\/td>\n<td>No<\/td>\n<td>No &mdash; deprecated<\/td>\n<\/tr>\n<tr>\n<td><code>utf8mb4<\/code><\/td>\n<td>1&ndash;4<\/td>\n<td>Yes<\/td>\n<td><strong>Yes, for everything new<\/strong><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>MySQL 8 defaults to <code>utf8mb4<\/code> with the <code>utf8mb4_0900_ai_ci<\/code> collation. MariaDB&rsquo;s default depends on version and packaging: recent releases default to <code>utf8mb4<\/code> with the newer <code>uca1400<\/code> collations, many Linux distribution packages have configured <code>utf8mb4<\/code> for years, and older installs may still default to <code>latin1<\/code>. Do not assume &mdash; check:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SHOW VARIABLES LIKE 'character_set%';\nSHOW VARIABLES LIKE 'collation%';\n\n-- Per table\nSELECT table_name, table_collation\nFROM information_schema.tables\nWHERE table_schema = 'shop';\n\n-- Per column\nSELECT column_name, character_set_name, collation_name\nFROM information_schema.columns\nWHERE table_schema = 'shop' AND table_name = 'customers';<\/code><\/pre>\n<h2>Five levels, one rule<\/h2>\n<p>Character set and collation are set at the <strong>server<\/strong>, <strong>database<\/strong>, <strong>table<\/strong> and <strong>column<\/strong> levels, each one defaulting from the level above &mdash; and separately for the <strong>connection<\/strong> between your application and the server. The column is what actually stores the data; the connection decides how the bytes your app sends are interpreted. The rule is simply: <strong>make them all <code>utf8mb4<\/code><\/strong>.<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code># my.cnf \/ my.ini  [mysqld]\ncharacter-set-server = utf8mb4\ncollation-server     = utf8mb4_unicode_ci\n\n-- New databases and tables\nCREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n\n-- The connection: most drivers take a charset option; in raw SQL:\nSET NAMES utf8mb4;<\/code><\/pre>\n<p>The connection setting is the one most often missed. If the client claims to send <code>latin1<\/code> while actually sending UTF-8 bytes, the server dutifully &ldquo;converts&rdquo; them, and you get garbage stored on disk. Set the charset in your driver&rsquo;s connection string (<code>charset=utf8mb4<\/code> or the equivalent) rather than relying on server defaults.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">MyDBDesk &mdash; Check the Settings That Cause Mojibake<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">Diagnosing encoding problems starts with seeing what the server and the table actually use. <strong>MyDBDesk<\/strong>&rsquo;s <strong>Server Status<\/strong> view shows the server&rsquo;s current configuration variables with a filter box &mdash; type <code>character_set<\/code> or <code>collation<\/code> and the relevant settings are all there. A table&rsquo;s <strong>Structure<\/strong> tab shows its full definition, including the character set and collation, and changing a column&rsquo;s type keeps its default, comment and collation.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/mydbdesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore MyDBDesk &rarr;<\/a>\n<\/div>\n<h2>Reading the symptoms<\/h2>\n<table>\n<thead>\n<tr>\n<th>You see<\/th>\n<th>What usually happened<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>????<\/code> in place of emoji or other characters<\/td>\n<td>The column or connection could not represent them (often <code>utf8mb3<\/code> or <code>latin1<\/code>) and they were replaced on the way in &mdash; the original data is lost<\/td>\n<\/tr>\n<tr>\n<td><code>Incorrect string value: 'xF0x9F...'<\/code><\/td>\n<td>Strict mode refused to store a 4-byte character in a 3-byte column &mdash; better than losing it silently<\/td>\n<\/tr>\n<tr>\n<td><code>Jos&Atilde;&copy;<\/code>, <code>&Atilde;&curren;<\/code>, <code>&acirc;&euro;&trade;<\/code><\/td>\n<td><strong>Mojibake<\/strong>: UTF-8 bytes interpreted as <code>latin1<\/code> somewhere along the way<\/td>\n<\/tr>\n<tr>\n<td><code>Illegal mix of collations<\/code><\/td>\n<td>Comparing or joining two columns (or a column and a literal) with incompatible collations<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>To see what is really stored, look at the bytes rather than trusting any screen:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SELECT name, HEX(name), LENGTH(name), CHAR_LENGTH(name)\nFROM customers WHERE id = 42;\n\n-- 'Jos\u00e9' correctly stored in utf8mb4:    4A6F73C3A9          (\u00e9 = C3A9)\n-- double-encoded mojibake:               4A6F73C383C2A9      (\u00e9 = C383C2A9)<\/code><\/pre>\n<h2>Converting a table to utf8mb4<\/h2>\n<p>If the stored bytes are correct and you simply need room for four-byte characters, convert the table. <strong>Take a backup first<\/strong> &mdash; this rewrites the whole table, which takes time and locks writes on large tables.<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>ALTER DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;\n\nALTER TABLE customers\n  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;<\/code><\/pre>\n<p><code>CONVERT TO<\/code> converts every text column&rsquo;s data. Changing only the table default (<code>ALTER TABLE ... CHARACTER SET ...<\/code> without <code>CONVERT TO<\/code>) affects new columns only.<\/p>\n<p>One historical snag: with older row formats, InnoDB index keys were limited to 767 bytes, and a <code>VARCHAR(255)<\/code> at four bytes per character needs 1,020. That is where the old advice to use <code>VARCHAR(191)<\/code> came from. With the default <code>DYNAMIC<\/code> row format in current versions the limit is 3,072 bytes, so <code>VARCHAR(255)<\/code> indexes fine.<\/p>\n<h3>Repairing double-encoded text<\/h3>\n<p>If <code>HEX()<\/code> shows mojibake bytes, the data itself was mis-stored and converting the table will not fix it. The classic repair re-interprets the bytes: turn the text back into the <code>latin1<\/code> bytes it was wrongly decoded from, then read those bytes as UTF-8. Test on a copy first, and only on rows that are actually affected:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SELECT name,\n       CONVERT(CAST(CONVERT(name USING latin1) AS BINARY) USING utf8mb4) AS fixed\nFROM customers\nWHERE name LIKE '%&Atilde;%';<\/code><\/pre>\n<p>When the preview looks right, apply it with an <code>UPDATE<\/code> on the same rows &mdash; and fix the connection setting that caused it, or new rows will arrive broken.<\/p>\n<div style=\"border:1px solid #c5d3f8;background:linear-gradient(135deg,#eef2fd 0%,#ffffff 72%);border-radius:14px;padding:1.5rem 1.65rem;margin:2rem 0;\">\n<div style=\"font-size:.7rem;font-weight:700;letter-spacing:.08em;text-transform:uppercase;color:#2d5be3;margin-bottom:.55rem;\">&#128295; BackendSide Tool<\/div>\n<h4 style=\"margin:0 0 .45rem;font-size:1.15rem;color:#1a1916;font-weight:700;\">MyDBDesk &mdash; CSV Imports That Respect the Encoding<\/h4>\n<p style=\"margin:0 0 1.05rem;color:#3d3c38;font-size:.92rem;line-height:1.65;\">A lot of garbled text arrives through CSV files. <strong>MyDBDesk<\/strong> imports CSV in <strong>UTF-8, UTF-16 or ANSI<\/strong> &mdash; comma, semicolon, tab or pipe separated &mdash; through a column-matching window that shows sample values before anything is written, so mangled characters are visible up front. The import is all-or-nothing, and CSV exports read the same on every PC whatever its language settings.<\/p>\n<p>  <a href=\"https:\/\/backendside.com\/mydbdesk.php\" style=\"display:inline-flex;align-items:center;gap:.4rem;background:#2d5be3;color:#ffffff;font-weight:600;font-size:.85rem;padding:.6rem 1.2rem;border-radius:6px;text-decoration:none;\">Explore MyDBDesk &rarr;<\/a>\n<\/div>\n<h2>Choosing a collation<\/h2>\n<p>Collation names follow a pattern: character set, algorithm, then sensitivity suffixes &mdash; <code>_ci<\/code> case-insensitive, <code>_cs<\/code> case-sensitive, <code>_ai<\/code>\/<code>_as<\/code> accent-insensitive\/sensitive, and <code>_bin<\/code> for plain byte comparison.<\/p>\n<table>\n<thead>\n<tr>\n<th>Collation<\/th>\n<th>Where<\/th>\n<th>Notes<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>utf8mb4_0900_ai_ci<\/code><\/td>\n<td>MySQL 8+ default<\/td>\n<td>Modern Unicode rules; MySQL-only; a NO PAD collation, so trailing spaces count in comparisons<\/td>\n<\/tr>\n<tr>\n<td><code>utf8mb4_uca1400_ai_ci<\/code><\/td>\n<td>MariaDB 10.10+<\/td>\n<td>Modern Unicode rules on MariaDB<\/td>\n<\/tr>\n<tr>\n<td><code>utf8mb4_unicode_ci<\/code><\/td>\n<td>Both<\/td>\n<td>Older Unicode rules, but portable &mdash; a safe choice if data moves between engines<\/td>\n<\/tr>\n<tr>\n<td><code>utf8mb4_general_ci<\/code><\/td>\n<td>Both<\/td>\n<td>Fast and simplified; sorts some languages less correctly<\/td>\n<\/tr>\n<tr>\n<td><code>utf8mb4_bin<\/code><\/td>\n<td>Both<\/td>\n<td>Exact byte comparison: case- and accent-sensitive<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Collation choice is not only about sorting. In a <code>_ci<\/code> collation, <code>'ali' = 'ALI'<\/code> is true, so a <code>UNIQUE<\/code> index on usernames will reject <code>ALI<\/code> if <code>ali<\/code> exists &mdash; usually what you want. For tokens, hashes and codes where case matters, use <code>_bin<\/code> on that column.<\/p>\n<p><strong>Moving dumps between MySQL and MariaDB?<\/strong> A MySQL dump full of <code>utf8mb4_0900_ai_ci<\/code> may fail on older MariaDB versions with <code>Unknown collation<\/code>. Use a collation both understand (such as <code>utf8mb4_unicode_ci<\/code>) for portable schemas, or replace the collation name in the dump before loading.<\/p>\n<p>To clear an <code>Illegal mix of collations<\/code> error, make the two sides match &mdash; ideally by converting the columns to the same collation, or for a one-off query with an explicit <code>COLLATE<\/code>:<\/p>\n<pre style=\"background:#1e1d1b;color:#eceae6;padding:1rem 1.15rem;border-radius:8px;overflow-x:auto;font-size:.85rem;line-height:1.5;\"><code>SELECT *\nFROM customers c\nJOIN legacy_contacts l\n  ON l.email = c.email COLLATE utf8mb4_unicode_ci;<\/code><\/pre>\n<p>Mismatched collations on join columns also hurt performance, because the index on one side may not be usable.<\/p>\n<h2>Key takeaways<\/h2>\n<ul>\n<li>A <strong>character set<\/strong> decides which characters can be stored; a <strong>collation<\/strong> decides how they compare and sort.<\/li>\n<li>MySQL&rsquo;s <code>utf8<\/code> is really <strong><code>utf8mb3<\/code><\/strong> &mdash; no emoji. Use <strong><code>utf8mb4<\/code><\/strong> everywhere.<\/li>\n<li>Set it at <strong>every level<\/strong>: server, database, table, column &mdash; and especially the <strong>connection<\/strong>.<\/li>\n<li>Diagnose with <strong><code>HEX()<\/code><\/strong>: correctly stored data only needs <code>CONVERT TO<\/code>; double-encoded data needs a byte-level repair.<\/li>\n<li>Pick collations deliberately: <code>_ci<\/code> for names and search, <code>_bin<\/code> for codes, and a portable collation if data moves between MySQL and MariaDB.<\/li>\n<li>Always back up before converting, and fix the cause, not just the data.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Question marks instead of emoji, accented letters that turn into strange symbol pairs, and Illegal mix of collations errors all come from the same place. What character sets and collations really are in MySQL and MariaDB, why utf8 is not UTF-8, and how to fix garbled data safely.<\/p>\n","protected":false},"author":1,"featured_media":454,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8,2],"tags":[24,25,23,31],"class_list":["post-350","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-tutorials","category-windows","tag-mariadb","tag-mydbdesk","tag-mysql","tag-utf8mb4"],"_links":{"self":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/350","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/comments?post=350"}],"version-history":[{"count":1,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/350\/revisions"}],"predecessor-version":[{"id":455,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/posts\/350\/revisions\/455"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media\/454"}],"wp:attachment":[{"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/media?parent=350"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/categories?post=350"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/backendside.com\/blog\/wp-json\/wp\/v2\/tags?post=350"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}