Mysql: converta codificação para UTF8 sem dados ilegíveis

É possível que a conversão do conjunto de dados MySQL de uma codificação para outra resulte em dados ilegíveis, por exemplo, ao converter de Latin1 para UTF8. Existem várias abordagens. Todos os exemplos pressupõem que estamos convertendo a title VARCHAR(255)coluna da commentstabela.

# 1 Converta em blob e depois em UTF8

A primeira abordagem é via Mattias e Perconas :

ALTER TABLE comments MODIFY title BLOB;
ALTER TABLE comments MODIFY title VARCHAR(255) CHARACTER SET utf8;

O mesmo link Percona também oferece mais abordagens sobre como fazer isso sem bloquear as tabelas.

# 2 converter para BINARY e depois UTF8

Sidecar tem um bom e simples 1-liner para testar os resultados e convertê-lo e um pequeno script Ruby para automatizar todo o banco de dados. O trecho de teste é particularmente útil. Isso não muda a coluna (não ALTER TABLE), em vez disso, apenas converte os dados no local.

-- test
SELECT CONVERT(CAST(CONVERT(title USING latin1) AS BINARY) USING utf8) FROM comments WHERE id = 123;

-- convert
UPDATE comments SET title = CONVERT(cast(CONVERT(title USING latin1) AS BINARY) USING utf8);
ALTER TABLE comments MODIFY title VARCHAR(255) CHARACTER SET utf8;

# 3: iconv

Outra outra abordagem é usar o iconvcomando para converter a tabela inteira:

mysqldump comments --add-drop-table users | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql some_database

Além disso, a Percona oferece uma ferramenta para converter o conjunto de caracteres para um banco de dados inteiro, conforme discutido em sua postagem de conversão do conjunto de caracteres .

# 4: Abordagem mais segura sem dupla conversão (MÉTODO PREFERIDO)

É possível que você ainda termine com dados truncados ou algo esteja errado. Isso pode acontecer, por exemplo, se você tiver armazenado UTF8dados em uma LATIN1coluna ou em algum outro cenário. Joni Salonen oferece um cheque junto com a atualização.

UPDATE comments SET title = @txt WHERE char_length(title) =  LENGTH(@txt := CONVERT(BINARY CONVERT(title USING latin1) USING utf8));
ALTER TABLE comments MODIFY title VARCHAR(255) CHARACTER SET utf8;

Dica de depuração

Se quiser ir mais fundo, você pode usar a HEXfunção para testar a saída, por exemplo:

SELECT HEX(CONVERT(`title` USING latin1)) FROM `comments` WHERE id=123;
SELECT HEX(CONVERT(CAST(CONVERT(title USING latin1) AS BINARY) USING utf8)) FROM comments WHERE id = 123;