WordPress: Convert All Tables To Utf8

Wordpress Convert All Tables To Utf8Are your WordPress MySQL Tables of mixed charsets? The ideal or standard character set is utf-8 as it supports multiple languages and special characters – so people from all over the world can post comments, register, and interact with your blog without having characters from their name, languages, etc converted into weird or strange symbols.

I read the WordPress article on Converrting your Tables but wasn’t so sure which direction to go. The article states:

In most cases if a collation is not defined MySQL will assume the default collation for the CHARSET which is specified. For UTF8 the default is utf8_general_ci, which is usually the right choice.

After a little research I got it working.

The following code is from the supplemental code page from that article and I found that the code below (second code block on that page) worked well for WordPress 3.4.1

I would not recommend using the bash script at the bottom of that page because while it converts the tables it doesn’t convert the fields. When connecting from the command line you may need to specify --protocol=TCP if you get an error like

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

But again, I would not really recommend that script, because while it will run, it will basically only run the following commands:

ALTER TABLE wp_commentmeta CHARACTER SET utf8;
ALTER TABLE wp_comments CHARACTER SET utf8;
ALTER TABLE wp_links CHARACTER SET utf8;
ALTER TABLE wp_options CHARACTER SET utf8;
ALTER TABLE wp_postmeta CHARACTER SET utf8;
ALTER TABLE wp_posts CHARACTER SET utf8;
ALTER TABLE wp_term_relationships CHARACTER SET utf8;
ALTER TABLE wp_term_taxonomy CHARACTER SET utf8;
ALTER TABLE wp_terms CHARACTER SET utf8;
ALTER TABLE wp_usermeta CHARACTER SET utf8;
ALTER TABLE wp_users CHARACTER SET utf8;

And the thing that is missing is converting the individual fields as well.

BACK UP YOUR DATABASE before running these SQL statements

SQL To convert a site from latin1 to UTF8

In this case all columns were previously set to latin1 and the data inside them was latin1

SQL To convert a site from latin1 to UTF8

In this case all columns were previously set to latin1 and the data inside them was latin1

post_content

alter table wp_posts change post_content post_content LONGBLOB;
alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET utf8 not null;

post_title

alter table wp_posts change post_title post_title BLOB;
alter table wp_posts change post_title post_title TEXT CHARACTER SET utf8 not null;

post_excerpt

alter table wp_posts change post_excerpt post_excerpt BLOB;
alter table wp_posts change post_excerpt post_excerpt TEXT CHARACTER SET utf8 not null; 

post_status

alter table wp_posts change post_status post_status VARBINARY(20);
alter table wp_posts change post_status post_status VARCHAR(20) character set utf8 not null default 'publish';

wp_posts comment_status

alter table wp_posts change comment_status comment_status VARBINARY(20);
alter table wp_posts change comment_status comment_status VARCHAR(20) character set utf8 not null default 'open';

ping_status

alter table wp_posts change ping_status ping_status VARBINARY(20);
alter table wp_posts change ping_status ping_status VARCHAR(20) character set utf8 not null default 'open';

post_password

alter table wp_posts change post_password post_password VARBINARY(20);
alter table wp_posts change post_password post_password VARCHAR(20) character set utf8 not null default '';

post_name

alter table wp_posts change post_name post_name VARBINARY(200);
alter table wp_posts change post_name post_name VARCHAR(200) character set utf8 not null default '';

wp_posts to_ping

alter table wp_posts change to_ping to_ping BLOB;
alter table wp_posts change to_ping to_ping TEXT CHARACTER SET utf8 not null;

wp_posts pinged

alter table wp_posts change pinged pinged BLOB;
alter table wp_posts change pinged pinged TEXT CHARACTER SET utf8 not null;

post_content_filtered

alter table wp_posts change post_content_filtered post_content_filtered BLOB not null;
alter table wp_posts change post_content_filtered post_content_filtered TEXT CHARACTER SET utf8 not null;

guid

