How to Convert Database Character Encoding in WordPress

Proper database character encoding configuration in WordPress is essential to prevent display errors, issues with special characters, and to ensure compatibility with multiple languages. Although the process can be complex, following a structured approach allows for a safe and effective conversion.

1. Importance of Character Encoding Conversion in WordPress

Until WordPress 2.1.3, databases were created using the latin1 character set with the latin1_swedish_ci collation. From version 2.2 onward, WordPress introduced the ability to define encoding in the wp-config.php file, setting UTF-8 as the recommended standard.

However, modifying this setting in wp-config.php does not alter the existing tables and columns, leading to inconsistencies in data retrieval and storage.

2. Preparation Before Conversion

Before making any changes, it is crucial to create a full database backup. Recommended steps:

• Export the database using phpMyAdmin or mysqldump.
• Work in a staging environment before applying changes to the live site.
• Check the current database encoding using the following SQL query:

SELECT schema_name, default_character_set_name FROM information_schema.schemata;

3. Converting the Database Character Encoding

3.1. Change Database Encoding

The following SQL command sets UTF-8 as the default character set for the database:

ALTER DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This change only applies to new tables, so existing ones must be updated manually.

3.2. Convert Table Encoding

To modify the encoding of each existing table, run the following command for each one:

ALTER TABLE wp_posts CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Repeat this process for all tables, replacing wp_posts with each table’s name.

3.3. Convert Column Encoding

Columns containing textual data (TEXT, VARCHAR, LONGTEXT, etc.) must be updated individually:

ALTER TABLE wp_posts CHANGE post_content post_content LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For VARCHAR columns, it’s essential to retain their character limit:

ALTER TABLE wp_users CHANGE user_email user_email VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For ENUM columns, define the allowed values explicitly:

ALTER TABLE wp_comments CHANGE comment_subscribe comment_subscribe ENUM('Y','N') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'N';

4. Alternative Method: BLOB Conversion

If characters still display incorrectly after conversion, an intermediate step is recommended: convert columns to a binary (BLOB) format before restoring them with the new encoding:

ALTER TABLE wp_posts CHANGE post_content post_content LONGBLOB;
ALTER TABLE wp_posts CHANGE post_content post_content LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

This approach helps avoid data corruption that can occur when modifying encoding directly.

5. Using mysqldump for Large Databases

For large databases, the best approach is to use mysqldump to export data in its original format and then reimport it with the new encoding:

mysqldump -u user -p --default-character-set=latin1 database_name > backup.sql

Then, reimport it using UTF-8:

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

This method ensures that the data is correctly read before applying the conversion.

6. Updating wp-config.php

Once the conversion is complete, verify that wp-config.php contains the following configuration:

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

If the site includes special characters or languages with complex symbols, UTF-8MB4 is the recommended option.

7. Testing and Verifying the Conversion

To confirm that the conversion was successful, perform the following checks:

1. Review data in phpMyAdmin to ensure special characters display correctly.
2. Publish a test post containing accented characters, symbols, and text in different languages to verify correct rendering.
3. Run SQL queries to check if characters are stored properly:

SELECT post_content FROM wp_posts WHERE post_content LIKE '%áéíóúñ%';

If characters appear incorrectly in the database, the conversion may not have been applied correctly or plugins might be interfering with encoding.

8. Final Considerations

• Always create a backup before modifying the database.
• Work in a staging environment before applying changes to a live site.
• If issues persist, consider updating MySQL and PHP to the latest versions.

Conclusion

Converting a WordPress database to UTF-8 is a crucial step to ensure multilingual compatibility and prevent encoding errors. Following these steps allows for a safe and effective conversion, ensuring that the website functions correctly without data loss.