converting latin1_swedish_ci "text" column data to utf8_bin

[7 reeds]

Hi,

I have seen several discussions on-line about converting existing databases and tables from latin1_* to utf8 using ALTER TABLE commands. I can't do that.

The MySQL instance I have inherited has the default latin1_swedish_ci encoding. All the existing databases and tables are in that encoding.

I am in the process of making major modifications to some of the existing apps and want to move to utf8 encoding. I have created the tables with the new encoding and I want to copy the old data from the old latin1 tables into the new tables. i will then setup an AFTER TRIGGER on the old tables to push any new items that come in to the new tables.

The question is how do I do the conversion "on the fly"? I know about the CONVERT() and CAST() functions.

I am mainly having issues with an old "TEXT" column that is latin1_swedish_ci. I am doing the following:

SELECT * FROM the old table

FOREACH row
    INSERT INTO new table ...
    VALUES (..., CONVERT(old_text_col USING utf8), ...)
END FOR

but that column in the new table always reports the column contents as a BLOB.

I do not see a charset option in DBI. What am I missing?