How to change your Database Character set

Currently the Default character set or Collation for MySQL  is still “latin1_swedish_ci” and it is so damn annoying. The original reason for this is because the creator of MySQL is Swedish, which is the same reason why Microsoft’s’ operating systems default language is English US.

Some hosts may differ, but for the most part lets assume the default is “latin1_swedish_ci”.

So now you might be wondering what the problem is and why we should change it.

Well you see, “latin1_swedish_ci” can present some problems when it comes to using certain characters on your site, namely those outside of the scope of the default collation. Thesis 2 had this exact problem when using this character ( → ). Those whose database was still on the default were met with a blank screen after activating the classic skin.

Now Thesis isn’t the only piece of software to suffer at the hands of outdated ( or perhaps I should say limited ) collation, but it is a perfectly good example of one of the problems it can cause.

Luckily for you, there is a solution

Another Character set that is slowly becoming the standard, it’s not quite there yet, although most people would advise you to use it, is UTF-8 or “utf8_general_ci” as it would be known from the databases perspective.

“utf8_general_ci” is basically a super-set of Latin 1, it contains all the same characters as Latin 1 plus a whole lot more.

In laymen terms you can’t really go wrong with using UTF8 as your character set for both the database and the server in general.

How to change your Character set for your Database

If you are running MySQL, there is a good change you will also be using “phpMyAdmin” which is a friendly user interface for viewing and/or modifying your database.

In Cpanel you have an option under databases that will take you straight to phpMyAdmin.

phpmyadmin in cpanel

Once logged in, you will see your database listed on the left.

Now to save time and arduous writing I have made a video for you so you can see clearly what we need to do.

Check out the video here on my Youtube Channel.

As promised here is the default list of WordPress database tables for you to execute within the SQL command window.

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

Please note that if your table prefix is different from the default “wp_” you will have to change the above list of sql commands before running them.

Meet the Author

Matthew Horne

Matthew Horne is web developer who specializes in optimized development. He also builds custom solutions instead of reverting to plugins. Matthew Has a strong understanding of PHP, JavaScript, jQuery.

0 comments… add one

Leave a Comment