Yes, normally convert from latin1 to binary, and from binary to utf8 (which would also be the recipe to convert actual utf8 data which accidentally ended up in latin1 columns to the proper definition without changing the content). I would not know why that would take overly long for you.
An alternative if you have the disk space, and the table has no triggers, is using a tool like pt-online-schema change to avoid locking during the change (it creates a shadow table with the proper data and renames the tables once it is done). I am looking for a way to convert about 40GB of InnoDB tables from latin1 > character set to utf8. As true conversion will take ages, I had the idea of > just changing the character sets (and preferably collation, too) of the > tables without actually converting the data. Conversion could be done > manually later. From my side it is ok that the data is wrongly encoded in > the tables for the time of manual conversion. The goal is to have the > tables up and running as soon as possible. > > I tried to set the character sets with CHANGE, MODIFY, ALTER, but mysql > always seems to convert the tables’ data. I read that character sets will > be ignored if you convert to binary character sets, but still changing to > this set takes ages. > > So my question is: Is there a way of changing an InnoDB table's character > set and collation without letting mysql converting the data? What is the > fastest way of changing the character sets, regardless of the method? > > If converting the data is the only way to go, I thought about converting > several tables at the same time, but still this would require a down time > of several hours, which is basically unacceptable. Is there no faster way > to go? > > Thanks a lot for your ideas! >
