How to Change MySQL Character Set and Collation (utf8mb4)

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

In MySQL, the character set (charset) decides which byte sequences are used to store characters, and the collation decides how characters are compared and sorted. Problems such as emoji that cannot be saved, characters replaced by ?, or searches that do or do not distinguish upper and lower case all come down to these two settings. Everything in this article was checked against the MySQL 8.4 reference manual as of November 2026, and the SQL was run on MySQL 8.4.

The settings exist at four levels

Character set and collation are set separately at the following four levels. When a lower level does not specify them, the value of the level above it is used.

The setting actually used to store data is the column setting. Changing the database or server setting does not change existing columns.

In addition, there are character sets used for communication with the client (character_set_client, character_set_connection and character_set_results). If these do not match the columns, text becomes garbled when it is saved or displayed.

Step 1: Check the current settings

-- Server and connection settings
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

-- Database defaults
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'mydb';

-- Default collation of each table
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

-- Per-column settings (the values actually used for storage)
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'mydb' AND CHARACTER_SET_NAME IS NOT NULL;

Replace mydb with your database name. To see the whole definition of one table at once, SHOW CREATE TABLE table_name; is handy.

Which character set and collation to use

Choose utf8mb4 as the character set. It can also store 4-byte characters such as emoji and some kanji. MySQL's utf8 is an alias for utf8mb3 (up to 3 bytes), which cannot store 4-byte characters, and utf8mb3 itself is deprecated. Since MySQL 8.0, the server's default character set is utf8mb4 and the default collation is utf8mb4_0900_ai_ci.

The main collations differ as follows.

With an _ai_ci collation, for example, the Japanese strings "ハハ" and "パパ" compare as equal. For columns where searches or unique constraints (UNIQUE) must tell them apart, specify a collation such as utf8mb4_ja_0900_as_cs. Changing the collation can also make rows violate a unique constraint and cause ALTER TABLE to fail, so check for duplicates first.

Collations with 0900 in the name treat trailing spaces as ordinary characters ('a' and 'a ' are different strings). If you switch from an older collation, search results can change because of the different handling of spaces. If the same database is shared with MariaDB or MySQL 5.7, choose a collation the other side supports.

Step 2: Back up before you start

Converting the character set rebuilds the table, which is heavy work, and undoing it requires a backup.

mysqldump -u root -p --single-transaction mydb > ~/mydb-before-charset.sql

With -p alone, you are prompted for the password. Do not put the password directly on the command line.

Step 3: Change the database default

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

This changes only the default for tables created from now on. Existing tables and columns stay as they are.

When you create a new database, specify the settings from the start.

CREATE DATABASE newdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Step 4: Convert existing tables and columns

To convert the table default and all string columns in the table at once, use CONVERT TO.

ALTER TABLE posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Without CONVERT TO, as in ALTER TABLE posts CHARACTER SET utf8mb4;, only the table default changes and existing columns are not converted.

To change a single column, use MODIFY. You must restate the column's original NOT NULL and DEFAULT, or they are dropped.

ALTER TABLE posts
  MODIFY title VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_ja_0900_as_cs NOT NULL;

To convert all tables in a database, generate the conversion SQL, review it, and then run it.

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb' AND TABLE_TYPE = 'BASE TABLE';

Things to watch when converting

• An ALTER TABLE that changes the character set or collation copies and rebuilds the whole table (ALGORITHM=COPY). On large tables this takes time, and writes to the table are blocked meanwhile. Do it when traffic is low.
• Going from utf8mb3 or latin1 to utf8mb4 increases the maximum number of bytes per character. A TEXT column may be changed to MEDIUMTEXT so that the data still fits.
• InnoDB index keys have a length limit (3072 bytes with row formats such as DYNAMIC, 767 bytes with the older COMPACT and REDUNDANT). utf8mb4 is counted as 4 bytes per character, so in a 767-byte environment you cannot index a VARCHAR(255) column. Check Row_format in SHOW TABLE STATUS and, if needed, limit the index to the first 191 characters or similar.
• Characters that do not exist in the source character set can cause data loss. Compare the row counts and the contents of key columns before and after the conversion.

When the data is already garbled

CONVERT TO converts data that is correctly stored in the column's character set into another character set. If, for example, a column is latin1 but actually contains UTF-8 byte sequences (saved through a misconfigured connection), running CONVERT TO encodes it twice and makes it impossible to fix.

In that case, first change the column to a binary type to drop its character set label, then change it back to a string type with the correct character set (this is the method shown in the official MySQL manual).

-- Example: fixing title, which holds UTF-8 but was created as a latin1 column
ALTER TABLE posts MODIFY title VARBINARY(800);
ALTER TABLE posts MODIFY title VARCHAR(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL;

Make the binary type at least as long as the original column's maximum byte length (BLOB for a TEXT column, MEDIUMBLOB for MEDIUMTEXT). This method cannot fix a column that mixes data in several character sets. Take a backup before trying it, and test it on a copy of the table first. For garbled text in WordPress, also see Fixing garbled characters in the WordPress database.

Step 5: Change the server default in the configuration file

Changing the server default makes it the default for databases created without a character set. The location of the configuration file depends on the environment.

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

[client]
default-character-set = utf8mb4

default-character-set under [client] is the character set that clients such as the mysql command use when they connect. After the change, restart MySQL (sudo systemctl restart mysql on Ubuntu) and check the values with the SQL from Step 1.

For application connections, specify the character set when connecting. With PHP's PDO, add charset=utf8mb4 to the DSN.

For a WordPress database

WordPress uses these two settings in wp-config.php to decide the connection's character set and collation.

<?php
define( 'DB_CHARSET', 'utf8mb4' );
define( 'DB_COLLATE', '' );

When DB_COLLATE is empty, the database's default collation is used. Since WordPress 4.2, WordPress creates tables in utf8mb4 when the database supports it. Old tables made only of utf8 (utf8mb3) columns are converted to utf8mb4 by the WordPress upgrade routine, but tables that contain latin1 or other columns are not converted (confirmed in maybe_convert_table_to_utf8mb4() in the WordPress source code). You only need to convert tables by hand when such old tables remain. Before changing wp-config.php, check with the SQL from Step 1 which character set the table columns actually use.

Summary

• Character set and collation exist at four levels (server, database, table, column), and the column setting is what is used for storage.
• ALTER DATABASE changes only the default for tables created later. Convert existing tables with ALTER TABLE ... CONVERT TO.
• Use utf8mb4 as the character set. Choose the collation according to how you want comparisons to work (voiced marks, kana, case).
• Back up before converting, and check index lengths and duplicates under unique constraints.
• Data that is already garbled cannot be fixed with CONVERT TO. Try the binary-type method on a copy of the table first.

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