Обнаружение utf8 сломанных символов в MySQL

У меня есть база данных с кучей сломанных символов utf8, разбросанных по нескольким таблицам. Список персонажей не очень обширен AFAIK (áéíúóÁÉÍÓÚÑ)

Фиксация данной таблицы очень проста

update orderItem set itemName=replace(itemName,'á','á');

Но я не могу найти способ обнаружения сломанных персонажей. Если я сделаю что-то вроде

SELECT * FROM TABLE WHERE field LIKE "%Ã%";

Я получаю почти все поля из-за сортировки (Ã = a). Все сломанные персонажи до сих пор начинаются с "Ã". База данных находится на испанском языке, поэтому этот конкретный символ не используется

Список сломанных символов, которые я получил до сих пор,

á = á
é = é
í- = í
ó = ó
ñ = ñ
á = Á

Любая идея, как заставить этот SELECT работать по назначению? (двоичный поиск или что-то в этом роде)

Ответы

Ответ 1

Как насчет другого подхода, а именно преобразования столбца назад и вперед, чтобы получить правильный набор символов? Вы можете преобразовать его в двоичный, затем в utf-8, а затем в iso-8859-1 или что-то еще, что вы используете. Подробнее см. В руководстве.

Ответ 3

UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'á','á');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ä','ä');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'é','é');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í©','é');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ó','ó');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'íº','ú');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ú','ú');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ñ','ñ');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í‘','Ñ');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'Ã','í');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'â€"','–');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'’','\'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'…','...');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'â€"','-');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'“','"');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'â€','"');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'‘','\'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'•','-');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'‡','c');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'Â','');

Ответ 4

Спасибо за ваши ответы!

Я исправил свои таблицы с этим, и хотел поделиться полным списком изменений. Обратите внимание, что это также включает исправление html-декодированных символов, кроме латинских, это было действительно беспорядок:

