WordPress.org
WordPress.org
Get WordPress

Codex

Interested in functions, hooks, classes, or methods? Check out the new WordPress Code Reference!

User:JeremyClarke/exampleSQLForUTF8Conversion

This page is an example SQL script for Converting Database Character Sets. It exists to be linked from that article.

This should not be copied wholesale and used to convert a site, at least not without careful testing. If you are trying to convert a site you should read the article linked above in full before using this script.

It was written for a WP 2.9.x site, and will likely need modification before being useful on the current version of WordPress. It should be considered an example, not a tool. 


####
## SQL To convert a site from latin1 to UTF8
## In this case all columns were already set to UTF8, but the data inside them was latin1 
## If you have proper latin1 text in a latin1 database then you should only need to use
## the last two commands for each column, and can erase the initial re-casting as latin1 
####

# post_content			
alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET latin1;
alter table wp_posts change post_content post_content LONGBLOB;
alter table wp_posts change post_content post_content LONGTEXT CHARACTER SET utf8;
# post_title
alter table wp_posts change post_title post_title TEXT CHARACTER SET latin1;
alter table wp_posts change post_title post_title BLOB;
alter table wp_posts change post_title post_title TEXT CHARACTER SET utf8;
# post_excerpt
alter table wp_posts change post_excerpt post_excerpt TEXT CHARACTER SET latin1;
alter table wp_posts change post_excerpt post_excerpt BLOB;
alter table wp_posts change post_excerpt post_excerpt TEXT CHARACTER SET utf8;	
# post_status
alter table wp_posts change post_status post_status VARCHAR(20) character set latin1;
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;
# wp_posts comment_status
alter table wp_posts change comment_status comment_status VARCHAR(20) character set latin1;
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;
# ping_status
alter table wp_posts change ping_status ping_status VARCHAR(20) character set latin1;
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;
# post_password
alter table wp_posts change post_password post_password VARCHAR(20) character set latin1;
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;
# post_name
alter table wp_posts change post_name post_name VARCHAR(200) character set latin1;
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;
# wp_posts to_ping
alter table wp_posts change to_ping to_ping TEXT CHARACTER SET latin1;
alter table wp_posts change to_ping to_ping BLOB;
alter table wp_posts change to_ping to_ping TEXT CHARACTER SET utf8;
# wp_posts pinged
alter table wp_posts change pinged pinged TEXT CHARACTER SET latin1;
alter table wp_posts change pinged pinged BLOB;
alter table wp_posts change pinged pinged TEXT CHARACTER SET utf8;
# post_content_filtered
alter table wp_posts change post_content_filtered post_content_filtered TEXT CHARACTER SET latin1;
alter table wp_posts change post_content_filtered post_content_filtered BLOB;
alter table wp_posts change post_content_filtered post_content_filtered TEXT CHARACTER SET utf8;
# guid
alter table wp_posts change guid guid VARCHAR(255) character set latin1;
alter table wp_posts change guid guid VARBINARY(255);
alter table wp_posts change guid guid VARCHAR(255) character set utf8;
# post_type
alter table wp_posts change post_type post_type VARCHAR(20) character set latin1;
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;
# post_mime_type
alter table wp_posts change post_mime_type post_mime_type VARCHAR(100) character set latin1;
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;

ALTER TABLE wp_posts CHARACTER SET utf8;


# comment_author
alter table wp_comments change comment_author comment_author TINYTEXT character set latin1;
alter table wp_comments change comment_author comment_author TINYBLOB;
alter table wp_comments change comment_author comment_author TINYTEXT character set utf8;
# comment_author_email
alter table wp_comments change comment_author_email comment_author_email VARCHAR(100) character set latin1;
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;
# comment_author_url
alter table wp_comments change comment_author_url comment_author_url VARCHAR(200) character set latin1;
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;
# comment_author_ip
alter table wp_comments change comment_author_ip comment_author_ip VARCHAR(100) character set latin1;
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;
# comment_content
alter table wp_comments change comment_content comment_content text character set latin1;
alter table wp_comments change comment_content comment_content BLOB;
alter table wp_comments change comment_content comment_content text character set utf8;
# comment_approved
alter table wp_comments change comment_approved comment_approved VARCHAR(20) character set latin1;
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;
# comment_agent
alter table wp_comments change comment_agent comment_agent VARCHAR(255) character set latin1;
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;
# comment_type
alter table wp_comments change comment_type comment_type VARCHAR(20) character set latin1;
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;
# comment_subscribe 
alter table wp_comments change comment_subscribe comment_subscribe ENUM('Y','N') CHARACTER SET utf8 not null default 'N'; 

ALTER TABLE wp_comments CHARACTER SET utf8;

# commentmeta meta_key
alter table wp_commentmeta change meta_key meta_key VARCHAR(255) character set latin1;
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 LONGTEXT character set latin1;
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 VARCHAR(255) character set latin1;
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;
# link_name
alter table wp_links change link_name link_name VARCHAR(255) character set latin1;
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;
# link_image
alter table wp_links change link_image link_image VARCHAR(255) character set latin1;
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;
# link_target
alter table wp_links change link_target link_target VARCHAR(25) character set latin1;
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;
# link_description
alter table wp_links change link_description link_description VARCHAR(255) character set latin1;
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;
# link_visible
alter table wp_links change link_visible link_visible VARCHAR(20) character set latin1;
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 VARCHAR(255) character set latin1;
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;
# link_notes
alter table wp_links change link_notes link_notes MEDIUMTEXT character set latin1;
alter table wp_links change link_notes link_notes MEDIUMBLOB;
alter table wp_links change link_notes link_notes MEDIUMTEXT character set utf8;
# link_rss
alter table wp_links change link_rss link_rss VARCHAR(255) character set latin1;
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;

