Mojibuster

Fix character encoding in MySQL and phpMyAdmin

phpMyAdmin, your SQL dump or your application shows é or ? instead of é? Usually the table, the connection and the import don’t match. Mojibuster repairs exports right in your browser – then you set up MySQL correctly for good.

Works with text and these files:

  • CSV
  • TXT
  • JSON
  • XML
  • SRT
  • VTT
  • TSV
  • MD
  • HTML
  • SQL
  • LOG

For example Excel exports, database dumps, subtitles or website code.

Repair

  1. Drop a file or paste text
  2. It gets repaired automatically
  3. Copy or download
or simply drag a file here

Three places where MySQL needs an encoding

  • Column or table: the character set the data is stored in, such as utf8mb4 or latin1.
  • Connection: the character set client and server talk in – set with SET NAMES utf8mb4 or in the connection settings.
  • File: the encoding used to read an import or write an export – in phpMyAdmin under Character set of the file.

If the three don’t match, characters break. The most common case: UTF-8 text was written over a latin1 connection into a latin1 column. The application looks fine, but phpMyAdmin and the export show é.

A row from a phpMyAdmin CSV export

Before

"1","José Muñoz","Rue de l’Église 12","Genève"

After

"1","José Muñoz","Rue de l’Église 12","Genève"

Check the character sets

  • SHOW VARIABLES LIKE 'character_set%'; shows the server and connection settings.
  • SHOW CREATE TABLE customers; shows the character set of the table and its columns.
  • SELECT name, HEX(name) FROM customers LIMIT 5; shows the real bytes: in a utf8mb4 column, C3A9 is an é, while C383C2A9 is an already broken é.

Repair an export or dump

  1. Export the table in phpMyAdmin as SQL or CSV – or with mysqldump --default-character-set=utf8mb4.
  2. Drop the file into the box above. Mojibuster repairs only the broken spots; correct characters stay unchanged.
  3. Check the repaired spots under What changed? and download the file.
  4. Import it into a table with utf8mb4 and choose utf-8 as the character set in the import dialog.
  5. Test on a copy first before replacing the real table.

The export never leaves your computer – which matters for customer data. If you use WordPress, mind the notes on serialized data on the WordPress & database page.

Fix the cause

  • Create new tables with DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci.
  • Set your application’s connection to utf8mb4, in PHP for example with charset=utf8mb4 in the PDO DSN.
  • If UTF-8 bytes sit in a latin1 column, don’t convert it directly with CONVERT TO – that encodes them a second time and creates double-encoded text. The safe way goes through a binary type: first MODIFY name VARBINARY(255), then MODIFY name VARCHAR(255) CHARACTER SET utf8mb4.
  • If MySQL replaces characters with ?, the column or connection can’t represent them. What’s already stored that way is lost – more on question marks instead of characters.

Frequently asked questions

Why does everything look right in my application but not in phpMyAdmin?

Because application and database make the same mistake twice: the application writes UTF-8 over a latin1 connection and reads it back the same way. phpMyAdmin connects correctly and therefore shows what is really stored.

Which collation should I use?

utf8mb4_unicode_ci suits most applications; since MySQL 8, utf8mb4_0900_ai_ci is the default. The character set utf8mb4 matters more – not utf8, which can’t store emojis in MySQL.

Does this work with MariaDB too?

Yes. MariaDB handles character sets like MySQL, and the commands are the same.

How large can the dump be?

Up to 100 MB per file. Export larger databases table by table.

More guides