I’m working on changing my database encoding for text columns from Latin1 to UTF-8. My database is pretty old and was set up before we thought about supporting international characters.
At first, I tried exporting the whole database, editing the schema files to change column definitions, then importing everything back. This approach worked fine on a test copy but took over 2 hours to complete. The main slowdown was writing and reading all those dump files.
I’m worried this won’t work for our live production database since it’s much bigger and we only have a short maintenance window each week when the site can be offline.
Then I thought about something - our website only has English content right now. All the text data is basically ASCII characters with no special symbols or accents. Since ASCII characters are the same in both Latin1 and UTF-8, would it be safe to just use ALTER TABLE commands to change the column encodings directly? Or are there hidden problems with this approach that would force me to go back to the dump and restore method?
ALTER TABLE works fine for ASCII-only data, but heads up on a few things. MySQL still runs character set conversion checks even when your data doesn’t actually change - this eats up time on big tables. I’ve done similar migrations and smaller tables were no problem, but tables with millions of rows still caused long locks. If you’re on MySQL 5.7+, try ALGORITHM=INPLACE to cut down downtime. Test your biggest table first during off-peak hours so you’ll know how long each ALTER will actually take in production.
totally agree! if it’s all ascii, using ALTER TABLE is a go. i’ve done it a few times with no probs. just make sure to back it up 1st and maybe try it on a lil part of the data to be safe.
Went through this same thing two years ago migrating our legacy app. ALTER TABLE works great for ASCII data, but here’s what bit me - check your connection strings and config files after. The data converts fine, but older apps can still throw encoding errors if they’re expecting Latin1. Had to update several PHP connection parameters to handle UTF-8 properly. One more thing - storage usage goes up a bit since UTF-8 uses variable-length encoding. Wasn’t much in my case, but worth knowing if you’re tight on space.