MySQL

MySQL Character Encoding Showing Question Marks

Question marks (or garbled characters like ’) appearing instead of accented letters, emoji, or other special characters almost always means there's a mismatch in character encoding somewhere along the path from your application, through the connection, into the database column itself.

The Problem

Text containing accented characters, non-Latin scripts, or emoji gets stored or displayed incorrectly:

Expected: café, naïve, 日本語
Actual:   caf?, na?ve, ??????

Sometimes the data looks correct when inserted but wrong when read back, or vice versa, depending on which part of the chain has the mismatched encoding.

Why It Happens

Character encoding has to be consistent across three separate layers for special characters to work correctly: the column/table's defined charset, the connection's charset, and the encoding your application actually sends. A mismatch anywhere in this chain causes corruption. Common causes:

  • The table or column was created with an older charset like latin1 instead of a Unicode-capable one like utf8mb4, so it simply can't represent the characters being stored
  • The database connection itself isn't specifying UTF-8, even if the underlying table supports it — MySQL uses a default connection charset that may not match what your application actually sends
  • Using utf8 instead of utf8mb4 — MySQL's utf8 charset is actually a legacy, incomplete implementation limited to 3 bytes per character, which cannot represent emoji or certain less common characters that require 4 bytes
  • The application code itself reads or writes the data using the wrong encoding before it even reaches MySQL, corrupting the data before the database layer is involved at all

The Fix

First, check what charset your table and columns are actually using:

SHOW CREATE TABLE your_table;

Look for CHARSET= in the output. If it shows latin1 or plain utf8 (not utf8mb4), convert the table to utf8mb4, which correctly supports the full range of Unicode characters, including emoji:

ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Next, ensure your database connection explicitly specifies utf8mb4, rather than relying on a possibly mismatched default. In a typical connection string:

mysql://user:password@localhost:3306/dbname?charset=utf8mb4

Or, if connecting manually within your application code, set it explicitly right after connecting:

SET NAMES utf8mb4;

If you're using the MySQL command-line client directly and seeing question marks there specifically (even though the data might actually be stored correctly), your terminal's own encoding might be the issue rather than MySQL. Confirm the client is also using UTF-8:

mysql --default-character-set=utf8mb4 -u user -p

For existing data that was already corrupted by being inserted through a mismatched connection, simply fixing the charset going forward won't repair already-corrupted rows — those need to be identified and re-inserted with the correct encoding, since the original bytes may already be irreversibly wrong.

Still Not Working?

If everything above is correctly set to utf8mb4 and you're still seeing corruption, check your application server's own default locale and encoding settings, particularly if you're reading files (like a CSV import) that themselves have their own encoding separate from anything MySQL controls. A file saved as Latin-1 or Windows-1252 and read as if it were UTF-8 (or vice versa) will produce corrupted text before it ever reaches a database query, regardless of how correctly the database itself is configured.

It's also worth checking your my.cnf (or my.ini on Windows) server configuration file directly, since some MySQL installations still default to latin1 at the server level even when individual databases and connections are configured otherwise. Setting the defaults explicitly at the server level prevents new databases or connections from silently reverting to the old default:

# my.cnf
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

Restart the MySQL service after changing this file for the new defaults to take effect.

Finally, if you migrated data from an older system or a different database engine entirely, double-check whether the export/import process itself preserved encoding correctly. A dump file created with one encoding and restored while MySQL assumes another is a common source of corruption that has nothing to do with your current table or connection configuration — inspecting the raw dump file's bytes for a known problematic string can confirm whether the corruption already existed before the data ever reached MySQL.

You might also like