inserting latin1-encoded text into utf8 tables (forgot to use mysql_set_charset)

Question

I have a PHP web app with MySQL tables taking utf8 text. I recently converted the data from latin1 to utf8 along with the tables and columns accordingly. I did, however, forget to use mysql_set_charset and the latest incoming data I would assume came through the MySQL connection as latin1. I don't know what happens when latin1 comes in to a utf8 column, but it's causing some strange display issues for items like comma, quotes, ampersand, etc.

Now that mysql_set_charset is in place, it is pulling the data out with funky characters. Any way to convert the latin1-utf8 soup to straight utf8 now that i have the database connection resource using the correct charset?

Answer 1

Found the fix with your comment. Here was the SQL line that seemingly has solved my issue.

UPDATE table SET col = CONVERT(CONVERT(CONVERT(col USING latin1) USING binary) using utf8);

Even though the column is UTF8, it forces it to pull the data out as latin1, convert to binary, convert to utf8 and re-insert.