alter table wp\_posts change guid guid VARBINARY(255);
alter table wp_posts change guid guid VARCHAR(255) character set utf8 not null default '';

post_type

alter table wp_posts change post_type post_type VARBINARY(20);
alter table wp_posts change post_type post_type VARCHAR(20) character set utf8 not null default 'post';

post_mime_type

alter table wp_posts change post_mime_type post_mime_type VARBINARY(100);
alter table wp_posts change post_mime_type post_mime_type VARCHAR(100) character set utf8 not null default '';

ALTER TABLE wp_posts CHARACTER SET utf8;

comment_author

alter table wp_comments change comment_author comment_author TINYBLOB;
alter table wp_comments change comment_author comment_author TINYTEXT character set utf8 not null;

comment_author_email

alter table wp_comments change comment_author_email comment_author_email VARBINARY(100);
alter table wp_comments change comment_author_email comment_author_email VARCHAR(100) character set utf8 not null default '';

comment_author_url

alter table wp_comments change comment_author_url comment_author_url VARBINARY(200);
alter table wp_comments change comment_author_url comment_author_url VARCHAR(200) character set utf8 not null default '';

comment_author_ip

alter table wp_comments change comment_author_IP comment_author_IP VARBINARY(100);
alter table wp_comments change comment_author_IP comment_author_IP VARCHAR(100) character set utf8 not null default '';

comment_content

alter table wp_comments change comment_content comment_content BLOB;
alter table wp_comments change comment_content comment_content text character set utf8 not null;

comment_approved

alter table wp_comments change comment_approved comment_approved VARBINARY(20);
alter table wp_comments change comment_approved comment_approved VARCHAR(20) character set utf8 not null default '1';

comment_agent

alter table wp_comments change comment_agent comment_agent VARBINARY(255);
alter table wp_comments change comment_agent comment_agent VARCHAR(255) character set utf8 not null default '';

comment_type

alter table wp_comments change comment_type comment_type VARBINARY(20);
alter table wp_comments change comment_type comment_type VARCHAR(20) character set utf8 not null default '';

ALTER TABLE wp_comments CHARACTER SET utf8;

commentmeta meta_key

alter table wp_commentmeta change meta_key meta_key VARBINARY(255);
alter table wp_commentmeta change meta_key meta_key VARCHAR(255) character set utf8;

commentmeta meta_value

alter table wp_commentmeta change meta_value meta_value LONGBLOB;
alter table wp_commentmeta change meta_value meta_value LONGTEXT character set utf8;

ALTER TABLE wp_commentmeta CHARACTER SET utf8;

link_url

alter table wp_links change link_url link_url VARBINARY(255);
alter table wp_links change link_url link_url VARCHAR(255) character set utf8 not null default '';

link_name

alter table wp_links change link_name link_name VARBINARY(255);
alter table wp_links change link_name link_name VARCHAR(255) character set utf8 not null default '';

link_image

alter table wp_links change link_image link_image VARBINARY(255);
alter table wp_links change link_image link_image VARCHAR(255) character set utf8 not null default '';

link_target

alter table wp_links change link_target link_target VARBINARY(25);
alter table wp_links change link_target link_target VARCHAR(25) character set utf8 not null default '';

link_description

alter table wp_links change link_description link_description VARBINARY(255);
alter table wp_links change link_description link_description VARCHAR(255) character set utf8 not null default '';

link_visible

alter table wp_links change link_visible link_visible VARBINARY(20);
alter table wp_links change link_visible link_visible VARCHAR(20) character set utf8 not null default 'Y'; 

link_rel

alter table wp_links change link_rel link_rel VARBINARY(255);
alter table wp_links change link_rel link_rel VARCHAR(255) character set utf8 not null default '';

link_notes

alter table wp_links change link_notes link_notes MEDIUMBLOB;
alter table wp_links change link_notes link_notes MEDIUMTEXT character set utf8 not null;

link_rss

