Change column charset

from the Artful MySQL Tips List


Useful trick: first change the column to BINARY, then to the desired charset.

CREATE TABLE tblbak SELECT * FROM t;
ALTER TABLE tbl MODIFY latin1col BINARY;
ALTER TABLE tbl MODIFY latin1col UTF8;

If this elicits "Incorrect string value" errors, and if the problem column is not utf8, try a finer-grained approach:

1. Create a table with the same structure as the original table except that the problem string column is CHARSET utf8.

2. Copy data into the new table using this expression for the problem column:

... CONVERT( CONVERT( colname USING binary ) USING utf8 ) . . .

If neither of these fixes works, you're in MySQL Character Set Hell. You probably got there via charset mismatches between column type and inserted data, followed by recodings that made matters worse. Getting out of this circle of hell is like navigating a maze; see Getting out of MySQL character set hell.



Return to the Artful MySQL Tips page