Как заменить каждый другой экземпляр определенного символа в строке MySQL?
Как заменить значение в столбце mysql запросом как, столбец options и его типа varchar(255)
От
id options
1 A|10|B|20|C|30
2 A|Positive|B|Negative
к
id options
1 A|10,B|20,C|30
2 A|Positive,B|Negative
Я делаю это по php, как это.
<?php
$str = "A|10|B|20|C|30";
$arr = explode("|",$str);
$newArr = array();
for($i=0;$i<count($arr);$i+=2){
if($arr[$i] && $arr[$i+1]){
$newArr[] = $arr[$i]."|".$arr[$i+1];
}
}
echo "Before:".$str."\n";
echo "After :".implode(",",$newArr);
?>
Поэтому вместо PHP я хочу сделать это в MySQL.
5 ответов
Вы должны рассмотреть возможность хранения ваших данных в нормализованной схеме. В вашем случае таблица должна выглядеть так:
| id | k | v |
|----|---|----------|
| 1 | A | 10 |
| 1 | B | 20 |
| 1 | C | 30 |
| 2 | A | Positive |
| 2 | B | Negative |
Эта схема более гибкая, и вы поймете, почему.
Так как же преобразовать данные в новую схему? Вам понадобится вспомогательная таблица, содержащая порядковые номера. Так как ваша колонка varchar(255) в нем можно хранить только 128 значений (+ 127 разделителей). Но давайте просто создадим 1000 номеров. Вы можете использовать любую таблицу с достаточным количеством строк. Но так как любой сервер MySQL имеет information_schema.columns стол, я буду им пользоваться.
drop table if exists helper_sequence;
create table helper_sequence (i int auto_increment primary key)
select null as i
from information_schema.columns c1
join information_schema.columns c2
limit 1000;
Мы будем использовать эти числа в качестве позиции значений в вашей строке, соединяя две таблицы.
Чтобы извлечь значение из строки с разделителями, вы можете использовать substring_index() функция. Значение в положении i будет
substring_index(substring_index(t.options, '|', i ), '|', -1)
В вашей строке у вас есть последовательность ключей, за которой следуют ее значения. Положение ключа является нечетным числом. Так что, если позиция ключа i, позиция соответствующего значения будет i+1
Чтобы получить количество разделителей в строке и ограничить наше объединение, мы можем использовать
char_length(t.options) - char_length(replace(t.options, '|', ''))
Запрос на сохранение данных в нормализованной форме будет выглядеть так:
create table normalized_table
select t.id
, substring_index(substring_index(t.options, '|', i ), '|', -1) as k
, substring_index(substring_index(t.options, '|', i+1), '|', -1) as v
from old_table t
join helper_sequence s
on s.i <= char_length(t.options) - char_length(replace(t.options, '|', ''))
where s.i % 2 = 1
Теперь беги select * from normalized_table и вы получите это:
| id | k | v |
|----|---|----------|
| 1 | A | 10 |
| 1 | B | 20 |
| 1 | C | 30 |
| 2 | A | Positive |
| 2 | B | Negative |
Так почему же этот формат лучший выбор? Помимо многих других причин, одна из них заключается в том, что вы можете легко преобразовать ее в старую схему с помощью
select id, group_concat(concat(k, '|', v) order by k separator '|') as options
from normalized_table
group by id;
| id | options |
|----|-----------------------|
| 1 | A|10|B|20|C|30 |
| 2 | A|Positive|B|Negative |
или в желаемый формат
select id, group_concat(concat(k, '|', v) order by k separator ',') as options
from normalized_table
group by id;
| id | options |
|----|-----------------------|
| 1 | A|10,B|20,C|30 |
| 2 | A|Positive,B|Negative |
Если вас не волнует нормализация, и вы просто хотите выполнить эту задачу, вы можете обновить таблицу с помощью
update old_table o
join (
select id, group_concat(concat(k, '|', v) order by k separator ',') as options
from normalized_table
group by id
) n using (id)
set o.options = n.options;
И брось normalized_table,
Но тогда вы не сможете использовать простые запросы, такие как
select *
from normalized_table
where k = 'A'
Посмотреть демо на rextester.com
Не используя хранимые процедуры, я бы сделал это в 2 этапа:
Вставьте запятую при втором появлении символа канала:
update options set options = insert(options, locate('|', options, locate('|', options) + 1), 1, ',');Вставьте оставшиеся запятые - выполните запрос N раз:
update options set options = insert(options, locate('|', options, locate('|', options, length(options) - locate(',', reverse(options)) + 1) + 1), 1, ',');где N =
select max(round(((length(options) - length(replace(options, '|', ''))) - 1 ) / 2) - 1) from options;(или не беспокойтесь о подсчете и продолжайте выполнять запрос до тех пор, пока он не скажет "затронуты 0 строк")
Проверено с этим набором данных:
id options
1 A|10|B|20|C|30
2 A|Positive|B|Negative
3 A|10|B|20|C|30|D|40|E|50|F|60
4 A|Positive|B|Negative|C|Neutral|D|Dunno
результаты в:
id options
1 A|10,B|20,C|30
2 A|Positive,B|Negative
3 A|10,B|20,C|30,D|40,E|50,F|60
4 A|Positive,B|Negative,C|Neutral,D|Dunno
(Я объясню позже)
демонстрация
объяснение
Это можно было бы относительно легко решить, если бы только в MySQL была функция замены регулярных выражений, но, к сожалению, этого нет. Так что я написал один - см. Этот пост в блоге. Здесь требуется "расширенная версия", чтобы позволить ей выполнить рекурсивную замену в найденном совпадении для замены. Тогда можно использовать следующий относительно простой SQL:
SQL (код функции опущен для краткости)
SELECT id,
options AS `before`,
reg_replace(options,
'\\|.*\\|', -- 2 pipe symbols with any text in between
'\\|$', -- Replace the second pipe symbol
',', -- Replace with a comma
FALSE, -- Non-greedy matching
2, -- Min match length = 2 (2 pipe symbols)
0, -- No max match length
0, -- Min sub-match length = 1 (1 pipe symbol)
0 -- Max sub-match length = 1 (1 pipe symbol)
) AS `after`
FROM tbl;
Вы можете сделать, создав функцию
CREATE FUNCTION doiterate(str TEXT, i INT, next INT, isp TINYINT(1))
RETURNS TEXT
BEGIN
myloop: LOOP
IF next = 0 THEN
LEAVE myloop;
END IF;
IF isp = TRUE THEN
set str = insert(str, i, 1, ',');
set isp = FALSE;
set i = next;
set next = locate('|', str, i + 1);
ITERATE myloop;
ELSE
set isp = TRUE;
set i = next;
set next = locate('|', str, i + 1);
ITERATE myloop;
END IF;
LEAVE myloop;
END LOOP;
return str;
END;
и называя это так:
SELECT t.`column`,
@loc := locate('|', t.`column`) as position,
@next := locate('|', t.`column`, @loc +1) as next,
@isp := 0 is_pipe,
@r := doiterate(t.column, @loc, @next, @isp) as returnstring
from test t;
Я полагаю, вы будете достаточно умны, чтобы
- изменить имя таблицы и столбца
- вставьте это в запрос на обновление
Вы можете изменить @isp:= на 1, если я получил неправильное изменение канала / комы (я предполагал, что второй канал должен быть заменен на кому)
Хм, я думаю, что вы пытаетесь сделать что-то вроде этого
SELECT GROUP_CONCAT(CONCAT(options,",") SEPARATOR "|") FROM Table.name;
Я кратко объясняю, беру для каждой строки результат и объединяю "," и объединяю всю строку с разделителем "|". Вам придется изменить Table.name на имя вашей таблицы
Если вы хотите объединить еще одно значение, например A,B,C (вы не объяснили, откуда берется значение ABC, скажем, ValueWhereABCisComingFrom):
SELECT GROUP_CONCAT(CONCAT(ValueWhereABCisComingFrom,"|",options) SEPARATOR ",") FROM Table.name;
если мой стол такой:
id | ValueWhereABCisComingFrom | options
0 | A | 10
1 | B | 20
2 | C | 30
У вас будет что-то вроде этого:
A|10,B|20,C|30
РЕДАКТИРОВАТЬ 1
Там нет никакого способа сделать это в этом случае. В MySQL нет такой функции, как preg_replace. Все, что вы можете сделать, это заменить все "|" лайк
SELECT Replace(options, '|', ',') AS P
FROM `docs`;
В MariaDB есть такая функция, так что вы можете попытаться перейти с одной базы на другую. Но только с MYSQL, никак:/