Fixing broken UTF-8 encoding

If you have double-encoded UTF8 characters (various smart quotes, dashes, apostrophe ’, quotation mark “, etc), in mysql you can dump the data, then read it back in to fix the broken encoding.

Like this:

mysqldump -h DB_HOST -u DB_USER -p DB_PASSWORD --opt --quote-names \
    --skip-set-charset --default-character-set=latin1 DB_NAME > DB_NAME-dump.sql

mysql -h DB_HOST -u DB_USER -p DB_PASSWORD \
    --default-character-set=utf8 DB_NAME < DB_NAME-dump.sql

This was a 100% fix for my double encoded UTF-8.

Source: http://blog.hno3.org/2010/04/22/fixing-double-encoded-utf-8-data-in-mysql/

As Dan pointed out: you need to convert them to binary and then convert/correct the encoding.

E.g., for utf8 stored as latin1 the following SQL will fix it:

UPDATE table
   SET field = CONVERT( CAST(field AS BINARY) USING utf8)
 WHERE $broken_field_condition

$bad_string = "Luis Pérez Casas, del Collettivo di avvocati “José Alvear Restrepoâ€, Colombia, un’organizzazione soggetta a costanti minacce";

$good_string = fix_broken_chars($bad_string);

echo $good_string;

function fix_broken_chars($garbled_utf8_string)
{   
    $conv_table = unserialize('a:5:{i:0;a:3:{s:8:"’";s:3:"’";s:8:"–";s:3:"–";s:8:"—";s:3:"—";}i:1;a:12:{s:7:"€";s:3:"€";s:7:"‚";s:3:"‚";s:7:"„";s:3:"„";s:7:"…";s:3:"…";s:7:"‡";s:3:"‡";s:7:"‰";s:3:"‰";s:7:"‹";s:3:"‹";s:7:"‘";s:3:"‘";s:7:"“";s:3:"“";s:7:"•";s:3:"•";s:7:"â„¢";s:3:"™";s:7:"›";s:3:"›";}i:2;a:22:{s:5:"À";s:2:"À";s:5:"Â";s:2:"Â";s:5:"Æ’";s:2:"ƒ";s:5:"Ä";s:2:"Ä";s:5:"Ã…";s:2:"Å";s:5:"â€";s:3:"”";s:5:"Æ";s:2:"Æ";s:5:"Ç";s:2:"Ç";s:5:"ˆ";s:2:"ˆ";s:5:"É";s:2:"É";s:5:"Ë";s:2:"Ë";s:5:"Å’";s:2:"Œ";s:5:"Ñ";s:2:"Ñ";s:5:"Ã’";s:2:"Ò";s:5:"Ó";s:2:"Ó";s:5:"Ô";s:2:"Ô";s:5:"Õ";s:2:"Õ";s:5:"Ö";s:2:"Ö";s:5:"×";s:2:"×";s:5:"Ù";s:2:"Ù";s:5:"Û";s:2:"Û";s:5:"Å“";s:2:"œ";}i:3;a:77:{s:4:"Ã";s:2:"Ã";s:4:"È";s:2:"È";s:4:"Ê";s:2:"Ê";s:4:"ÃŒ";s:2:"Ì";s:4:"Ž";s:2:"Ž";s:4:"ÃŽ";s:2:"Î";s:4:"Ëœ";s:2:"˜";s:4:"Ø";s:2:"Ø";s:4:"Å¡";s:2:"š";s:4:"Ú";s:2:"Ú";s:4:"Ãœ";s:2:"Ü";s:4:"ž";s:2:"ž";s:4:"Þ";s:2:"Þ";s:4:"Ÿ";s:2:"Ÿ";s:4:"ß";s:2:"ß";s:4:"¡";s:2:"¡";s:4:"á";s:2:"á";s:4:"¢";s:2:"¢";s:4:"â";s:2:"â";s:4:"£";s:2:"£";s:4:"ã";s:2:"ã";s:4:"¤";s:2:"¤";s:4:"ä";s:2:"ä";s:4:"Â¥";s:2:"¥";s:4:"Ã¥";s:2:"å";s:4:"¦";s:2:"¦";s:4:"æ";s:2:"æ";s:4:"§";s:2:"§";s:4:"ç";s:2:"ç";s:4:"¨";s:2:"¨";s:4:"è";s:2:"è";s:4:"©";s:2:"©";s:4:"é";s:2:"é";s:4:"ª";s:2:"ª";s:4:"ê";s:2:"ê";s:4:"«";s:2:"«";s:4:"ë";s:2:"ë";s:4:"¬";s:2:"¬";s:4:"ì";s:2:"ì";s:4:"­";s:2:"­";s:4:"í";s:2:"í";s:4:"®";s:2:"®";s:4:"î";s:2:"î";s:4:"¯";s:2:"¯";s:4:"ï";s:2:"ï";s:4:"°";s:2:"°";s:4:"ð";s:2:"ð";s:4:"±";s:2:"±";s:4:"ñ";s:2:"ñ";s:4:"²";s:2:"²";s:4:"ò";s:2:"ò";s:4:"³";s:2:"³";s:4:"ó";s:2:"ó";s:4:"´";s:2:"´";s:4:"ô";s:2:"ô";s:4:"µ";s:2:"µ";s:4:"õ";s:2:"õ";s:4:"¶";s:2:"¶";s:4:"ö";s:2:"ö";s:4:"·";s:2:"·";s:4:"÷";s:2:"÷";s:4:"¸";s:2:"¸";s:4:"ø";s:2:"ø";s:4:"¹";s:2:"¹";s:4:"ù";s:2:"ù";s:4:"º";s:2:"º";s:4:"ú";s:2:"ú";s:4:"»";s:2:"»";s:4:"û";s:2:"û";s:4:"¼";s:2:"¼";s:4:"ü";s:2:"ü";s:4:"½";s:2:"½";s:4:"ý";s:2:"ý";s:4:"¾";s:2:"¾";s:4:"þ";s:2:"þ";s:4:"¿";s:2:"¿";s:4:"ÿ";s:2:"ÿ";}i:4;a:1:{s:2:"Ã";s:2:"à";}}');

    foreach ($conv_table as $convert) {
        $garbled_utf8_string = str_replace(array_keys($convert), $convert, $garbled_utf8_string);    
    }

    return $garbled_utf8_string;
}

Implements this table http://www.i18nqa.com/debug/utf8-debug.html

In my case, I found out by using "mb_convert_encoding" that the previous encoding was iso-8859-1 (which is latin1) then I fixed my problem by using an sql query :

UPDATE myDB.myTable SET myColumn = CAST(CAST(CONVERT(myColumn USING latin1) AS binary) AS CHAR)

However, it is stated in the mysql documentations that conversion may be lossy if the column contains characters that are not in both character sets.