Switch MySQL to utf8mb4: Migration Guide & Troubleshooting

1. Introduction

Why Changing MySQL’s Character Set Is Necessary

Common Issues and Their Causes

1. Character garbling

• utf8 and latin1 are mixed
• Client and server character set settings differ

1. Search-related issues

• Differences in collation prevent obtaining the intended search results
• Sorting order differs from expectations

1. Data migration problems

• Because utf8mb4 is not used, emojis and special symbols cannot be stored
• Character set conversion is not performed correctly during data export/import

Purpose and Structure of This Article

Article Flow

1. Basic knowledge of MySQL character sets
2. How to check the current character set
3. How to change MySQL character set
4. Post-change troubleshooting
5. Impact of character set changes on performance
6. Recommended settings (best practices)
7. FAQ (frequently asked questions)

2. MySQL Character Sets: Basic Understanding

What is a character set (Character Set)?

Common character sets

What is collation (Collation)?

Common collations

utf8 vs utf8mb4 Differences

Reasons to switch from utf8 to utf8mb4

1. Future compatibility: In modern systems, utf8mb4 is becoming the standard.
2. Storing special characters and emojis: Using utf8mb4 ensures you can safely handle data in social media posts and messaging apps.
3. Internationalization support: Reduces the risk of garbled text when building multilingual systems.

Summary

• Character set (Character Set) determines how data is stored and processed.
• Collation defines the rules for comparing characters.
• MySQL’s utf8 actually supports only up to 3 bytes, so using utf8mb4 is recommended.
• utf8mb4_unicode_ci is the recommended collation for general use.

3. How to Check the Current Character Set

How to Check the Current Character Set

Check the MySQL Server’s Overall Character Set

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                        |
+--------------------------+----------------------------+

Check the Character Set per Database

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'database_name';

+----------------+----------------------+----------------------+
| SCHEMA_NAME    | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------+----------------------+----------------------+
| my_database   | utf8mb4               | utf8mb4_unicode_ci   |
+----------------+----------------------+----------------------+

Check the Table’s Character Set

SHOW CREATE TABLE table_name;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

• DEFAULT CHARSET=latin1 → not utf8mb4, so it needs to be changed
• COLLATE=latin1_swedish_ci → changing to utf8mb4_unicode_ci is more appropriate

Check the Column’s Character Set

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'database_name' 
AND TABLE_NAME = 'table_name';</> Example output

+-------------+--------------------+----------------------+
| COLUMN_NAME | CHARACTER_SET_NAME | COLLATION_NAME       |
+-------------+--------------------+----------------------+
| name        | latin1             | latin1_swedish_ci    |
| email       | utf8mb4            | utf8mb4_unicode_ci   |
+-------------+--------------------+----------------------+

Summary

• MySQL’s character set is configured at multiple levels (server, database, table, column)
• By checking the character set at each level, you can make appropriate changes
• Use commands like SHOW VARIABLES and SHOW CREATE TABLE to thoroughly understand the current configuration

4. How to Change MySQL Character Set

Change the Server-Wide Default Character Set

Steps

1. Open the configuration file

• On Linux: bash sudo nano /etc/mysql/my.cnf
• On Windows:
  • Open C:ProgramDataMySQLMySQL Server X.Xmy.ini

1. Add or modify character set settings Add or modify the following in the mysqld section.

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

3. Restart MySQL

   sudo systemctl restart mysql

   net stop MySQL && net start MySQL

4. Verify after changes

   SHOW VARIABLES LIKE 'character_set_server';

Change Character Set at the Database Level

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Verification after change

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME 
FROM information_schema.SCHEMATA 
WHERE SCHEMA_NAME = 'mydatabase';

Change Character Set at the Table Level

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Verification after change

SHOW CREATE TABLE users;

Change Character Set at the Column Level

ALTER TABLE users MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Verification after change

SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME 
FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA = 'mydatabase' 
AND TABLE_NAME = 'users';

Verification After Changes and the Importance of Backups

Data Backup

mysqldump -u root -p --default-character-set=utf8mb4 mydatabase > backup.sql

Reconfirm Settings

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
SHOW CREATE TABLE users;

Add and Display Test Data

INSERT INTO users (name, email) VALUES ('Test User', 'test@example.com');
SELECT * FROM users;

Summary

• Server-wide character set change: Edit my.cnf and set character-set-server=utf8mb4
• Database character set change: ALTER DATABASE mydatabase CHARACTER SET utf8mb4
• Table character set change: ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4
• Column character set change: ALTER TABLE users MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8mb4
• After changes, always verify the settings and test the data

