Как удалить ¶ из sql?

Question

[Tupic123]

Всем привет, перестали отображаться данные в одном из модулей один текст отображается, другой нет. Путем сравнения понял, что в "сломанном" варианте значения есть ¶, а в "нормальном" вместо него стоит \r\n. Вот и вопрос, как заменить в таблице 'table_name' во всех ячейках с именем 'value' символ ¶ на \r\n

Comments

[voyager-studio]

Интересная проблема, ранее с этим не сталкивался...

[Tupic123]

voyager-studio, Согласен, одна из самых интересных задач за последнее время...

Answer 1

[alexalexes]

update table_name
set value = replace(value, char(код символа ¶), concat(char(код символа \r), char(код символа\n)))

Только не копипасьте вслепую, а разберитесь с вложенными функциями, в какой кодировке база и какие коды символов можно использовать для замены.
Перед применением update проверьте результат select-ом:

select value,
          replace(value, char(код символа ¶), concat(char(код символа \r), char(код символа\n))) as new_value
from table_name

Comments

[Tupic123]

А текст внутри value не изменится?

[alexalexes]

если будете update использовать, то изменения сохранятся в базу. Если будете преобразовывать вывод value при select-e, то изменится только вывод.

[Tupic123]

alexalexes,
SELECT value,
REPLACE(value, char(0xC20xB6c2b6), concat(char(x0A), char(x0A))) AS new_value
FROM table_name
#1054 - Неизвестный столбец '0xC20xB6c2b6' в 'field list'

[alexalexes]

Вы хоть отличаете, где корректно записано число?

SELECT value,
REPLACE(value, char(0xc2b6 using utf8), concat(char(0x0D using utf8), char(0x0A using utf8))) AS new_value
FROM table_name

[Tupic123]

alexalexes, Я написал в итоге sql запрос, но как понял что знак абзаца - это просто отображаемый в инструментах невидимый знак, а по факту как такового его нет. Теперь вопрос как первую (не рабочую) строку превратить во вторую(работает)

