Useful trick: first change the column to BINARY , then to the desired charset, eg if a text column was latin1 and it needs to be utf8 ...
CREATE TABLE tblbak SELECT * FROM t; ALTER TABLE tbl MODIFY COLUMN latin1txtcol CHARACTER SET BINARY; ALTER TABLE tbl MODIFY COLUMN latin1txtcol CHARACTER SET 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 https://medium.com/@jesseproudman/getting-out-of-mysql-character-set-hell-8431e75383db. Last updated 17 Apr 2020 |
![]() |