Skip to content

Instantly share code, notes, and snippets.

@Dimasmagadan
Forked from ericrasch/database encoding fix.sql
Last active December 31, 2017 12:01
Show Gist options
  • Save Dimasmagadan/28e1b184dd9724d340858fdef8d8cdf8 to your computer and use it in GitHub Desktop.
Save Dimasmagadan/28e1b184dd9724d340858fdef8d8cdf8 to your computer and use it in GitHub Desktop.
Fix #WordPress database encoding issues with these #MySQL queries...source #1: http://tanyanam.com/technology/fixing-encoding-issues-on-wordpress-comments-display-as-question-markssource #2: http://stackoverflow.com/a/3568144/284091The 3 groups of repeated queries include the post content, post title, and the post slug.
ALTER DATABASE 'my_wp_database' CHARACTER SET utf8;
ALTER TABLE 'wp_comments' CHARACTER SET utf8;
alter table 'wp_comments' change comment_content comment_content LONGTEXT CHARACTER SET utf8;
alter table 'wp_comments' change comment_author comment_author LONGTEXT CHARACTER SET utf8;
update `wp_posts` set `post_content` = replace(`post_content` ,'á','á');
update `wp_posts` set `post_content` = replace(`post_content` ,'é','é');
update `wp_posts` set `post_content` = replace(`post_content` ,'í©','é');
update `wp_posts` set `post_content` = replace(`post_content` ,'ó','ó');
update `wp_posts` set `post_content` = replace(`post_content` ,'íº','ú');
update `wp_posts` set `post_content` = replace(`post_content` ,'ú','ú');
update `wp_posts` set `post_content` = replace(`post_content` ,'ñ','ñ');
update `wp_posts` set `post_content` = replace(`post_content` ,'í‘','Ñ');
update `wp_posts` set `post_content` = replace(`post_content` ,'Ã','í');
update `wp_posts` set `post_content` = replace(`post_content` ,'–','–');
update `wp_posts` set `post_content` = replace(`post_content` ,'—','–');
update `wp_posts` set `post_content` = replace(`post_content` ,'’','\'');
update `wp_posts` set `post_content` = replace(`post_content` ,'…','...');
update `wp_posts` set `post_content` = replace(`post_content` ,'–','-');
update `wp_posts` set `post_content` = replace(`post_content` ,'“','"');
update `wp_posts` set `post_content` = replace(`post_content` ,'â€','"');
update `wp_posts` set `post_content` = replace(`post_content` ,'‘','\'');
update `wp_posts` set `post_content` = replace(`post_content` ,'•','-');
update `wp_posts` set `post_content` = replace(`post_content` ,'‡','c');
update `wp_posts` set `post_content` = replace(`post_content` ,'Â','');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'á','á');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'é','é');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'í©','é');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'ó','ó');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'íº','ú');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'ú','ú');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'ñ','ñ');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'í‘','Ñ');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'Ã','í');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'–','–');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'—','–');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'’','\'');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'…','...');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'–','-');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'“','"');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'â€','"');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'‘','\'');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'•','-');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'‡','c');
update `wp_posts` set `post_excerpt` = replace(`post_excerpt` ,'Â','');
update `wp_posts` set `post_title` = replace(`post_title` ,'á','á');
update `wp_posts` set `post_title` = replace(`post_title` ,'é','é');
update `wp_posts` set `post_title` = replace(`post_title` ,'í©','é');
update `wp_posts` set `post_title` = replace(`post_title` ,'ó','ó');
update `wp_posts` set `post_title` = replace(`post_title` ,'íº','ú');
update `wp_posts` set `post_title` = replace(`post_title` ,'ú','ú');
update `wp_posts` set `post_title` = replace(`post_title` ,'ñ','ñ');
update `wp_posts` set `post_title` = replace(`post_title` ,'í‘','Ñ');
update `wp_posts` set `post_title` = replace(`post_title` ,'Ã','í');
update `wp_posts` set `post_title` = replace(`post_title` ,'–','–');
update `wp_posts` set `post_title` = replace(`post_title` ,'—','–');
update `wp_posts` set `post_title` = replace(`post_title` ,'’','\'');
update `wp_posts` set `post_title` = replace(`post_title` ,'…','...');
update `wp_posts` set `post_title` = replace(`post_title` ,'–','-');
update `wp_posts` set `post_title` = replace(`post_title` ,'“','"');
update `wp_posts` set `post_title` = replace(`post_title` ,'â€','"');
update `wp_posts` set `post_title` = replace(`post_title` ,'‘','\'');
update `wp_posts` set `post_title` = replace(`post_title` ,'•','-');
update `wp_posts` set `post_title` = replace(`post_title` ,'‡','c');
update `wp_posts` set `post_title` = replace(`post_title` ,'Â','');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'á','á');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'é','é');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'í©','é');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'ó','ó');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'íº','ú');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'ú','ú');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'ñ','ñ');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'í‘','Ñ');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'Ã','í');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'–','–');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'—','–');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'’','\'');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'…','...');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'–','-');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'“','"');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'â€','"');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'‘','\'');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'•','-');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'‡','c');
update `wp_postmeta` set `meta_value` = replace(`meta_value` ,'Â','');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment