MySQL - Сопоставить каждое значение из ячейки одного столбца со значениями в другом и заменить его значением из соседнего столбца

Question

Есть таблица contents, которая содержит 13 столбцов и 1,5 млн строк.

Столбец rel_gall (text, utf8_general_ci) содержит в ячейках список значений с разделителем запятая.
Ячейка может содержать от нуля (ячейка может быть пустой) до тридцати значений с разделителем запятая. Значения между разделителем могут содержать буквы разного регистра и цифры.
(пример: cRbkmw,wiwvRDJ,ivhswa,iqexFT,hjJmkT,uiiwRnT,iklbJr,ibjTof,lvibeib,imiJoh).

Столбец HEX (varchar(250), utf8_general_ci) содержит в ячейках значение, которое встречается в ячейках столбца rel_gall. Столбец HEX не уникальный, строки повторяются. Из рандомного набора значений столбца HEX и состоит ячейка столбца rel_gall.

Столбец HEX_new (varchar(250), utf8_general_ci) содержит в ячейках новое значение HEX. На эти значения мы будем обновлять значения в столбце rel_gall. Столбец HEX_new не уникальный, строки повторяются.

Нужно сопоставить каждое значение из ячейки столбца rel_gall со значениями в ячейках столбца HEX (достаточно первого совпадения) и заменить значение из ячейки rel_gall на новое значение из соседнего столбца HEX_new.

Пример:

Строка id 4. Значение в столбце rel_gall xRbhmw должно замениться на g3b1p5j7f3g1e1, hiiwRRJ -> z6k0d2f1c0t6k6, bbJmxT -> n0m3j7v6h1w8c5, iTohwbT -> q4o2p2o0o5l3p6, imibJo -> q7k4e3m1n1y3h1 и т.д.

Answer 1

Ответ полностью обновлен после обсуждения.

Для начала лучше сделать копию таблицы contents, на всяк случай... если что-то пойдет не так. Потом сотрете.

Создаем временную таблицу.

CREATE TABLE `__temp` (
  `id` int(11) NOT NULL,
  `tHEX` varchar(250) NOT NULL,
  `tHEX_new` varchar(250) NOT NULL,
  PRIMARY KEY (`id`,`tHEX`,`tHEX_new`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Вносим в нее данные (может занять время из-за FIND_IN_SET())

INSERT INTO __temp (id, tHEX, tHEX_new)
SELECT c.id, c2.HEX, c2.HEX_new 
FROM contents c JOIN contents c2 ON FIND_IN_SET(c2.HEX, c.rel_gall)>0;

Обновляем источник

UPDATE contents c
JOIN (SELECT id, GROUP_CONCAT(tHEX_new) as hexes FROM __temp GROUP BY id) gr ON c.id=gr.id
SET c.rel_gall=gr.hexes;

Кстати, если так все сделать, то из rel_gall уйдут все строчки, которые не упомянуты в contents.HEX. Насколько я понял, в Вашем случае это как раз плюс. :)

БЛОГ НА HUSL

MySQL - Сопоставить каждое значение из ячейки одного столбца со значениями в другом и заменить его значением из соседнего столбца

Ответы (1 шт):