If you began using WordPress prior to version 2.2, you may notice that your database has tables with two different character sets and collations. This is because all database tables created prior to WordPress 2.2 use the latin1 character set and the latin1_swedish_ci collation, and all database tables created after WordPress 2.2 use the utf8 character set and the utf8_general_ci collation. Outside of the obvious visual difference, no hard evidence has ever been submitted to prove that this difference causes any sort of problem. However, numerous random and unrelated issues have apparently been resolved by standardizing the character set and collation across the database. The WordPress Codex provides an article detailing the process of converting database character sets with MySQL commands. If the thought of destroying your entire database simply because you made a typo concerns you, then fear not! There is an easy way to convert your database’s character set using nothing more than phpMyAdmin (available through most hosting providers’ control panel) and a plain text editor.
The following instructions are Mac-specific using the plain text editor Fraise, but they can be easily reinterpreted using any Windows or Linux-based plain text editor.
- Backup your WordPress database. (Never alter the database without a fresh backup on hand.)
- Download and install Fraise.
- Export your WordPress database with phpMyAdmin.
- Open the .sql file in Fraise.
- Select all of the text and select “Unicode (UTF-8)” from the Text/Reload Text With Encoding menu.
- Navigate to Edit/Find/Advanced Find & Replace, then find and replace all occurrences of “latin1” with “utf8”.
- Save the file and quit Fraise.
- Return to your WordPress database via phpMyAdmin.
- Check all of the tables and choose “Drop” from the pull-down menu.
- Navigate to the Operations tab, choose “utf8_general_ci” from the pull-down menu under the Collations section and click the Go button.
- Navigate to the Import tab, choose your edited .sql file, select “utf8” from the character set pull-down menu, and click the Go button.
Within minutes (or seconds), your database will be re-populated with all of the original tables, now featuring the utf8 character set and the utf8_general_ci collation.
2 responses
Awesome instructions, James. I was in need of a clean up, and this definitely helped! Thanks so much!
J.
Thanks, Jim! I’m glad that I could help with your cleanup project!