Fixing Garbled Text in the WordPress Database: Diagnose and Repair to utf8mb4

This article is a localized edition of the Japanese original. Read the Japanese original

When Japanese (or any non-Latin) text in WordPress turns into garbage, first check what bytes are actually stored in the database. Mojibake can look the same while having different causes, and the correct fix for one cause is the wrong fix for another. For example, the commonly recommended ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 can make the corruption permanent, depending on the state of the data.

The SQL in this article was tested on MySQL 8.4 (as of November 2026). The same syntax works on MariaDB.

Take a backup before you start

Repairing character sets rewrites the data itself. Export the whole database first so you can roll back if something goes wrong.

# If WP-CLI is available
wp db export before-charset-fix.sql

# With mysqldump
mysqldump -u username -p database_name > before-charset-fix.sql

If possible, try the procedure on a copy of the site first and apply it to production afterward.

Guess the cause from what the garbled text looks like

Data replaced with ? no longer holds any information about the original characters, so SQL cannot bring it back. Restore it from a backup taken before the corruption.

If theme or plugin files are saved in Shift_JIS, the problem is in the files, not the database. See Converting WordPress Shift_JIS files to UTF-8.

Check the bytes that are stored

In the SQL tab of phpMyAdmin, or with WP-CLI's wp db query, display the contents of a garbled post in hexadecimal. If your table prefix is not wp_, adjust accordingly.

SELECT ID, post_title, HEX(post_title)
FROM wp_posts
WHERE ID = 123;

The hiragana character "あ" is the three bytes E38182 in UTF-8. Read the result as follows.

• The UTF-8 bytes are stored as they are, like E38182…: the data is correct. The problem is on the display side (the connection character set, or the character set declared for the column)
• Each character has grown to 5-6 bytes, like C3A3C281…: UTF-8 was converted to UTF-8 a second time before being stored (already double-encoded)
• A run of 3F (?): the characters are lost

Also check the character sets of the tables and columns.

-- Table collations
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

-- Character set of each column
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND CHARACTER_SET_NAME IS NOT NULL;

Check the character set settings in wp-config.php

WordPress uses DB_CHARSET in wp-config.php as the character set of the database connection. The current WordPress default is as follows.

define( 'DB_CHARSET', 'utf8mb4' );
define( 'DB_COLLATE', '' );

If you leave DB_COLLATE empty, WordPress picks a collation based on what the database supports.

On older sites, DB_CHARSET may be missing or set to something else, so UTF-8 text was saved through a latin1 connection into latin1 columns. When you then change DB_CHARSET to utf8mb4 or move the data to another server, the mojibake surfaces. Even if reverting the setting makes the pages look right again, the data is still sitting in latin1 columns, so move it into utf8mb4 columns with the steps below.

Case 1: the column is utf8 (utf8mb3) and holds correct UTF-8

If HEX() returns the E38182… form and the column's character set is utf8 / utf8mb3, you can convert straight to utf8mb4. utf8mb4 is a superset of utf8mb3 and can also store 4-byte characters such as emoji.

ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Run it on every WordPress table (wp_options, wp_postmeta, wp_comments and so on) and on tables created by plugins. You can list the tables with SHOW TABLES;.

Case 2: the column is latin1 but holds UTF-8 bytes (one step before double encoding)

This is when HEX() returns E38182… but the column's character set is latin1. It is common on sites migrated from old WordPress installs.

Do not run CONVERT TO CHARACTER SET utf8mb4 in this state. MySQL converts the contents to utf8mb4 as if they were "latin1 characters," so E38182 becomes C3A3C281E2809A and the mojibake is baked into the data.

There are two ways to fix it.

Method A: change the column to a binary type first, then to utf8mb4

When you go through a binary type (BLOB, VARBINARY), MySQL moves the bytes without converting them.

-- Example for a TEXT column
ALTER TABLE wp_posts MODIFY post_title BLOB;
ALTER TABLE wp_posts MODIFY post_title TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

Match the intermediate type to the column type.

Check the original column definition (NOT NULL, DEFAULT and so on) with SHOW CREATE TABLE wp_posts; and repeat the same attributes in the second MODIFY. If you leave them out, those attributes are dropped. Because there are many columns, Method B is less work on sites with many tables.

Method B: dump as latin1 and reload as utf8mb4

If you dump with --default-character-set=latin1, the contents of latin1 columns are written to the file without conversion (still as UTF-8 bytes). You then change the table definitions in that file to utf8mb4 and load it again.

# 1. Dump without conversion (do not write SET NAMES)
mysqldump -u username -p --default-character-set=latin1 --skip-set-charset database_name > latin1.sql

# 2. Change the character set in the table definitions to utf8mb4
sed -e 's/CHARSET=latin1/CHARSET=utf8mb4/g' \
    -e 's/COLLATE=latin1_swedish_ci/COLLATE=utf8mb4_unicode_520_ci/g' \
    -e 's/CHARACTER SET latin1/CHARACTER SET utf8mb4/g' \
    latin1.sql > utf8mb4.sql

# 3. Make sure no latin1 references remain
grep -n 'latin1' utf8mb4.sql

# 4. Load it into an empty database as utf8mb4
mysql -u username -p --default-character-set=utf8mb4 new_database_name < utf8mb4.sql

Use this method when every table in the database is latin1. If some tables are already correctly stored as utf8mb4, step 1 converts their Japanese text to latin1 and replaces it with ?. In a mixed database, list only the latin1 tables after the database name in mysqldump, or use Method A.

After loading into the new database, switch DB_NAME in wp-config.php to it and make sure DB_CHARSET is utf8mb4.

Case 3: the data is already double-encoded (C3A3… is stored)

If HEX() returns the C3A3C281… form, the UTF-8 text was converted once more before being stored, for example because CONVERT TO was run by mistake in the past.

If you have a backup from before the corruption, the safe route is to restore it and fix it with Case 1 or Case 2. Without a backup, you can sometimes undo one layer per column with CONVERT(CAST(CONVERT(列 USING latin1) AS BINARY) USING utf8mb4) (where 列 is the column), but characters that cp1252 cannot represent may not come back, so check the result on a copy before applying it.

-- Only checks the result of the reversal (does not modify data)
SELECT ID,
       post_title,
       CONVERT(CAST(CONVERT(post_title USING latin1) AS BINARY) USING utf8mb4) AS fixed
FROM wp_posts
WHERE ID = 123;

Avoiding mojibake during migration and restore

• Specify --default-character-set=utf8mb4 both when you dump and when you load
• When importing in phpMyAdmin, set "Character set of the file" to utf-8
• Do not open a dump file in a text editor and save it again (the encoding may change to Shift_JIS or similar)
• Create the destination database as utf8mb4 too

CREATE DATABASE wordpress CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

You can change an existing database's default character set with ALTER DATABASE, but that only changes the default for tables created later; existing tables and data stay the same.

Related articles

• Converting the WordPress database to utf8mb4
• Changing MySQL character sets and collations

Questions about WordPress? The publisher of this site is based in Japan and replies in Japanese. Contact (Japanese)