(Если вам нужно больше конверсий, посмотрите их на https://www.utf8-chartable.de/unicode-utf8-table.pl)

update 'table' set 'field' = replace('field', 'â€', '"');
update 'table' set 'field' = replace('field', 'â€"', '–');
update 'table' set 'field' = replace('field', '•', '-');
update 'table' set 'field' = replace('field', '“', '"');

update 'table' set 'field' = replace('field', '¡', '¡');
update 'table' set 'field' = replace('field', '¢', '¢');
update 'table' set 'field' = replace('field', '£', '£');
update 'table' set 'field' = replace('field', '¤', '¤');
update 'table' set 'field' = replace('field', 'Â¥', '¥');
update 'table' set 'field' = replace('field', '¦', '¦');
update 'table' set 'field' = replace('field', '§', '§');
update 'table' set 'field' = replace('field', '¨', '¨');
update 'table' set 'field' = replace('field', '©', '©');
update 'table' set 'field' = replace('field', 'ª', 'ª');
update 'table' set 'field' = replace('field', '«', '«');
update 'table' set 'field' = replace('field', '¬', '¬');
# This one looks like it missing a character, but it there. 0xad
update 'table' set 'field' = replace('field', '­', '­');
update 'table' set 'field' = replace('field', '®', '®');
update 'table' set 'field' = replace('field', '¯', '¯');
update 'table' set 'field' = replace('field', '°', '°');
update 'table' set 'field' = replace('field', '±', '±');
update 'table' set 'field' = replace('field', '²', '²');
update 'table' set 'field' = replace('field', '³', '³');
update 'table' set 'field' = replace('field', '´', '´');
update 'table' set 'field' = replace('field', 'µ', 'µ');
update 'table' set 'field' = replace('field', '¶', '¶');
update 'table' set 'field' = replace('field', '·', '·');
update 'table' set 'field' = replace('field', '¸', '¸');
update 'table' set 'field' = replace('field', '¹', '¹');
update 'table' set 'field' = replace('field', 'º', 'º');
update 'table' set 'field' = replace('field', '»', '»');
update 'table' set 'field' = replace('field', '¼', '¼');
update 'table' set 'field' = replace('field', '½', '½');
update 'table' set 'field' = replace('field', '¾', '¾');
update 'table' set 'field' = replace('field', '¿', '¿');

update 'table' set 'field' = replace('field', 'À', 'À');
# This one looks like it missing a character, but it there. 0x81
update 'table' set 'field' = replace('field', 'Ã', 'Á');
update 'table' set 'field' = replace('field', 'Â', 'Â');
update 'table' set 'field' = replace('field', 'Ã', 'Ã');
update 'table' set 'field' = replace('field', 'Ä', 'Ä');
update 'table' set 'field' = replace('field', 'Ã…', 'Å');
update 'table' set 'field' = replace('field', 'Æ', 'Æ');
update 'table' set 'field' = replace('field', 'Ç', 'Ç');
update 'table' set 'field' = replace('field', 'È', 'È');
update 'table' set 'field' = replace('field', 'É', 'É');
update 'table' set 'field' = replace('field', 'Ê', 'Ê');
update 'table' set 'field' = replace('field', 'Ë', 'Ë');
update 'table' set 'field' = replace('field', 'ÃŒ', 'Ì');
# This one looks like it missing a character, but it there. 0x8d
update 'table' set 'field' = replace('field', 'Ã', 'Í');
update 'table' set 'field' = replace('field', 'ÃŽ', 'Î');
# This one looks like it missing a character, but it there. 0x8f
update 'table' set 'field' = replace('field', 'Ã', 'Ï');
# This one looks like it missing a character, but it there. 0x90
update 'table' set 'field' = replace('field', 'Ã', 'Ð');
update 'table' set 'field' = replace('field', 'Ñ', 'Ñ');
update 'table' set 'field' = replace('field', 'Ã', 'Ò');
update 'table' set 'field' = replace('field', 'Ã"', 'Ó');
update 'table' set 'field' = replace('field', 'Ã"', 'Ô');
update 'table' set 'field' = replace('field', 'Õ', 'Õ');
update 'table' set 'field' = replace('field', 'Ö', 'Ö');
update 'table' set 'field' = replace('field', '×', '×');
update 'table' set 'field' = replace('field', 'Ø', 'Ø');
update 'table' set 'field' = replace('field', 'Ù', 'Ù');
update 'table' set 'field' = replace('field', 'Ú', 'Ú');
update 'table' set 'field' = replace('field', 'Û', 'Û');
update 'table' set 'field' = replace('field', 'Ãœ', 'Ü');
# This one looks like it missing a character, but it there. 0x9d
update 'table' set 'field' = replace('field', 'Ã', 'Ý');
update 'table' set 'field' = replace('field', 'Þ', 'Þ');
update 'table' set 'field' = replace('field', 'ß', 'ß');
update 'table' set 'field' = replace('field', 'à', 'à');
update 'table' set 'field' = replace('field', 'á', 'á');
update 'table' set 'field' = replace('field', 'â', 'â');
update 'table' set 'field' = replace('field', 'ã', 'ã');
update 'table' set 'field' = replace('field', 'ä', 'ä');
update 'table' set 'field' = replace('field', 'Ã¥', 'å');
update 'table' set 'field' = replace('field', 'æ', 'æ');
update 'table' set 'field' = replace('field', 'ç', 'ç');
update 'table' set 'field' = replace('field', 'è', 'è');
update 'table' set 'field' = replace('field', 'é', 'é');
update 'table' set 'field' = replace('field', 'ê', 'ê');
update 'table' set 'field' = replace('field', 'ë', 'ë');
update 'table' set 'field' = replace('field', 'ì', 'ì');
# This one looks like it missing a character, but it there. 0xad
update 'table' set 'field' = replace('field', '­­Ã', 'í');
update 'table' set 'field' = replace('field', 'î', 'î');
update 'table' set 'field' = replace('field', 'ï', 'ï');
update 'table' set 'field' = replace('field', 'ð', 'ð');
update 'table' set 'field' = replace('field', 'ñ', 'ñ');
update 'table' set 'field' = replace('field', 'ò', 'ò');
update 'table' set 'field' = replace('field', 'ó', 'ó');
update 'table' set 'field' = replace('field', 'ô', 'ô');
update 'table' set 'field' = replace('field', 'õ', 'õ');
update 'table' set 'field' = replace('field', 'ö', 'ö');
update 'table' set 'field' = replace('field', '÷', '÷');
update 'table' set 'field' = replace('field', 'ø', 'ø');
update 'table' set 'field' = replace('field', 'ù', 'ù');
update 'table' set 'field' = replace('field', 'ú', 'ú');
update 'table' set 'field' = replace('field', 'û', 'û');
update 'table' set 'field' = replace('field', 'ü', 'ü');
update 'table' set 'field' = replace('field', 'ý', 'ý');
update 'table' set 'field' = replace('field', 'þ', 'þ');
update 'table' set 'field' = replace('field', 'ÿ', 'ÿ');

update 'table' set 'field' = replace('field' ,'ç','ç');
update 'table' set 'field' = replace('field' ,'ã','ã');
update 'table' set 'field' = replace('field' ,'á','á');
update 'table' set 'field' = replace('field' ,'â','â');
update 'table' set 'field' = replace('field' ,'é','é');
update 'table' set 'field' = replace('field' ,'í','í');
update 'table' set 'field' = replace('field' ,'õ','õ');
update 'table' set 'field' = replace('field' ,'ú','ú');
update 'table' set 'field' = replace('field' ,'ç','ç');
update 'table' set 'field' = replace('field' ,'Á','Á');
update 'table' set 'field' = replace('field' ,'Â','Â');
update 'table' set 'field' = replace('field' ,'É','É');
update 'table' set 'field' = replace('field' ,'Í','Í');
update 'table' set 'field' = replace('field' ,'Õ','Õ');
update 'table' set 'field' = replace('field' ,'Ú','Ú');
update 'table' set 'field' = replace('field' ,'Ç','Ç');
update 'table' set 'field' = replace('field' ,'Ã','Ã');
update 'table' set 'field' = replace('field' ,'À','À');
update 'table' set 'field' = replace('field' ,'Ê','Ê');
update 'table' set 'field' = replace('field' ,'Ó','Ó');
update 'table' set 'field' = replace('field' ,'Ô','Ô');
update 'table' set 'field' = replace('field' ,'Ü','Ü');
update 'table' set 'field' = replace('field' ,'ã','ã');
update 'table' set 'field' = replace('field' ,'à','à');
update 'table' set 'field' = replace('field' ,'ê','ê');
update 'table' set 'field' = replace('field' ,'ó','ó');
update 'table' set 'field' = replace('field' ,'ô','ô');
update 'table' set 'field' = replace('field' ,'ü','ü');
update 'table' set 'field' = replace('field' ,'&','&');
update 'table' set 'field' = replace('field' ,'>','>');
update 'table' set 'field' = replace('field' ,'&lt;','<');
update 'table' set 'field' = replace('field' ,'&circ;','ˆ');
update 'table' set 'field' = replace('field' ,'&tilde;','˜');
update 'table' set 'field' = replace('field' ,'&uml;','¨');
update 'table' set 'field' = replace('field' ,'&cute;','´');
update 'table' set 'field' = replace('field' ,'&cedil;','¸');
update 'table' set 'field' = replace('field' ,'&quot;','"');
update 'table' set 'field' = replace('field' ,'&ldquo;','"');
update 'table' set 'field' = replace('field' ,'&rdquo;','"');
update 'table' set 'field' = replace('field' ,'&lsquo;','‘');
update 'table' set 'field' = replace('field' ,'&rsquo;','');
update 'table' set 'field' = replace('field' ,'&lsaquo;','‹');
update 'table' set 'field' = replace('field' ,'&rsaquo;','›');
update 'table' set 'field' = replace('field' ,'&laquo;','«');
update 'table' set 'field' = replace('field' ,'&raquo;','»');
update 'table' set 'field' = replace('field' ,'&ordm;','º');
update 'table' set 'field' = replace('field' ,'&ordf;','ª');
update 'table' set 'field' = replace('field' ,'&ndash;','–');
update 'table' set 'field' = replace('field' ,'&mdash;','—');
update 'table' set 'field' = replace('field' ,'&macr;','¯');
update 'table' set 'field' = replace('field' ,'&hellip;','…');
update 'table' set 'field' = replace('field' ,'&brvbar;','¦');
update 'table' set 'field' = replace('field' ,'&bull;','•');
update 'table' set 'field' = replace('field' ,'&para;','¶');
update 'table' set 'field' = replace('field' ,'&sect;','§');
update 'table' set 'field' = replace('field' ,'&sup1;','¹');
update 'table' set 'field' = replace('field' ,'&sup2;','²');
update 'table' set 'field' = replace('field' ,'&sup3;','³');
update 'table' set 'field' = replace('field' ,'&frac12;','½');
update 'table' set 'field' = replace('field' ,'&frac14;','¼');
update 'table' set 'field' = replace('field' ,'&frac34;','¾');
update 'table' set 'field' = replace('field' ,'&#8539;','⅛');
update 'table' set 'field' = replace('field' ,'&#8540;','⅜');
update 'table' set 'field' = replace('field' ,'&#8541;','⅝');
update 'table' set 'field' = replace('field' ,'&#8542;','⅞');
update 'table' set 'field' = replace('field' ,'&gt;','>');
update 'table' set 'field' = replace('field' ,'&lt;','<');
update 'table' set 'field' = replace('field' ,'&plusmn;','±');
update 'table' set 'field' = replace('field' ,'&minus;','−');
update 'table' set 'field' = replace('field' ,'&times;','×');
update 'table' set 'field' = replace('field' ,'&divide;','÷');
update 'table' set 'field' = replace('field' ,'&lowast;','∗');
update 'table' set 'field' = replace('field' ,'&frasl;','⁄');
update 'table' set 'field' = replace('field' ,'&permil;','‰');
update 'table' set 'field' = replace('field' ,'&int;','∫');
update 'table' set 'field' = replace('field' ,'&sum;','∑');
update 'table' set 'field' = replace('field' ,'&prod;','∏');
update 'table' set 'field' = replace('field' ,'&radic;','√');
update 'table' set 'field' = replace('field' ,'&infin;','∞');
update 'table' set 'field' = replace('field' ,'&asymp;','≈');
update 'table' set 'field' = replace('field' ,'&cong;','≅');
update 'table' set 'field' = replace('field' ,'&prop;','∝');
update 'table' set 'field' = replace('field' ,'&equiv;','≡');
update 'table' set 'field' = replace('field' ,'&ne;','≠');
update 'table' set 'field' = replace('field' ,'&le;','≤');
update 'table' set 'field' = replace('field' ,'&ge;','≥');
update 'table' set 'field' = replace('field' ,'&there4;','∴');
update 'table' set 'field' = replace('field' ,'&sdot;','⋅');
update 'table' set 'field' = replace('field' ,'&middot;','·');
update 'table' set 'field' = replace('field' ,'&part;','∂');
update 'table' set 'field' = replace('field' ,'&image;','ℑ');
update 'table' set 'field' = replace('field' ,'&real;','ℜ');
update 'table' set 'field' = replace('field' ,'&prime;','′');
update 'table' set 'field' = replace('field' ,'&Prime;','″');
update 'table' set 'field' = replace('field' ,'&deg;','°');
update 'table' set 'field' = replace('field' ,'&ang;','∠');
update 'table' set 'field' = replace('field' ,'&perp;','⊥');
update 'table' set 'field' = replace('field' ,'&nabla;','∇');
update 'table' set 'field' = replace('field' ,'&oplus;','⊕');
update 'table' set 'field' = replace('field' ,'&otimes;','⊗');
update 'table' set 'field' = replace('field' ,'&alefsym;','ℵ');
update 'table' set 'field' = replace('field' ,'&oslash;','ø');
update 'table' set 'field' = replace('field' ,'&Oslash;','Ø');
update 'table' set 'field' = replace('field' ,'&isin;','∈');
update 'table' set 'field' = replace('field' ,'&notin;','∉');
update 'table' set 'field' = replace('field' ,'&cap;','∩');
update 'table' set 'field' = replace('field' ,'&cup;','∪');
update 'table' set 'field' = replace('field' ,'&sub;','⊂');
update 'table' set 'field' = replace('field' ,'&sup;','⊃');
update 'table' set 'field' = replace('field' ,'&sube;','⊆');
update 'table' set 'field' = replace('field' ,'&supe;','⊇');
update 'table' set 'field' = replace('field' ,'&exist;','∃');
update 'table' set 'field' = replace('field' ,'&forall;','∀');
update 'table' set 'field' = replace('field' ,'&empty;','∅');
update 'table' set 'field' = replace('field' ,'&not;','¬');
update 'table' set 'field' = replace('field' ,'&and;','∧');
update 'table' set 'field' = replace('field' ,'&or;','∨');
update 'table' set 'field' = replace('field' ,'&crarr;','↵');

Ответ 5

Никакая замена текста не является универсальным решением, потому что вы можете забыть о каком-то характере. Более подходящее исправление для двух преобразованных символов:

  • конвертировать обратно в latin1
  • преобразовать в двоичный
  • конвертировать в utf8

Вот так:

alter table descriptions modify name VARCHAR(2000) character set latin1;
alter table descriptions modify name blob;
alter table descriptions modify name VARCHAR(2000) character set utf8;

Ответ 6

Оператор SELECT, который вам нужен, следующий:

SELECT * FROM TABLE WHERE LENGTH(name) != CHAR_LENGTH(name);

Возвращает все строки, содержащие многобайтовые символы.

name считается полем/полем, в котором будут найдены странные символы. *

Ответ 8

У меня была такая же проблема, но мне не понравилось решение replace(), потому что всегда есть возможность пропускать некоторые символы. Я работал против столбца со смешанными данными (некоторые из них были utf8_encode() d, а некоторые нет) с 4 миллионами строк, около 250 тыс. Записей с неверно кодированными данными (с символами Ã ‰/etc), охватывающие около 15 международных языков, включая в основном европейские языки, но также русский, японский и китайский.

Я начал с дублирования столбца, так как я не хотел потерять какие-либо данные:

ALTER TABLE images ADD COLUMN reptitle TEXT;

Скопировал все данные с помощью многобайтовых символов (спасибо Адаму за подсказку)

UPDATE images SET reptitle = title WHERE LENGTH(title) != CHAR_LENGTH(title)

Поскольку reptitle был создан с набором символов по умолчанию в таблице, он уже был utf8, но содержал поврежденные данные, поскольку таблица изображений использовалась как источник латинского языка. В столбце reptitle теперь содержатся некоторые данные, которые правильно закодированы, а некоторые повреждены (все значения с многобайтовыми символами, некоторые из них были правильно utf8_encode() d. Итак, с подсказкой Дэвида...

ALTER TABLE images MODIFY reptitle TEXT character set latin1;
ALTER TABLE images MODIFY reptitle BLOB;
ALTER TABLE images MODIFY reptitle TEXT character set utf8;

Средний шаг, возможно, не был необходим, поскольку TEXT и BLOB (я думаю) одинаковы. Это привело к исправлению всех неправильно закодированных данных ( "Ã © tudiantes" стал "étudiantes" и т.д.), Но данные, которые были правильны ранее, были усечены у первого многобайтового символа ( "Lapin de Pâques" стал "Lapin de P" ). Я не знаю, почему усечение, но это в одноразовой колонке, поэтому мне было все равно. Усеченные данные дают CHAR_LENGTH и LENGTH тех же значений, потому что нет многобайтовых символов, оставшихся таким легким запросом...

UPDATE images SET title = reptitle WHERE LENGTH(reptitle)!=CHAR_LENGTH(reptitle)

Затем, конечно, просто опустите запасной столбец

ALTER TABLE images DROP COLUMN reptitle

Также убедитесь, что (так как я использую PHP, и это несколько раз приводило меня в порядок, поэтому я думал, что я упоминал об этом здесь), все ваши файлы script - это UTF8 (без спецификации), и вы используете:

mysql_set_charset('utf8', $connection);

Et voilà... отлично отремонтированные данные, все языки:)

Ответ 9

В дополнение к Raúl Avila Solano и acseven ответьте, если вы хотите обновить все сломанные символы в одном запросе, вы можете сделать:

update `table` set field = replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(field,'&uuml;','ü'),'&ocirc;','ô'),'&oacute;','ó'),'&ecirc;','ê'),'&agrave;','à'),'&atilde;','ã'),'&Uuml;','Ü'),'&Ocirc;','Ô'),'&Oacute;','Ó'),'&Ecirc;','Ê'),'&Agrave;','À'),'&Atilde;','Ã'),'&Ccedil;','Ç'),'&Uacute;','Ú'),'&Otilde;','Õ'),'&Iacute;','Í'),'&Iacute;','Í'),'&Eacute;','É'),'&Acirc;','Â'),'&Aacute;','Á'),'&ccedil;','ç'),'&uacute;','ú'),'&otilde;','õ'),'&iacute;','í'),'&eacute;','é'),'&acirc;','â'),'&aacute;','á'),'&atilde;','ã'),'&ccedil;','ç'),'à ','à'),'à ','à'),'º','º'),'ª','ª'),'ç','ç'),'â€"','–'),'ó','ó'),'é','é'),'á','á'),'ê','ê'),'ã','ã'),'â','â'),'í','í'),'õ','õ'),'Ø','Ø'),'•','-'),'ú','ú'),'à ','À'),'Ã','Ã'),'Ç','Ç'),'â€','"'),'“','"'),'É','É');

