Convert a WordPress Database to utf8mb4: Check, Convert, wp-config.php

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

The standard character set for a WordPress database is utf8mb4. On older sites, tables are sometimes still utf8 (shown as utf8mb3 in newer MySQL and MariaDB) or latin1. utf8 (utf8mb3) stores at most 3 bytes per character, so it can't store emoji or some CJK characters (4-byte characters).

This article walks through checking your current character sets, converting to utf8mb4, and verifying the result. WordPress's behavior was checked against the WordPress source code (wp-includes/class-wpdb.php and others) as of November 2026.

What happens with utf8 tables

• Emoji in post titles, content and excerpts are saved by WordPress as HTML character references (in the form &#x...;). They still display, but the database doesn't hold the original characters.
• In other fields (custom fields, comments, plugin tables and so on), saving a value that contains a 4-byte character can fail.
• If a table is latin1 and holds Japanese or other non-Latin text, it may look garbled in tools such as phpMyAdmin (see "Be careful with latin1 tables" below).

Back up before you start

Converting character sets rewrites the table contents. Take a full database backup first so you can roll back if something goes wrong.

# Run in your WordPress directory
wp db export before-utf8mb4.sql

If you can't use WP-CLI, use phpMyAdmin's export or your hosting provider's backup feature. Converting large tables takes time, and writes to the table may be held up meanwhile. Work during a low-traffic period, and if possible try it on a staging environment first.

Check the current character sets

Run the following SQL in phpMyAdmin's "SQL" tab or with wp db query. DATABASE() is the database you are currently connected to.

-- Default character set and collation of the database
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = DATABASE();

-- Collation per table (anything not starting with utf8mb4_ needs converting)
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME;

-- Columns whose character set is not utf8mb4
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND CHARACTER_SET_NAME IS NOT NULL
  AND CHARACTER_SET_NAME <> 'utf8mb4'
ORDER BY TABLE_NAME, COLUMN_NAME;

With WP-CLI, put the SQL inside the quotes of wp db query "...". If the last query returns nothing, every column is already utf8mb4.

Be careful with latin1 tables

If you find latin1 tables, check before converting whether they really contain latin1 characters, or whether UTF-8 byte sequences have simply been stored in a latin1 container. The latter is common on old sites.

SELECT ID, HEX(post_title) FROM wp_posts WHERE post_title <> '' LIMIT 5;

If the hex of a Japanese title is a run of 3-byte sequences starting with values such as E3 (for example, the hiragana character "a" is E38182), and the title displays correctly in WordPress, then UTF-8 is stored in a latin1 container. Running CONVERT TO from the next section on such a table converts each byte as a latin1 character, which really does garble the text. This case needs a different procedure. See "Fixing garbled text in the WordPress database", and test on a copy of the database before applying it to production.

utf8 (utf8mb3) tables can be converted to utf8mb4 directly, because utf8mb4 includes every utf8mb3 character.

Convert the database and tables to utf8mb4

These examples use the utf8mb4_unicode_520_ci collation. When WordPress is set to use utf8mb4_unicode_ci, it switches to utf8mb4_unicode_520_ci when connecting if the database supports it. Using the same collation keeps things simple.

Convert one at a time with SQL

-- Database default (used for tables created from now on)
ALTER DATABASE `wordpress_db`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_520_ci;

-- Convert the table and all of its columns
ALTER TABLE `wp_posts`
  CONVERT TO CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_520_ci;

Replace wordpress_db and wp_posts with your own database and table names. ALTER DATABASE only changes the default; existing tables stay as they are. Run ALTER TABLE ... CONVERT TO for each table. Check the table prefix (the wp_ part) in $table_prefix in wp-config.php.

Convert everything with WP-CLI

With WP-CLI, wp db query uses the connection details from wp-config.php, so you don't have to put the password on the command line. Run this script in your WordPress directory.

#!/bin/bash
# Convert tables with the WordPress prefix to utf8mb4
set -eu

COLLATION="utf8mb4_unicode_520_ci"
DB_NAME="$(wp config get DB_NAME)"

wp db query "ALTER DATABASE \`${DB_NAME}\` CHARACTER SET utf8mb4 COLLATE ${COLLATION};"

wp db tables --all-tables-with-prefix | while read -r table; do
  echo "Converting: ${table}"
  wp db query "ALTER TABLE \`${table}\` CONVERT TO CHARACTER SET utf8mb4 COLLATE ${COLLATION};"
done

echo "Done"

• wp db tables --all-tables-with-prefix prints the tables with the WordPress prefix (including ones created by plugins), one per line. Tables of other applications in the same database that use a different prefix are not converted.
• set -e stops the script if a conversion fails. Check the error for that table before continuing.
• If you have latin1 tables, do the check from the previous section before running this script.

If you get ERROR 1071

ERROR 1071 (42000): Specified key was too long means an index exceeds the length limit. utf8mb4 counts each character as up to 4 bytes, so an index on a VARCHAR(255) column is 1020 bytes.

• On MySQL 5.7.9 or later and MariaDB 10.2.2 or later, with the DYNAMIC row format (the default in these versions), the limit is 3072 bytes and this error normally doesn't occur.
• On older versions, or for tables with the COMPACT row format, the limit is 767 bytes. WordPress core tables limit their indexes to the first 191 characters (764 bytes), so they're fine, but plugin tables can hit this error.

If you're running an old database version, consider upgrading it. You can check each table's row format with this SQL.

SELECT TABLE_NAME, ROW_FORMAT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE();

wp-config.php settings

Set the character set in wp-config.php like this.

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

• DB_CHARSET is the character set WordPress uses when it connects to the database. Current WordPress replaces utf8 with utf8mb4 when connecting, even if utf8 is written there. So editing wp-config.php alone doesn't change your tables' character set. Converting the tables is the real work.
• If you leave DB_COLLATE empty, WordPress picks a collation that matches the character set (utf8mb4_unicode_ci for utf8mb4, or utf8mb4_unicode_520_ci if the database supports it). Unless you have a specific reason, leave it empty.

As with any edit to WordPress files, keep a copy of the original wp-config.php before changing it.

Verify after converting

# Any tables left that aren't utf8mb4? (no output means all converted)
wp db query "SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_COLLATION NOT LIKE 'utf8mb4%';"

# Connection character sets (are character_set_client, connection and results utf8mb4?)
wp db query "SHOW VARIABLES LIKE 'character_set%';"

To confirm that 4-byte characters are stored as-is, create a test draft. This example builds one emoji with printf, puts it in the title, and shows the stored bytes in hex.

# Create a draft with one emoji in the title and capture the post ID
ID=$(wp post create --post_title="$(printf 'utf8mb4 test \xF0\x9F\x98\x80')" --post_status=draft --porcelain)

# Show the stored title in hex
wp db query "SELECT HEX(post_title) FROM $(wp db prefix)posts WHERE ID = ${ID};"

# Delete the test draft
wp post delete "$ID" --force

If the result ends in F09F9880, the emoji was stored as 4 bytes. If it contains 26 23 78, which represents &#x, as in 262378316636..., the column is still utf8 (utf8mb3) and WordPress has replaced the emoji with a character reference.

Make utf8mb4 the MySQL/MariaDB default too

On servers you manage yourself, making utf8mb4 the database server's default character set means databases and tables created later will also be utf8mb4. Add this under [mysqld] in the configuration file (for example /etc/mysql/mysql.conf.d/mysqld.cnf for MySQL on Ubuntu, or /etc/mysql/mariadb.conf.d/50-server.cnf for MariaDB).

[mysqld]
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_520_ci

Restart the database server after editing. In MySQL 8.0 and later, the default character set is already utf8mb4. On shared hosting, the hosting company manages this setting and you can't change it.

Cautions

• Before you start, take a backup with wp db export or similar, and make sure you know how to restore it (wp db import before-utf8mb4.sql).
• Running CONVERT TO directly on a latin1 table can garble its contents. Check the contents with HEX() first.
• While large tables are converting, writes from the site may be held up. Work during a low-traffic period.

Summary

• Use information_schema to find tables and columns that aren't utf8mb4
• After taking a backup, run ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 on each table (WP-CLI's wp db tables --all-tables-with-prefix and wp db query let you do them all at once)
• For latin1 tables, check with HEX() that the contents aren't UTF-8 byte sequences before converting
• In wp-config.php, set DB_CHARSET to utf8mb4 and leave DB_COLLATE empty. The setting alone doesn't change your tables
• Use a test draft to confirm that an emoji is stored as F09F9880

Differences between collations and changing individual columns are covered in "MySQL character sets and collations".

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