Change column charset
from the Artful MySQL Tips List
Useful trick: first change the column to
, 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
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