"Convert to UTF8" Without Converting
Posted on 2006-11-06
I have a table which CLAIMS it's using latin1 character encoding, however what's stored in each row is actually UTF-8 data. To clarify: SHOW CREATE TABLE insists that the encoding is latin1, but SELECT HEX(column_name) reveals a UTF-8 sequence.
I want to tell MySQL that this table is really stored as UTF-8. Doing this, however, by any method I've seen described in the manual, causes MySQL to "convert" my data. That is, it takes my UTF-8 string, interprets it as latin1, converts that "latin1" string to UTF-8, and stores some mangled data back in my table.
I need a way to just indicate "Listen, trust me, this is UTF-8; don't do anything about it, just record for posterity's sake that I'm using a different encoding than you think I am."
It took me a while to get my head around all this, so I'll be happy to clarify if it's confusing. I expect, though, that there's a really simple solution I just haven't encountered.
Note that statements like ALTER TABLE t CHANGE COLUMN c c VARCHAR(50) CHARACTER SET utf8; do this undesirable conversion. ALTER TABLE t CHARACTER SET utf8; does nothing but change the default (and leaves the columns marked as latin1). ALTER TABLE t CONVERT TO CHARACTER SET utf8; does the undesirable conversion again.