заменить символы мусора в mysql

Моя БД находится в latin1 и заполнена â" или '��"' (в зависимости от того, установлен ли мой терминал на latin1 или unicode соответственно). Судя по контексту, я думаю, что они должны быть тире. Похоже, они вызывают неприятные ошибки при рендеринге (или не рендеринге) в IE. Я хотел бы найти и заменить их. Проблема в том, что ни â, ни � не совпадают с replace. Выполнение запроса:

    update TABLE set COLUMN = replace(COLUMN,'��"','---');

Выполняется без ошибок, но ничего не делает (изменено 0 строк). Мне ясно, что символ «вопросительный знак в ромбе» не сопоставляется, когда я копирую его в терминале. Есть ли способ узнать его код и сопоставить его с этим или чем-то еще? Консоль mysql очень близка к тому, чтобы сделать это в одну строку, поэтому я бы предпочел не запускать ее вне терминала, если можно этого избежать.

База данных размещена на Amazon RDS, поэтому я не могу установить регулярное выражение udf, на которое я видел ссылку в других вопросах здесь. В долгосрочной перспективе мне придется правильно преобразовать всю базу данных в utf8, но мне нужно немедленно решить эту проблему с рендерингом.

РЕДАКТИРОВАТЬ:

Я изолировал плохой символ с помощью hexdump, это e2 80 (я не думаю, что это соответствует какому-либо символу юникода). Как я могу передать это функции замены?

    update TABLE set COLUMN = replace(COLUMN, char(0xe2,0x80),'---');

ничего не делает.


mysql character-encoding latin1
person mmdanziger    schedule 13.02.2012    source источник
comment
Есть ли причина для того, чтобы он был latin1? Сейчас я обычно использую юникод по умолчанию, и его не так сложно преобразовать - просто выгрузите БД в SQL и перезагрузите ее в новую, использующую utf8.   -  person Matt Gibson    schedule 14.02.2012
comment
Я унаследовал это. Дамп нежелателен, потому что это повлечет за собой значительные простои, которых хотелось бы избежать. Я читал о методах на месте, таких как nicj.net/2011/04/17/, но у меня не было времени адаптировать его к моей базе данных.   -  person mmdanziger    schedule 14.02.2012
comment
У вас может не быть другого выбора, кроме как найти все строки с этой ошибочной последовательностью символов и вручную создать отдельные операторы, чтобы исправить каждую из них (хотя сценарий может помочь в этом).   -  person staticsan    schedule 14.02.2012
comment
Данные хранятся неправильно, отображаются неправильно или действительно повреждены? Пожалуйста, загляните в раздел базы данных на kunststube.net/frontback и попытайтесь выяснить это. Ответ может состоять в том, чтобы экспортировать данные в неправильной кодировке и повторно импортировать их обратно в правильной кодировке.   -  person deceze♦    schedule 14.02.2012
comment
Все вышеперечисленное в данном конкретном случае. В общем, мы сохраняем хороший UTF8 в столбцах latin1, а затем извлекаем его и отображаем как UTF8, чтобы все работало гладко. А вот со вторым что-то не так... с этим становится очень трудно справиться. В ближайшее время я переведу все в UTF8 в БД.   -  person mmdanziger    schedule 15.02.2012

Ответы (2)


arrow_upward
1
arrow_downward

Я понял. Я использовал встроенную функцию mysql hex, чтобы вывести запись, которая, как я знал, была плохой.

    select hex(column) from table where id=666;

Затем выбрал слова (эти числа, зажатые между «20») и обнаружил, что мой оскорбительный набор байтов на самом деле был x'C3A2E282AC2671756F743B'. Как это соответствует тому, как я видел его закодированным в PHP и в моей системе (как e2 80), я не знаю, и на данный момент мне все равно.

Чтобы проверить, прежде чем уничтожить данные, вы снова подключаете это к mysql:

    select x'C3A2E282AC2671756F743B';
    +---------------------------+
    | x'C3A2E282AC2671756F743B' |
    +---------------------------+
    | â€"               |
    +---------------------------+
    1 row in set (0.00 sec)

Итак, используя запрос на замену, как указано выше, я смог сразу избавиться от всех неверных данных.

Для протокола было:

    update TABLE set COLUMN = replace(COLUMN, x'C3A2E282AC2671756F743B','--');

Я очень надеюсь, что это полезно для кого-то. Несмотря на то, что путаница в кодировании довольно распространена в mysql, я поискал везде и не смог найти объяснения этому, в конечном счете, довольно простому процессу.

person mmdanziger    schedule 14.02.2012
comment
x'C3A2' — это UTF-8 для â; x'E282AC' — это UTF-8 для €; x'2671756F743B' - это ASCII для ", поэтому что-то делает кодировку Unicode... - person Neil; 15.02.2012
comment
@Neil Я думаю, что история заключалась в том, что текст был подготовлен с помощью какой-то MS cp1252 (или другой схемы MS ??), затем неэффективно преобразован в UTF8 и сброшен в столбец latin1. Дефис cp1252 en/em не очень хорошо копируется в UTF8, поэтому все, что было преобразовано, превратило его в то, что кажется произвольным мусором... Но en(em)dash равен 96(97) в cp1252 и E2 80 93 (E2 80 94) в UTF8... не знаю, как любой из них стал c3a2e82ac... - person mmdanziger; 15.02.2012
comment
E2 80 94 - это — в Windows-1252 - возможно, умная цитата превратилась в " где-то в строке. - person Neil; 16.02.2012

arrow_upward
0
arrow_downward

Кто-то мог отправить длинное тире (U+2014) с кодировкой UTF-8 (E2 80 94), которую вы сейчас пытаетесь интерпретировать как латиницу-1, однако это недействительно в латинице-1, которая исключает диапазон 80- 9F, поэтому вы, вероятно, увидите вместо недопустимых байтов символы замены Unicode, что приведет к â�� при отображении. Для сравнения, в Windows-1252 он будет отображаться как —.

Вы можете использовать CHAR(0xE2, 0x80, 0x94) для создания строки поиска.

person Neil    schedule 13.02.2012
comment
Ваш ответ привел меня в правильном направлении, я понял, что плохой персонаж — это e2 80. Но как сформировать запрос на замену? это не так, как я ожидал. - person mmdanziger; 14.02.2012
comment
На самом деле... это не e2 80. См. мое решение проблемы ниже. - person mmdanziger; 15.02.2012