Как очистить данные с помощью специальных символов в MySQL

Question

Как можно очистить данные, которые выглядят так Réation, l’Oreal выглядеть так R'action а также L'Oreal соответственно в MySQL?

Answer 1

Это выглядит как пример "двойного кодирования". Именно там правая рука говорила с utf8, но левая рука слушала латынь1. См. Проблема с символами UTF-8; то, что я вижу, - это не то, что я сохранил. См. также http://mysql.rjweb.org/doc.php/charcoll.

Réation -> Réation после удаления двойного кодирования.

Еще вы говорите R'action - Интересно, печатал ли ты é как e' или же 'e?? Я также собираюсь предположить, что вы имели в виду L’Oreal?? (Обратите внимание на "правильную одинарную кавычку" вместо "апостроф".)

Во-первых, нам нужно убедиться, что это на самом деле обычное двойное кодирование.

SELECT col, HEX(col) FROM ... WHERE ...

должен дать вам это для гекса Réation:

52  E9       6174696F6E  -- latin1 encoding
52 C3A9      6174696F6E  -- utf8 encoding
52 C383 C2A9 6174696F6E  -- double encoding

(Игнорировать интервал.)

Если у вас есть третий из них, приступайте к моему ответу. Если вы получите что-нибудь еще, СТОП! - проблема сложнее, чем я думал.

Теперь посмотрим, исправит ли это исправление в двойном кодировании (перед тем, как это исправить):

SELECT col, CONVERT(BINARY(CONVERT(CONVERT(
                  BINARY(CONVERT(col USING latin1)) USING utf8mb4)
                      USING latin1)) USING utf8mb4)
     FROM tbl;

Вы должны предотвратить это и исправить данные. Некоторое из следующего необратимо; проверить это на копии таблицы!

Ваш случай: CHARACTER SET latin1, но содержат в себе байты utf8/utf8mb4; оставить байты в покое при исправлении кодировки:

Во-первых, давайте предположим, что у вас есть это объявление для tbl.col:

col VARCHAR(111) CHARACTER SET latin1 NOT NULL

Затем преобразовать столбец без изменения байтов:

ALTER TABLE tbl MODIFY COLUMN col VARBINARY(111) NOT NULL;
ALTER TABLE tbl MODIFY COLUMN col VARCHAR(111) CHARACTER SET utf8mb4 NOT NULL;

Примечание: если вы начнете с TEXT использовать BLOB в качестве промежуточного определения. (Обязательно оставьте остальные спецификации такими же - VARCHAR, NOT NULL, так далее.)

Сделайте это для каждого столбца в каждой таблице с проблемой.

(В этом обсуждении я не делаю различий между utf8mb4 и utf8. Большинство текстов вполне довольны; эмодзи и некоторым китайцам нужен utf8mb4, а не просто utf8.)

из комментария

        CONVERT(UNHEX('C38EC2B2') USING utf8mb4) = 'β'  (Greek beta)
CONVERT(CONVERT(UNHEX('C38EC2B2') USING latin1) USING utf8mb4) = 'β'

Мой вывод: сначала у вас была какая-то неверная конфигурация. Затем вы применили одно или несколько неправильных исправлений. Теперь у вас такой беспорядок, что я не осмелюсь помочь вам распутать его. Таким образом, беспорядок включен вне просто "двойного кодирования".

Если возможно, начните сначала, убедившись, что некоторые тестовые данные сохраняются правильно перед добавлением дополнительных данных. Если данные плохие, не пытайтесь исправить данные; отойдите и начните все сначала. См. "Лучшая практика" в разделе "Проблемы..." для правильной настройки. Я буду рядом, чтобы помочь вам понять, верен ли гекс, который вы видите в таблицах.