Correct garbled characters from Latin1 to UTF8

changecharset_latin1-utf8.sql

# Mysql: Convert encoding to UTF8 without garbled data
#1 Convert to blob then UTF8

ALTER TABLE comments MODIFY title BLOB;
ALTER TABLE comments MODIFY title VARCHAR(255) CHARACTER SET utf8;

#2 convert to BINARY then UTF8

-- test
SELECT CONVERT(CAST(CONVERT(title USING latin1) AS BINARY) USING utf8) FROM comments WHERE id = 123;

-- convert
UPDATE comments SET title = CONVERT(cast(CONVERT(title USING latin1) AS BINARY) USING utf8);
ALTER TABLE comments MODIFY title VARCHAR(255) CHARACTER SET utf8;

#3: iconv

mysqldump comments --add-drop-table users | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql some_database

#4: Safest approach without double conversion (PREFERRED METHOD)

UPDATE comments SET title = @txt WHERE char_length(title) =  LENGTH(@txt := CONVERT(BINARY CONVERT(title USING latin1) USING utf8));
ALTER TABLE comments MODIFY title VARCHAR(255) CHARACTER SET utf8;

# Debugging Tip

SELECT HEX(CONVERT(`title` USING latin1)) FROM `comments` WHERE id=123;
SELECT HEX(CONVERT(CAST(CONVERT(title USING latin1) AS BINARY) USING utf8)) FROM comments WHERE id = 123;