{"1":{"type":"2","block_heading":{"1":""},"block_content":{"1":""},"html":{"1":"<div class="footer-left">
                            <span class="logo">
                                <a href="index.php" title="Porto eCommerce Template">
                                    <img class="img-responsive" src="image\/catalog\/proizvoditeli\/lokmat.jpg" alt="Porto eCommerce" width="75">
                                <\/a>
                            <\/span>
                            <div class="social-icons">
                                <a href="https:\/\/www.facebook.com\/lockmatukraine\/" class="social-icon icon-facebook" target="_blank">
                                    <i class="fa fa-facebook"><\/i>
                                <\/a>
                                <a href="https:\/\/www.instagram.com\/lock_mat_ukraine\/" class="social-icon icon-twitter" target="_blank">
                                    <i class="fa fa-instagram"><\/i>
                                <\/a>
                                <a href="https:\/\/vk.com\/lockmat" class="social-icon icon-linkedin" target="_blank">
                                    <i class="fa fa-vk"><\/i>
                                <\/a>
                            <\/div>
                        <\/div><!-- End .footer-left -->

                        <div class="footer-center">
                            <a style="color:#fff; text-decoration: underline" href=".\/dostavka-oplata">	\u0414\u043e\u0441\u0442\u0430\u0432\u043a\u0430 \u0438 \u043e\u043f\u043b\u0430\u0442\u0430<\/a>  
 <a style="color:#fff; text-decoration: underline" href=".\/vozvrat"> \u0412\u043e\u0437\u0432\u0440\u0430\u0442 <\/a>
                        <\/div><!-- End .footer-center -->

                        <div class="footer-right">
                        \u00a9 Copyright 2017-2020. Lock-Mat. \u0412\u0441\u0435 \u043f\u0440\u0430\u0432\u0430 \u0437\u0430\u0449\u0438\u0449\u0435\u043d\u044b
                        <\/div><!-- End .footer-right -->"},"layout_id":"99999","position":"bottom","status":"1","sort_order":""}}

{"1":{"type":"2","block_heading":{"1":""},"block_content":{"1":""},"html":{"1":"<div class="footer-left">\r\n                            <span class="logo">\r\n                                <a href="index.php" title="Porto eCommerce Template">\r\n                                    <img class="img-responsive" src="image\/catalog\/proizvoditeli\/lokmat.jpg" alt="Porto eCommerce" width="75">\r\n                                <\/a>\r\n                            <\/span>\r\n                            <div class="social-icons">\r\n                                <a href="https:\/\/www.facebook.com\/lockmatukraine\/" class="social-icon icon-facebook" target="_blank">\r\n                                    <i class="fa fa-facebook"><\/i>\r\n                                <\/a>\r\n                                <a href="https:\/\/www.instagram.com\/lock_mat_ukraine\/" class="social-icon icon-twitter" target="_blank">\r\n                                    <i class="fa fa-instagram"><\/i>\r\n                                <\/a>\r\n                                <a href="https:\/\/vk.com\/lockmat" class="social-icon icon-linkedin" target="_blank">\r\n                                    <i class="fa fa-vk"><\/i>\r\n                                <\/a>\r\n                            <\/div>\r\n                        <\/div><!-- End .footer-left -->\r\n\r\n                        <div class="footer-center">\r\n                            <a style="color:#fff; text-decoration: underline" href=".\/dostavka-oplata">\t\u0414\u043e\u0441\u0442\u0430\u0432\u043a\u0430 \u0438 \u043e\u043f\u043b\u0430\u0442\u0430<\/a>  \r\n <a style="color:#fff; text-decoration: underline" href=".\/vozvrat"> \u0412\u043e\u0437\u0432\u0440\u0430\u0442 <\/a>\r\n                        <\/div><!-- End .footer-center -->\r\n\r\n                        <div class="footer-right">\r\n                        \u00a9 Copyright 2017-2020. Lock-Mat. \u0412\u0441\u0435 \u043f\u0440\u0430\u0432\u0430 \u0437\u0430\u0449\u0438\u0449\u0435\u043d\u044b\r\n                        <\/div><!-- End .footer-right -->"},"layout_id":"99999","position":"bottom","status":"1","sort_order":""}}

[Tupic123]

alexalexes, И как такое может появлиять или возникнуть? Сломалось много чего

[alexalexes]

Валидный JSON хотите получить, тогда это меняет дело.
Тогда никаких символьных представлений переноса строк на выходе не должно быть, они должны стать именно '\r\n' (этими 4-мя символами, а не двумя байтами).
Тогда для всех возможных видов символов переносов нужно подготовить замену на экранирующий эквивалент:

SELECT value,
replace(replace(replace(value, char(0x0A using utf8), '\n'), char(0xc2b6 using utf8), '\r\n'),  char(0x0D using utf8), '\r') AS new_value
FROM table_name

Или даже так, в зависимости от того как будет происходить последующая обработка строки (вдруг далее будет еще какое-то разэкранирование, чтобы потом отдекодить JSON):

SELECT value,
replace(replace(replace(value, char(0x0A using utf8), '\\n'), char(0xc2b6 using utf8), '\\r\\n'),  char(0x0D using utf8), '\\r') AS new_value
FROM table_name

А вообще, проблему нужно решать в том модуле, который заносит в базу невалидный JSON, он не должен пропускать ни одного невалидного символа для нотации JSON, должен их либо удалять либо подставлять экранирующую последовательность.

[Tupic123]

alexalexes, Ну так вот именно, что модуль от себя вносит валидные строки) Но в какой то момент в базе появились эти параграфы вместо \r\n. Они не символом там, а просто ну "абзац". Не понимаю как это могло произойти ибо это не впечатанный символ, а "невидимый"

[alexalexes]

Значит, где-то есть форма, которая позволяет вносить такой контент не по общим правилам. Посмотрите, какие пользователи имеют доступ на внесение этих данных и как у них выглядит эта форма в плане сохранения изменений.

[Tupic123]

alexalexes, я возможно даже знаю где она может быть, но базу то как починить бы...

[alexalexes]

Починить можно update-ом, но нужно убедиться, что заплатка из реплейсов работает на 99% данных. Подстраховать себя резервной копией таблицы или даже базы.
До исправления такую же заплатку нужно ставить на select-ы, если процесс поиска источника затянется.

[Tupic123]

alexalexes, Я пробовал вышеприведенный запрос, но каких либо изменений он не дал, что странно

[Tupic123]

alexalexes, Не силен в sql, не подскажите как написать правильный update?

[alexalexes]

Этим запросом проверить, что в каждой строке new_value формируется валидный JSON (если уверены, то можно проверить некоторые строки):

SELECT value,
replace(replace(replace(value, char(0x0A using utf8), '\\n'), char(0xc2b6 using utf8), '\\r\\n'),  char(0x0D using utf8), '\\r') AS new_value
FROM table_name

Далее сделать экспорт таблицы или базы данных.
Желательно, между моментом начала бэкапа и последним корректным update-ом обеспечить невозможность вносить новые данные в исправляемую таблицу.
Теперь нужно разобраться с утилитой, через которую вы посылаете запрос в СУБД.
Если вы делаете запрос на phpmyadmin, то нужно убедиться что время выполнения скрипта php перед таймаутом будет больше чем запрос update.
Если время выполнения скрипта нельзя трогать или как-то увеличить до разумных пределов, то в запрос update придется подставлять limit и обновлять записи порциями, обработка которых гарантировано заходит во время выполнения.
Далее, понять как работают транзакции в программе, с помощью которой посылаете запрос.
Для phpmyadmin всегда работает auto commit. Это значит, что по завершении запроса update данные в базе кристализуются без каких транзакционных команд (commit, rollback) и будут доступны для выборки.
Если для выполнения запроса вы используете консоль или какую-то десктоп прогу, то тоже нужно убедиться как работает режим транзакций.
Если вы отмените автокамит, то после update должны сами скомандовать commit/rollback в зависимости от того, готовы ли морально выкристализовать изменения в базе. После команды commit-а откатить изменения нельзя, только возиться с бэкапом.
Собственно, сам update:

update table_name
set value = replace(replace(replace(value, char(0x0A using utf8), '\n'), char(0xc2b6 using utf8), '\r\n'),  char(0x0D using utf8), '\r')

[Tupic123]

alexalexes, Да, но данный запрос затрагивает 0 строк, то есть таких символов переноса там видимо нет, наверное мне нужно подобрать другой код который я заменяю на \r и \n?

[alexalexes]

Да, взять hex редактор и посмотреть коды символов в тексте.

[Tupic123]

alexalexes, Если я правильно понял то параграф - 0xB6 (decimal 182), from where it was inherited by ISO/IEC 8859-1 (1987) and thence by Unicode as U+00B6 ¶
Это 0xB6, но

update oc_setting
set value = replace(replace(replace(value, char(0xB6  using utf8), '\n'), char(0xB6  using utf8), '\r\n'),  char(0xB6  using utf8), '\r')

Все равно затрагивает 0 строк

[alexalexes]

Не нужно пользоваться update-ом для поиска, сломаете данные при необдуманной правке.
Используйте select:

select *
from oc_setting
where value like concat('%', char(182), '%')
-- value like concat('%', char(1987), '%')
-- value like concat('%', char(0xB6), '%')
-- value like concat('%', char(0x00B6), '%')
-- value like concat('%', char(0xB6 using utf8), '%')
-- value like concat('%', char(0x00B6 using utf8), '%')

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

[Tupic123]

alexalexes,

select *
from oc_setting
where value like concat('%', char(0xB6 using utf8), '%')
-- value like concat('%', char(0x00B6 using utf8), '%')

Вот эти варианты отдали результаты не равные 0, и по гуглу расшифровываются как абзацы. Теперь нужно сделать бэкап и выполнить

update oc_setting
set value = replace(replace(replace(value, char(0x00B6using utf8), '\n'), char(0x00B6using utf8), '\r\n'),  char(0x00B6using utf8), '\r')

?

[alexalexes]

Если и char(0xB6 using utf8), и char(0x00B6 using utf8), тогда вот так:

update oc_setting
set value = replace(replace(value, char(0xB6 using utf8), '\r\n'), char(0x00B6 using utf8), '\r\n')

[Tupic123]

alexalexes, Ииииии
SQL запрос:
update oc_setting set value = replace(replace(value, char(0xB6 using utf8), '\r\n'), char(0x00B6 using utf8), '\r\n')
Затронуто строк: 0

[alexalexes]

Допустим.
Этот запрос возвращает строки, где есть абзац.
В значении new_value есть изменения по сравнению с value?

select value,
          replace(replace(value, char(0xB6 using utf8), '\r\n'), char(0x00B6 using utf8), '\r\n') as new_value
from oc_setting
where value like concat('%', char(0xB6 using utf8), '%')
       or value like concat('%', char(0x00B6 using utf8), '%')

[Tupic123]

alexalexes, Нет, + Warning: #1300 Invalid utf8 character string: 'B6'

[alexalexes]

А так есть предупреждение?
Есть изменения в new_value?

select value,
          replace(value, char(0x00B6 using utf8), '\r\n') as new_value
from oc_setting
where value like concat('%', char(0x00B6 using utf8), '%')

[Tupic123]

alexalexes, Warning: #1300 Invalid utf8 character string: 'B6'
Пробовал менять char, результат аналогичен, должен был об этом сказать

[alexalexes]

Предупреждение 1300 не критично, возможно, просто не тот символ ищется.
Если вывести голый:
select ASCII('¶')
то выдает 194 код.
Можно попробовать вот так:

select value,
          replace(value, char(ASCII('¶')), '\r\n') as new_value,
          ASCII('¶') as search_code_symbol
from oc_setting
where value like concat('%', char(ASCII('¶')), '%')

Но результат будет зависеть от того в какой кодировке соединение к базе.

[Tupic123]

alexalexes, Пусто, кодировка utf8_general_ci

[alexalexes]

Тогда нужно вернуться к этому запросу и немного его изменить:

select value,
          replace(value, char(0x00B6 using utf8), '\r\n') as new_value_1,
          replace(value, char(0xB6 using utf8), '\r\n') as new_value_2
from oc_setting
where value like concat('%', char(0xB6 using utf8), '%')
       or value like concat('%', char(0x00B6 using utf8), '%')

Выборка есть?
Будет ли разница между value, new_value_1 и new_value_2?

[Tupic123]

alexalexes, Нет, разницы нет

[Tupic123]

alexalexes, Мы можем с вами где то списаться, а потом выложить ответ сюда что бы не разводить тонну комментов?

[Tupic123]

alexalexes, Вообщем. Я нашел в sql файле эту строку - она выглядит точно как мне нужно. А после импорта - уже ломается. То есть в sql дампе все хорошо, а когда уже смотришь в ячейке - выглядит по другому. Настройки импорта самые стандартные...
Режим совместимости none кодировка utf-8