ALTER TABLE wp_links CHARACTER SET utf8;

# option_name
alter table wp_options change option_name option_name VARCHAR(64) character set latin1;
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;
# option_value
alter table wp_options change option_value option_value LONGTEXT character set latin1;
alter table wp_options change option_value option_value LONGBLOB;
alter table wp_options change option_value option_value LONGTEXT character set utf8;
# wp_options autoload
alter table wp_options change autoload autoload VARCHAR(20) character set latin1;
alter table wp_options change autoload autoload VARBINARY(20);
alter table wp_options change autoload autoload VARCHAR(20) character set utf8;

ALTER TABLE wp_options CHARACTER SET utf8;

# wp_postmeta meta_key
alter table wp_postmeta change meta_key meta_key VARCHAR(255) character set latin1;
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 LONGTEXT character set latin1;
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 VARCHAR(200) character set latin1;
alter table wp_terms change name name VARBINARY(200);
alter table wp_terms change name name VARCHAR(200) character set utf8;
# wp_terms slug
alter table wp_terms change slug slug VARCHAR(200) character set latin1;
alter table wp_terms change slug slug VARBINARY(200);
alter table wp_terms change slug slug VARCHAR(200) character set utf8;

ALTER TABLE wp_terms CHARACTER SET utf8;

# wp_term_taxonomy taxonomy
alter table wp_term_taxonomy change taxonomy taxonomy VARCHAR(32) character set latin1;
alter table wp_term_taxonomy change taxonomy taxonomy VARBINARY(32);
alter table wp_term_taxonomy change taxonomy taxonomy VARCHAR(32) character set utf8;
# wp_term_taxonomy description
alter table wp_term_taxonomy change description description LONGTEXT character set latin1;
alter table wp_term_taxonomy change description description LONGBLOB;
alter table wp_term_taxonomy change description description LONGTEXT character set utf8;

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 VARCHAR(255) character set latin1;
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 LONGTEXT character set latin1;
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 VARCHAR(60) character set latin1;
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;
# user_pass
alter table wp_users change user_pass user_pass VARCHAR(64) character set latin1;
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;
# user_nicename
alter table wp_users change user_nicename user_nicename VARCHAR(50) character set latin1;
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;
# user_email
alter table wp_users change user_email user_email VARCHAR(100) character set latin1;
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;
# user_url
alter table wp_users change user_url user_url VARCHAR(100) character set latin1;
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;
# user_activation_key
alter table wp_users change user_activation_key user_activation_key VARCHAR(60) character set latin1;
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;
# wp_users display_name
alter table wp_users change display_name display_name VARCHAR(250) character set latin1;
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;

ALTER TABLE wp_users CHARACTER SET utf8;

User:egalojames : The above pretty much worked for me in WordPress 3.3.1, but a few modifications were necessary. It appears the WordPress schema has changed slightly in a few places since this was written. There was no comment_subscribe column in my wp_comments table. Furthermore, most of the columns seem to have changed from allowing nulls to not allowing nulls, and acquired new defaults. So, to avoid changing the columns' definitions I had to add things like "not null default 'publish'", on the end of the final (third) alter table line for each column, after "character set utf8". Of course, this has to be done for each column individually, depending whether it's nullable and what the default is in the current schema. Once I'd done all that, it worked perfectly with no schema change side effects.

Here is my updated version of the script tested with WordPress 3.3.1, but it's the 2-line variant not the 3-line variant because my existing data was all 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;

User:christianebuddy : A pretty handy way is using a Shell script in Linux. Here is the function to convert all tables in a database. You have to define USER, PASSWORD and DB yourself in the script. 

#!/bin/bash
USER=user
PASSWORD=password
DB=db

doAllTables() {
# get the table names
TABLENAMES=`mysql -u $USER -p$PASSWORD -D $DB -e "SHOW TABLES\G;"|grep 'Tables_in_'|sed -n 's/.*Tables_in_.*: \([_0-9A-Za-z]*\).*/\1/p'`
# loop through the tables and convert them
for TABLENAME in $TABLENAMES
do
mysql -u $USER -p$PASSWORD -D $DB -e "ALTER TABLE $TABLENAME CHARACTER SET utf8;"
done
}

doAllTables

And to convert the Database use: 

mysql -u $USER -p$PASSWORD -D $DB -e "ALTER DATABASE $DB CHARACTER SET utf8;"

The above Linux script didn't seem to do anything for me, I even tried putting in test code and it seems to be doing "something" but it didn't convert the database. Instead I used http://wordpress.org/extend/plugins/utf-8-database-converter/ which worked with my WordPress 3.1.4 blog.

Retrieved from "https://codex.wordpress.org/index.php?title=User:JeremyClarke/exampleSQLForUTF8Conversion&oldid=115646"

Codex Resources