How can I convert corrupted old data or broken charset in database to UTF-8?

Question

I have a database, but the data in it is not UTF-8 compatible and shows Turkish characters broken. I need a detailed help on how to translate data automatically or manually. I've tried everything, but can't figure out how to replace broken characters with compatible ones.

post subjects looks like this

There are too many symbols that I can't replace.

Reverse the insert, if you haven't lost some bits, then insert the data again but this time use utf8 all the way — nbk, Jul 22 '23 at 19:23
Also see https://stackoverflow.com/questions/279170/utf-8-all-the-way-through — nbk, Jul 22 '23 at 19:32
A photo gives not enough detailed information to give you detailed help. which encoding do you use? — destructioneer, Jul 22 '23 at 19:38
latin1 rn but I want to convert all entire old data to UTF-8 — fugue, Jul 22 '23 at 19:43
That's Mojibake -- see https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored — Rick James, Aug 03 '23 at 18:23

score 0 · Answer 1 · answered Jul 22 '23 at 22:49

You haven't given us much information to work from, but assuming you have been inserting UTF-8 data into latin1 columns over a latin1 connection you can do the following.

Given a posts table like:

CREATE TABLE posts (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL
) DEFAULT CHARSET=latin1;

You can create a new table like:

CREATE TABLE posts_utf8 (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    subject VARCHAR(255) NOT NULL,
    body TEXT NOT NULL
) DEFAULT CHARSET=utf8mb4;

And then populate it with:

INSERT INTO posts_utf8
SELECT id,
    CONVERT(BINARY subject USING utf8mb4),
    CONVERT(BINARY body USING utf8mb4)
FROM posts;

If you are happy with the content of the new table you can then switch them over:

RENAME TABLE posts TO posts_old, posts_utf8 TO posts;

There are several different ways to "fix" bad characters; be sure to test this first. — Rick James, Aug 03 '23 at 18:24

How can I convert corrupted old data or broken charset in database to UTF-8?

1 Answers1