alter table wp_links change link_rss link_rss VARBINARY(255);
alter table wp_links change link_rss link_rss VARCHAR(255) character set utf8 not null default '';

ALTER TABLE wp_links CHARACTER SET utf8;

option_name

alter table wp_options change option_name option_name VARBINARY(64);
alter table wp_options change option_name option_name VARCHAR(64) character set utf8 not null default '';

option_value

alter table wp_options change option_value option_value LONGBLOB;
alter table wp_options change option_value option_value LONGTEXT character set utf8 not null;

wp_options autoload

alter table wp_options change autoload autoload VARBINARY(20);
alter table wp_options change autoload autoload VARCHAR(20) character set utf8 not null default 'yes';

ALTER TABLE wp_options CHARACTER SET utf8;

wp_postmeta meta_key

alter table wp_postmeta change meta_key meta_key VARBINARY(255);
alter table wp_postmeta change meta_key meta_key VARCHAR(255) character set utf8;

wp_postmeta meta_value

alter table wp_postmeta change meta_value meta_value LONGBLOB;
alter table wp_postmeta change meta_value meta_value LONGTEXT character set utf8;

ALTER TABLE wp_postmeta CHARACTER SET utf8;

wp_terms name

alter table wp_terms change name name VARBINARY(200);
alter table wp_terms change name name VARCHAR(200) character set utf8 not null default '';

wp_terms slug

alter table wp_terms change slug slug VARBINARY(200);
alter table wp_terms change slug slug VARCHAR(200) character set utf8 not null default '';

ALTER TABLE wp_terms CHARACTER SET utf8;

wp_term_taxonomy taxonomy

alter table wp_term_taxonomy change taxonomy taxonomy VARBINARY(32);
alter table wp_term_taxonomy change taxonomy taxonomy VARCHAR(32) character set utf8 not null default '';

wp_term_taxonomy description

alter table wp_term_taxonomy change description description LONGBLOB;
alter table wp_term_taxonomy change description description LONGTEXT character set utf8 not null;

ALTER TABLE wp_term_taxonomy CHARACTER SET utf8;

term_relationships has no encoded content, but switch carset anyway

ALTER TABLE wp_term_relationships CHARACTER SET utf8;

wp_usermeta meta_key

alter table wp_usermeta change meta_key meta_key VARBINARY(255);
alter table wp_usermeta change meta_key meta_key VARCHAR(255) character set utf8;

wp_usermeta meta_value

alter table wp_usermeta change meta_value meta_value LONGBLOB;
alter table wp_usermeta change meta_value meta_value LONGTEXT character set utf8;

ALTER TABLE wp_usermeta CHARACTER SET utf8;

user_login

alter table wp_users change user_login user_login VARBINARY(60);
alter table wp_users change user_login user_login VARCHAR(60) character set utf8 not null default '';

user_pass

alter table wp_users change user_pass user_pass VARBINARY(64);
alter table wp_users change user_pass user_pass VARCHAR(64) character set utf8 not null default '';

user_nicename

alter table wp_users change user_nicename user_nicename VARBINARY(50);
alter table wp_users change user_nicename user_nicename VARCHAR(50) character set utf8 not null default '';

user_email

alter table wp_users change user_email user_email VARBINARY(100);
alter table wp_users change user_email user_email VARCHAR(100) character set utf8 not null default '';

user_url

alter table wp_users change user_url user_url VARBINARY(100);
alter table wp_users change user_url user_url VARCHAR(100) character set utf8 not null default '';

user_activation_key

alter table wp_users change user_activation_key user_activation_key VARBINARY(60);
alter table wp_users change user_activation_key user_activation_key VARCHAR(60) character set utf8 not null default '';

wp_users display_name

alter table wp_users change display_name display_name VARBINARY(250);
alter table wp_users change display_name display_name VARCHAR(250) character set utf8 not null default '';

ALTER TABLE wp_users CHARACTER SET utf8;

Related Posts:

  • No Related Posts
This entry was posted in Server Administration, Web Development and tagged , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *