MySQL Character Set and Collation Conversion: Complete Guide from latin1 to utf8mb4

Fundamental Concepts of Character Sets and Collations

Before delving into conversion methods, it's essential to understand the basic concepts of Character Sets and Collations. Character sets define the collection of characters that can be stored in a database, while collations determine the sorting and comparison rules for these characters. In MySQL, latin1_general_ci uses single-byte encoding primarily supporting Western European language characters, whereas utf8mb4_general_ci employs four-byte UTF-8 encoding supporting global characters including Chinese, Japanese, and Korean.

Table-Level Conversion: Core Conversion Method

According to best practices, table-level conversion is the most commonly used and efficient approach. The ALTER TABLE CONVERT TO CHARACTER SET statement not only changes the table's default character set but also automatically converts the character sets of all columns within the table. The advantage of this method lies in its atomic operation, ensuring data consistency.

Below is a complete example of table-level conversion:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

When executing this statement, the following operations are automatically handled:

• Changing the table's default character set to utf8mb4
• Converting character sets for all CHAR, VARCHAR, and TEXT type columns
• Updating corresponding collation settings
• Maintaining the integrity of existing data

Limitations of Database-Level Conversion

Although the ALTER DATABASE statement can modify the database's default character set, this approach has significant limitations:

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Database-level conversion only affects newly created tables going forward and does not change any existing tables. This means that pre-existing tables retain their original character set settings and require individual conversion.

Special Application Scenarios for Column-Level Conversion

In certain specific situations, precise control over individual columns may be necessary. Column-level conversion is suitable for scenarios with mixed character set requirements:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This method allows developers to set different character sets for different columns but should be used cautiously, as mixed character sets can lead to decreased query performance and inconsistent sorting issues.

Export-Import Alternative Approach

For large databases or complex migration scenarios, the export-import method may be more appropriate. The basic workflow of this approach includes:

# Export database
mysqldump -u username -p --default-character-set=latin1 database_name > backup.sql

# Modify character set settings in backup file
sed -i 's/latin1/utf8mb4/g' backup.sql

# Re-import database
mysql -u username -p --default-character-set=utf8mb4 database_name < backup.sql

The advantage of this method is the ability to perform data validation and cleanup during conversion, though it requires additional storage space and longer downtime.

Technical Details of Character Conversion

During character set conversion, MySQL performs re-mapping of character encodings. For conversion from latin1 to utf8mb4, since utf8mb4 is a superset of latin1, all latin1 characters can find corresponding encodings in utf8mb4, resulting in relatively low risk of data loss.

However, the following technical details require attention:

• Index rebuilding: After character set conversion, all string-based indexes need to be rebuilt
• Storage space changes: utf8mb4 characters may occupy more storage space
• Collation impact: The behavior of comparison and sorting operations may change

Performance Considerations and Best Practices

When performing character set conversion, the following performance factors should be considered:

• Execute conversion operations during off-peak hours to minimize impact on production environments
• For large tables, consider batch conversion or using online DDL tools
• Perform complete backups before conversion to ensure data security
• Verify data integrity and application compatibility after conversion

The recommended conversion sequence is: first validate in test environment, then conduct small-scale pilot in production environment, and finally roll out comprehensively.

Common Issues and Solutions

During actual conversion processes, the following common issues may be encountered:

• Foreign key constraint conflicts: Ensure related tables use the same character set
• Stored procedure compatibility: Check string operations in stored procedures
• Application adaptation: Update connection strings and character set settings
• Index performance: Monitor query performance changes after conversion

Through systematic planning and testing, these issues can be effectively avoided, ensuring smooth character set conversion.