Very disappointed by wordpress not providing a simple dropdownlist to change the character set for all table and columns, but instead default to some weird Swedish set and requires the user to spend half an hour to do some machinary work. Saved the script to save someone 30mins.

In my case, the character set I set to is utf8_unicode_ci, please change by search/replace if this is not your target character set.

In the first line(bold area), my database name is abcde_abcde, please change it to yours!

Textfile version here: http://terrywong.co.uk/blog/files/wdp2_71DatabaseScript.rtf

ALTER DATABASE `abcde_abcde` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `wp_comments` CHANGE `comment_author` `comment_author` TINYTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_comments` CHANGE `comment_author_email` `comment_author_email` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_comments` CHANGE `comment_author_url` `comment_author_url` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_comments` CHANGE `comment_author_IP` `comment_author_IP` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_comments` CHANGE `comment_content` `comment_content` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_comments` CHANGE `comment_approved` `comment_approved` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ’1′;
ALTER TABLE `wp_comments` CHANGE `comment_agent` `comment_agent` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_comments` CHANGE `comment_type` `comment_type` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_comments`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `wp_links` CHANGE `link_url` `link_url` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_links` CHANGE `link_name` `link_name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_links` CHANGE `link_image` `link_image` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_links` CHANGE `link_target` `link_target` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_links` CHANGE `link_description` `link_description` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_links` CHANGE `link_visible` `link_visible` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘Y’;
ALTER TABLE `wp_links` CHANGE `link_rel` `link_rel` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_links` CHANGE `link_notes` `link_notes` MEDIUMTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_links` CHANGE `link_rss` `link_rss` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_links`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `wp_options` CHANGE `option_name` `option_name` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_options` CHANGE `option_value` `option_value` LONGTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_options` CHANGE `autoload` `autoload` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘yes’;
ALTER TABLE `wp_options`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `wp_postmeta` CHANGE `meta_key` `meta_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `wp_postmeta` CHANGE `meta_value` `meta_value` LONGTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `wp_postmeta`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `wp_posts` CHANGE `post_content` `post_content` LONGTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_posts` CHANGE `post_title` `post_title` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_posts` CHANGE `post_excerpt` `post_excerpt` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_posts` CHANGE `post_status` `post_status` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘publish’;
ALTER TABLE `wp_posts` CHANGE `comment_status` `comment_status` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘open’;
ALTER TABLE `wp_posts` CHANGE `ping_status` `ping_status` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘open’;
ALTER TABLE `wp_posts` CHANGE `post_password` `post_password` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_posts` CHANGE `post_name` `post_name` VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_posts` CHANGE `to_ping` `to_ping` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_posts` CHANGE `pinged` `pinged` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_posts` CHANGE `post_content_filtered` `post_content_filtered` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_posts` CHANGE `guid` `guid` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_posts` CHANGE `post_type` `post_type` VARCHAR( 20 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT ‘post’;
ALTER TABLE `wp_posts` CHANGE `post_mime_type` `post_mime_type` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_posts`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `wp_terms` CHANGE `name` `name` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_terms` CHANGE `slug` `slug` VARCHAR( 200 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_terms`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `wp_term_relationships`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `wp_term_taxonomy` CHANGE `taxonomy` `taxonomy` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_term_taxonomy` CHANGE `description` `description` LONGTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_term_taxonomy`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `wp_usermeta` CHANGE `meta_key` `meta_key` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `wp_usermeta` CHANGE `meta_value` `meta_value` LONGTEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `wp_usermeta`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

ALTER TABLE `wp_users` CHANGE `user_login` `user_login` VARCHAR( 60 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_users` CHANGE `user_pass` `user_pass` VARCHAR( 64 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_users` CHANGE `user_nicename` `user_nicename` VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_users` CHANGE `user_email` `user_email` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_users` CHANGE `user_url` `user_url` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_users` CHANGE `user_activation_key` `user_activation_key` VARCHAR( 60 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_users` CHANGE `display_name` `display_name` VARCHAR( 250 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
ALTER TABLE `wp_users`  DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Reference: http://boringest.blogasian.com/2008/10/25/how-to-show-chinese-characters-in-wordpress/