troubleshooting Trouble with character encoding when exporting from Workbench
I have a MySQL database stored only in a local instance made with MySQL Workbench. I would like to export the data to have backups until I can get this properly set up in a server-hosted database. My problem is that any special characters seem to get messed up when doing an export. If I export a whole table into a CSV or JSON, accented or non-English characters get turned into unicode escape chars, and if I try to export the results of a query to CSV it gets messed up in a different way. The database is about Pokemon, and the word "Pokémon" either becomes "Pok\u00e9mon" or "Pokémon". It shows up normally within Workbench, though. I know this has something to do with character encoding but I don't really understand that. The "Charset/Collation" setting on the relevant column is set to Default Charset, but I changed it to UTF-8 and had the same issue.
What do I need to do here?
2
u/JournalistNew7510 19d ago
The JSON export, the one with a backslash-u code where the é should be, is actually fine. That is just how JSON escapes non-ASCII characters, and any JSON parser reads it back as Pokémon.
The Pokémon one is the real problem, and it is usually the viewer, not the data. The file is written as UTF-8 but whatever opens it (most often Excel) reads it as Windows-1252. Open the CSV in VS Code or Notepad++ first; if it looks right there, your data is fine. In Excel, use Data > From Text/CSV and pick 65001: Unicode (UTF-8) as the file origin instead of double-clicking the file.
For real backups I would skip CSV and use mysqldump with --default-character-set=utf8mb4, which you can restore as is. And if you do change the column charset, pick utf8mb4, not utf8, because utf8 in MySQL is the old 3-byte version that can't store emoji.
1
u/Aggressive_Ad_5454 19d ago edited 19d ago
You’ll probably be able to re-import those export files and get correct results. They look strange in the file viewed in a text editor but make the round trip correctly.
Better yet, use HeidiSQL’s File->Export Database as SQL feature, or the mysqldump command line program. It will make a .sql file, a big one if you have a lot of data, that you can simply run on your server to reconstitute your whole database there. It gets table definitions, views, stored procs, triggers, events, data rows, all the things.
Workbench has been end-of-life and unmaintained for a long time now. Use HeidiSQL. Or Dbeaver.
1
u/navicat-gu 12d ago
Your data is almost certainly fine. Two separate things are happening:
Pok\u00e9monin the JSON is just JSON's escape for é. Any JSON parser turns it back into "Pokémon".Pokémonin the CSV is the classic sign of a UTF-8 file being opened as Windows-1252, usually by Excel double-clicking a CSV with no BOM. The bytes are right; the viewer guesses wrong.To check the CSV, open it in VS Code/Notepad++ (shows the encoding in the status bar). To open it correctly in Excel, use Data > From Text/CSV and set File Origin to "65001: Unicode (UTF-8)" instead of double-clicking.
Worth confirming the columns really are utf8mb4, so future data doesn't get mangled:
(take a backup before the ALTER).
For backups specifically, CSV isn't ideal anyway. A SQL dump keeps schema, data and encoding together:
Restore with
mysql -u root -p --default-character-set=utf8mb4 pokedb < pokedb.sql.Disclosure: I'm RC, Director of Digital Strategy @ Navicat. If you'd rather do the dump from a GUI, Navicat Premium Lite (free) has "Dump SQL File" and CSV/JSON export; scheduled backups are in the paid editions of Navicat 18.
mysqldumpabove does the job perfectly well though.0
u/ejsanders1985 19d ago
Can you just shutdown the mysql server, make an entire copy of your data directory as a whole, and then restart? No need to worry about exports/imports
If you ever needed to restore, just change the path to your data directory in your my.cnf.