5. Troubleshooting After Changing Character Encoding

Causes of Garbled Text and How to Address Them

Solution 1: Properly Set the Client Character Set

SET NAMES utf8mb4;

Solution 2: Correctly Convert Pre-Change Data

UPDATE users SET name = CONVERT(CAST(CONVERT(name USING latin1) AS BINARY) USING utf8mb4);

latin1 to utf8mb4 Post-Change Considerations

1. Back up the current data

   mysqldump -u root -p --default-character-set=latin1 mydatabase > backup.sql

2. Change the database character set

   ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. Change the table character set

   ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. Re-import the data

   mysql -u root -p --default-character-set=utf8mb4 mydatabase < backup.sql

Data Cannot Be Searched Correctly After the Change

Case 1: LIKE Search Not Working

SELECT * FROM users WHERE name COLLATE utf8mb4_unicode_ci LIKE '%Tanaka%';

Case 2: Sort Order Changed

SELECT * FROM users ORDER BY BINARY name;

Measures on the Application Side

For PHP

mysqli_set_charset($conn, 'utf8mb4');

For Python (MySQL Connector)

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="mydatabase",
    charset="utf8mb4"
)

For Node.js (MySQL2)

const mysql = require('mysql2');

const connection = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: 'password',
  database: 'mydatabase',
  charset: 'utf8mb4'
});

Summary

• Issues that arise after changing the character set can be categorized into three areas: client settings, data conversion, and application settings.
• To prevent garbled text, unify the client-side character set with SET NAMES utf8mb4.
• Be aware of changes in LIKE searches and sort order, and specify COLLATE as needed.
• Setting utf8mb4 on the application side also prevents encoding mismatches.

6. Impact of Character Set Changes on Performance

Increase in Storage Usage Due to Character Set Change

Bytes per character for each character set

Solution

ALTER TABLE posts MODIFY COLUMN title VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Increase in Index Size

Check Index Impact

SHOW INDEX FROM users;

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Solution

ALTER TABLE users MODIFY COLUMN email VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Impact on Query Performance

Operations That May Be Affected

• LIKE searches containing large amounts of data
• ORDER BY processing
• JOIN query performance

Solution

CREATE INDEX idx_name ON users(name(100));

Memory Usage and Buffer Size Tuning

Recommended Settings

[mysqld]
innodb_buffer_pool_size = 1G
query_cache_size = 128M

Summary

• Changing to utf8mb4 increases storage usage
• Index size grows and may exceed limits
• Query performance can be affected
• Memory usage rises, requiring buffer size adjustments

7. Recommended Settings (Best Practices)

Recommended MySQL Character Set Settings

my.cnf Recommended Settings

1. MySQL Server Character Set Settings

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init-connect='SET NAMES utf8mb4'
skip-character-set-client-handshake
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
query_cache_size = 128M

2. Client‑Side Character Set Settings

[client]
default-character-set = utf8mb4

Recommended Database Settings

CREATE DATABASE mydatabase DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Recommended Table Settings

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  email VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Changing Character Set of Existing Tables

ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Difference Between utf8mb4_general_ci and utf8mb4_unicode_ci

Index Optimization

CREATE FULLTEXT INDEX idx_fulltext ON articles(content);

Summary

• The combination of utf8mb4 + utf8mb4_unicode_ci is recommended
• Standardize server settings (my.cnf) and unify the character set on connection
• Explicitly specify utf8mb4 at the database, table, and column levels
• Using VARCHAR(191) avoids index length limitations
• Using utf8mb4_unicode_ci enables accurate comparisons

8. FAQ (Frequently Asked Questions)

What is the difference between utf8 and utf8mb4?

SHOW VARIABLES LIKE 'character_set_server';

Will data be lost when changing MySQL character set?

mysqldump -u root -p --default-character-set=utf8mb4 mydatabase > backup.sql

How to resolve garbled characters?

UPDATE users SET = CONVERT(CAST(CONVERT(name USING latin1) AS BINARY) USING utf8mb4);

What are the risks of converting from latin1 to utf8mb4?

ALTER DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Does switching to utf8mb4 affect performance?

ALTER TABLE users MODIFY COLUMN email VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Which should be used: utf8mb4_general_ci or utf8mb4_unicode_ci?

Will queries become slower after switching to utf8mb4?

CREATE FULLTEXT INDEX idx_fulltext ON articles(content);

Summary

Finally