When deploying web applications or performing database internationalization, developers often need to change MySQL's default character set from latin1 or utf8 to the more compatible utf8mb4. However, in practice, many users encounter problems such as failure to change the MySQL default encoding, ineffectiveness after setting it, and garbled characters still appearing in the database. While seemingly a simple character set change, it involves multiple steps, including system configuration, connection methods, SQL syntax, and table structure restrictions. A single mistake could render the change ineffective and even impact online business.

Changing the Default Encoding in the Wrong Place

MySQL's default encoding isn't globally set with a single command. Instead, it's defined at multiple levels: server, database, connection, table, and field. Changing only one level while ignoring others may still result in a different encoding.

For example, many users change character-set-server=utf8mb4 in /etc/my.cnf , believing the global setting has been successful, only to discover that newly created databases are still set to utf8. This is because MySQL's default database, in some environments, depends on the database creation statement or initialization settings.

Solution:

1. Ensure the following parameters are correctly added to the [mysqld] section of the my.cnf file:

character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

2. Restart the MySQL service and then execute SHOW VARIABLES LIKE 'character_set%'; to verify that the change was successful.

3. When creating a new database, you still need to display the specified encoding:

CREATE DATABASE dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. If you use a container deployment such as Docker, you need to modify the image startup parameters or initialization script instead of the host configuration file.

Not Changing the Client Connection Encoding

Another common mistake is not changing the connection encoding settings in client programs like PHP, Python, and Java after changing the server's default encoding. This can cause client-server communication to use UTF-8 or Latin-1, even if the server supports UTF-8MB4. This can result in interrupted, truncated, or garbled character transmission.

For example, in PHP, the default MySQL connection may use Latin-1 encoding, requiring manual connection character set specification.

Solution:

1. PHP (MySQLi) Example:

$mysqli = new mysqli("localhost", "user", "password", "database");
$mysqli->set_charset("utf8mb4");

2. The PDO connection needs to declare the character set in the DSN:

$dsn = "mysql:host=localhost;dbname=yourdb;charset=utf8mb4";
$pdo = new PDO($dsn, "user", "password");

3. Add parameters when connecting to the MySQL CLI tool:

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

4. If you are connecting to the database remotely, ensure that the MySQL server allows the utf8mb4 transport protocol and is not being downgraded by a proxy or middleware.

Existing tables or fields are not affected by the change

Changing the character set in the MySQL configuration file only affects the default character set for newly created databases and tables; existing table structures are not automatically changed. If your database is a long-running system or has imported legacy tables, you must explicitly change the character set for each table and field.

This is also a problem that is often overlooked. Many people find that existing data is still garbled after configuration changes because the table structure remains unchanged.

Solution:

1. Run the following command to check the current encoding of the table:

SELECT TABLE_NAME, TABLE_COLLATION 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'your_database';

2. Use ALTER TABLE to batch convert table character sets:

ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3. If encoding is specifically specified for a field, the field-level character set also needs to be modified separately.

ALTER TABLE your_table 
MODIFY column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4. Write a script to batch modify the entire database table structure to unify the character set and collation rules.

Conversion failure caused by index field length exceeding the limit

utf8mb4 is a four-byte character set and takes up more storage space than utf8 (three-byte). When certain fields in the table have indexes (especially VARCHAR(255) with indexes), converting to utf8mb4 will result in an error because the index length exceeds the MySQL limit (767 bytes).

This problem mainly occurs in MySQL 5.6 and below, or when the DYNAMIC/COMPRESSED row format is not enabled.

Solution:

1. Shorten the index field length appropriately:

ALTER TABLE your_table 
CHANGE column_name column_name VARCHAR(191) 
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. Change the table's storage engine row format to DYNAMIC:

ALTER TABLE your_table ROW_FORMAT=DYNAMIC;

3. If you use the InnoDB engine, you can extend the index length by setting parameters:

innodb_large_prefix = 1
innodb_file_format = Barracuda
innodb_file_per_table = 1

4. After configuring, restart MySQL for the configuration to take effect.

The system or MySQL version does not support utf8mb4

Some older versions of MySQL (such as 5.1 and 5.0) do not support utf8mb4. Forcibly configuring it will be ineffective or may not be recognized after configuration. Even if utf8mb4 is supported, some older versions do not support more sophisticated collations such as utf8mb4_unicode_ci or utf8mb4_0900_ai_ci.

Solution:

1. Ensure that your MySQL version is 5.5.3 or higher. MySQL 5.7+ or 8.0+ is recommended.

2. For collation compatibility issues, use utf8mb4_general_ci instead of the advanced collation.

3. If upgrading is absolutely impossible, you can temporarily use utf8, but be aware that it is incompatible with four-byte characters such as emojis.

Character set changes not taking effect without restarting the service

Modifying the configuration file without restarting the MySQL service is a common oversight. Even if you write to my.cnf, the changes won't take effect unless the service is reloaded.

Solution:

1. Restart the MySQL service after modifying the configuration:

sudo systemctl restart mysql

2. After modification, you can use the following command to verify:

SHOW VARIABLES LIKE 'character%';

Ensure that parameters such as character_set_server and collation_server are all set to utf8mb4.

Inconsistent character set settings when importing SQL files

Some developers fail to specify a character set when importing old databases or backups. This causes the original utf8 data to be interpreted as latin1 or other encodings, resulting in garbled characters during the import process.

Solution:

1. Specify the default character set before importing:

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

2. The encoding should also be explicitly specified when exporting:

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

3. If the SQL file contains a SET NAMES statement, ensure that it matches the actual character set being imported.

Character encoding consistency is fundamental to multilingual websites, cross-platform interfaces, and global products. Developers are advised to adopt utf8mb4 as the standard character set from the outset of system development and maintain consistent settings across all links to fundamentally prevent issues such as garbled characters, poor compatibility, and transcoding failures.