Ответ 10

это также решило мою проблему на некоторых итальянских символах

UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'á','á');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ä','ä');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'é','é');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í©','é');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ó','ó');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'íº','ú');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ú','ú');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'ñ','ñ');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í‘','Ñ');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'Ã','í');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'â€"','–');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'’','\'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'…','...');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'â€"','-');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'“','"');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'â€','"');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'‘','\'');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'•','-');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name`,'‡','c');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'Â','');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í ','à');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í¨','è');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'íˆ','È');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'€','€');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'eÌ€','è');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í²','ò');
UPDATE `table_name` SET `column_name` = REPLACE(`column_name` ,'í¹','ù');

Ответ 11

У вас могут быть строки с правильно закодированным UTF8 и с неправильно закодированными символами. В этом случае "CONVERT (BINARY CONVERT (post_title USING latin1) USING utf8)" обрезает некоторые поля.

Я закончил это так.

update `table` set `name` = replace(`name` ,CONVERT(BINARY "ä" USING latin1),'ä');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "ö" USING latin1),'ö');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "ü" USING latin1),'ü');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "Ä" USING latin1),'Ä');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "Ö" USING latin1),'Ö');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "Ü" USING latin1),'Ü');
update `table` set `name` = replace(`name` ,CONVERT(BINARY "ß" USING latin1),'ß');

Ответ 12

Основываясь на данных этого поста https://www.i18nqa.com/debug/utf8-debug.html, я бы посоветовал, что это хороший запрос для определения хитрых записей и возможных правильных значений:

SELECT my_field,CONVERT(BINARY CONVERT(my_field USING latin1) USING utf8mb4) AS new_field_value FROM my_table WHERE my_field REGEXP '[âÆËÅÂÃ]';

Будьте очень осторожны, потому что у нас была неправильная кодировка имени файла, но нормальная кодировка пути, и в этом случае некоторые из приведенных выше решений могли бы причинить боль. Если некоторые из ваших данных уже правильно закодированы в UTF8, вы, вероятно, обнаружите, что потеряли их.

Ответ 13

Средний шаг, возможно, не был необходимым, поскольку TEXT и BLOB совпадают.

Это привело к исправлению всех неправильно закодированных данных, но данные, которые были ранее правильными, были усечены при первом многобайтовом символе.

Ответ 14

Там хороший script для автоматизации процесса преобразования в целом базе данных. Также полезно знать, что реализация MySQL UTF-8 является неполной, поскольку она поддерживает только символы UTF-8 до 3 байтов. Решение состоит в использовании кодировки utf8mb4, представленной в MySQL 5.5.3.

Ответ 15

Это расширение ответа @Thales Ceolin для изменения каждой таблицы в БД:

select concat(
    "update ", 
    a.TABLE_NAME, 
    " set ", b.COLUMN_NAME, 
    " = CONVERT(BINARY CONVERT(", 
    b.COLUMN_NAME, 
    " USING latin1) USING utf8) where ",
    b.COLUMN_NAME, 
    " is not null;") query
from INFORMATION_SCHEMA.TABLES a
left join INFORMATION_SCHEMA.COLUMNS b on a.TABLE_NAME = b.TABLE_NAME
where a.table_schema = 'db_name'
and a.TABLE_TYPE = 'BASE TABLE'
and b.data_type in ('text', 'varchar')
and a.TABLE_NAME = 'table_name';

Это приведет к:

update table_name set idn = CONVERT(BINARY CONVERT(idn USING latin1) USING utf8) where idn is not null;
update table_nameset name = CONVERT(BINARY CONVERT(name USING latin1) USING utf8) where name is not null;
update table_name set primary_last_name = CONVERT(BINARY CONVERT(primary_last_name USING latin1) USING utf8) where primary_last_name is not null;

Ответ 16

Поскольку основной вопрос был об обнаружении битых символов, мое решение: (чтобы предотвратить нормальное двойное кодирование кодировки)

  1. Обнаружение (от латинского 1 до utf8)
SELECT name FROM %table% 
 WHERE 
CONVERT(CONVERT(name USING BINARY) USING utf8 ) != CONVERT(CONVERT(CONVERT(CONVERT(name USING BINARY) USING latin1) USING BINARY) USING utf8);
  1. Обновление (с латиницы 1 до utf8)
UPDATE %table% SET name = convert(cast(convert(name using latin1 ) as binary) using utf8 )
 WHERE 
CONVERT(CONVERT(name USING BINARY) USING utf8 ) != CONVERT(CONVERT(CONVERT(CONVERT(name USING BINARY) USING latin1) USING BINARY) USING utf8);