May 302009
This plugin is based on the post “Convert MySQL Tables to UTF-8” and an existing plugin by g30rg3_x. The reason I modified their code is that they will convert all tables in your database to the UTF-8 charset, but what we need is to convert WP tables, so I changed the code
"SHOW TABLES" to "SHOW TABLES LIKE " . $table_prefix . "%", which will guarantee other tables could stay untouched. Besides, g30rg3_x’s purpose was to alter the charset of old WP databases to new UTF-8 databases, but in fact we also need to change the charset after we moved our DB to a new host when the charset is not UTF-8 by default. Judging from my experience, the default charset/collation for many web hosts is latin1/latin1_swedish_ci (I don’t know why), whereas popular web-buidling systems often use utf8/utf8_general_ci, thus we need to change the charset before all content could be normally displayed. Without PHP and SHOW TALBES / SHOW COLUMNS, we will need to write endless code to change all tables and all columns.
mysql> select collation('asdf'); # default collation
+-------------------+
| collation('asdf') |
+-------------------+
| latin1_swedish_ci |
+-------------------+
1 row in set (0.00 sec)
Download the UTF-8 DB Converter for WordPress
The critical part of this plugin is:
....
$sql2 = "ALTER TABLE $table DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci";
....
$sql4 = "ALTER TABLE $table CHANGE `$field_name` `$field_name` $field_type
CHARACTER SET utf8 COLLATE utf8_bin";
....
I don’t think I need to describe the installation again, but I sould warn you again about possible data lost during the conversion. Do back up early please.
Related Posts
19 Responses to “Convert MySQL Database to UTF-8 in WordPress”
Comments (15) Pingbacks (4)
thank you ..
plugin nice ..
but links i have not Convert to UTF-8 ..
Example:
% d8% a7% d8% aa% d8% b5% d9% 84 -% d8% a8% d9% 86% d8% a7
I want to convert:
اتصل بنا
That’s not a UTF-8 problem. You should use
urldecode()to decode the string to your (Arabian?) characters. For example:<meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <?php echo urldecode("%D8%A7%D8%AA%D8%B5%D9%84+%D8%A8%D9%86%D8%A7"); ?>Love the blog mate, keep up the good work – I’ll definitely recommend your blog to some friends of mine ^^
Thank you
but i have 1 question, i have sql file that was using iso-8859-1 for 2 years and then the user change the code to utf-8, will this repair and convert iso-8859-1 to utf8 ?
Worked perfectly. You just saved me a lot of time! Thanks so much!
Derek
thank you very much for the plugin!
Thank you so much for this plugin. I just applied it to my installation, and it worked perfectly with no troubles.
Dude, you rock! I’ve been trying to convert my db, which was a mess of latin and utf8. I found the previous WP plug-in and it butchered my posts, clipping them after any non-latin character (I write in English, but use em-dashes like their going out of style).
Your plugin worked without a hitch! You have no idea how grateful I am! Thanks again for fixing the previous version
Glad that it helps.
By the way, your web design looks really beautiful!
Yep. It helped a ton dude!
And thanks for the compliment, and the lovely plugin
Thank you! Plugin works great, and I can finally use UTF8 on my website
Thanks for the great plugin. I’m getting an error. Do you have any idea what this means?
wp_bdprss_items changed to UTF-8 successfully.---- item_feed_url changed to UTF-8 successfully.
SQL Error:
Specified key was too long; max key length is 1000 bytes
Sorry, I have no idea…
I first noticed a character set encoding problem ( see “Victim?s parents to speak on bullycase” here: http://carmel-indiana.us/page/6/ ). What was supposed to be an apostrophe was displaying as a question mark instead. I installed and activated the plugin. It indicated that it had fixed the encoding. But I don’t see that the post has been changed. Is the plugin supposed to fix posts that were posted prior to installation of the plugin?