ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Заменить в ячейке все значения на значения из другого столбца
15 сообщений из 15, страница 1 из 1
Заменить в ячейке все значения на значения из другого столбца
    #39939526
_DenSeo_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_DenSeo_
Гость
Здравствуйте!

Помогите, пожалуйста, решить такую задачу.

Есть таблица contents, в которой 1,5 млн строк.

Структура таблицы:

Имя Тип Сравнение id int(11) utf8_general_ciname varchar(250) utf8_general_ciurl varchar(250) utf8_general_ciHEX varchar(250) utf8_general_ciHEX_new varchar(250) utf8_general_cirel_gall text utf8_general_ci

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

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

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

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

Или, перефразирую:
Нужно заменить в ячейке столбца rel_gall все значения HEX на значения из столбца HEX_new.

Ниже покажу наглядно цветом на четвертой строке какое значение в колонке rel_gall на какое должно измениться.
(Примечание: значения в столбце rel_gall разделил точкой с запятой, чтобы корректно вывести тут таблицу, в оригинале разделитель - запятая)

Было:
id name url HEX HEX_new rel_gall1 100-dollar-bill 100 Png Download 100-dollar-bill-100-png-download xRbhmw g3b1p5j7f3g1e1 xRbhmw;iiwmRTJ;imhhwm;imixJT;bbJmxT;iiwmRbT;imibJo2 My Name's Jess Parkinson And I'm A Yoga Instructor my-names-jess-parkinson-and-im-a-yoga bbJmxT n0m3j7v6h1w8c5 bbJmxT;oimo;iTxJRTx;mboxhb;oRbmh;hTwowo;iiwmRTJ3 Supreme 100 Dollar Bill Trucker Jacket Png Download supreme-100-dollar-bill-trucker-jacket-png-download iTohwbT q4o2p2o0o5l3p6 iTohwbT;iiTiobo;xRbhmw;hiiwRRJ;imibJo;hxThwxw;bbJmxT4 100 Dollar Bill House - 100 Dollar Bill HD Png Download 100-dollar-bill-house-100-dollar-bill-hd hiiwRRJ z6k0d2f1c0t6k6 xRbhmw;hiiwRRJ;bbJmxT;iTohwbT;imibJo;hxThwxw;imixJT5 Money In Hand - 100 Dollar Bill HD Png Download money-in-hand-100-dollar-bill-hd-png imibJo q7k4e3m1n1y3h1 mhihwJ;hihJJoh;imibJo;hxhoRmb;oimo;xbxJJh;mhwJmi

Стало:
id name url HEX HEX_new rel_gall1 100-dollar-bill 100 Png Download 100-dollar-bill-100-png-download xRbhmw g3b1p5j7f3g1e1 xRbhmw;iiwmRTJ;imhhwm;imixJT;bbJmxT;iiwmRbT;imibJo2 My Name's Jess Parkinson And I'm A Yoga Instructor my-names-jess-parkinson-and-im-a-yoga bbJmxT n0m3j7v6h1w8c5 bbJmxT;oimo;iTxJRTx;mboxhb;oRbmh;hTwowo;iiwmRTJ3 Supreme 100 Dollar Bill Trucker Jacket Png Download supreme-100-dollar-bill-trucker-jacket-png-download iTohwbT q4o2p2o0o5l3p6 iTohwbT;iiTiobo;xRbhmw;hiiwRRJ;imibJo;hxThwxw;bbJmxT4 100 Dollar Bill House - 100 Dollar Bill HD Png Download 100-dollar-bill-house-100-dollar-bill-hd hiiwRRJ z6k0d2f1c0t6k6 g3b1p5j7f3g1e1;z6k0d2f1c0t6k6;n0m3j7v6h1w8c5;q4o2p2o0o5l3p6;q7k4e3m1n1y3h1;hxThwxw;imixJT5 Money In Hand - 100 Dollar Bill HD Png Download money-in-hand-100-dollar-bill-hd-png imibJo q7k4e3m1n1y3h1 mhihwJ;hihJJoh;imibJo;hxhoRmb;oimo;xbxJJh;mhwJmi

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

Заранее благодарен за помощь!
...
Рейтинг: 0 / 0
20.03.2020, 13:53
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39939542
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Это итерационная задача, одним запросом не решить (ну то есть можно, но уж больно тяжёлый он получится).

Рисуй хранимую процедуру.
...
Рейтинг: 0 / 0
20.03.2020, 14:15
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39939547
_DenSeo_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_DenSeo_
Гость
Akina
Рисуй хранимую процедуру.


К сожалению, я не потяну.
Может быть Вы поможете? Готов отблагодарить...
...
Рейтинг: 0 / 0
20.03.2020, 14:22
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39939553
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Версия-то MySQL хоть какая?

И ещё - почему, скажем, "bbJmxT" заменено в rel_gall записи 4, но НЕ заменено в записях 2 и 3?
...
Рейтинг: 0 / 0
20.03.2020, 14:33
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39939573
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
В принципе вот тебе решение для версии 8:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
WITH RECURSIVE
cte1 AS ( SELECT id, 
                 name,
                 url,
                 HEX_old, 
                 HEX_new, 
                 rel_gall, 
                 1 level
          FROM contents
          UNION ALL
          SELECT cte1.id, 
                 cte1.name, 
                 cte1.url, 
                 cte1.HEX_old, 
                 cte1.HEX_new, 
                 REPLACE(cte1.rel_gall, contents.HEX_old, contents.HEX_new), 
                 1 + cte1.level
          FROM contents, cte1
          WHERE FIND_IN_SET(contents.HEX_old, cte1.rel_gall) ),
cte2 AS ( SELECT id, MAX(level) level
          FROM cte1
          GROUP BY id )
SELECT DISTINCT cte1.id, 
                cte1.name, 
                cte1.url, 
                cte1.HEX_old, 
                cte1.HEX_new, 
                cte1.rel_gall
FROM cte1
NATURAL JOIN cte2;


Но оно так, чисто поржать - потому как даже на твоём куцем примере из 5 записей CTE1 генерит промежуточный набор из 672 записей. А на 1,5 млн. оно просто тихо скончается, не родившись.
...
Рейтинг: 0 / 0
20.03.2020, 14:58
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39939583
_DenSeo_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_DenSeo_
Гость
Akina
Версия-то MySQL хоть какая?

И ещё - почему, скажем, "bbJmxT" заменено в rel_gall записи 4, но НЕ заменено в записях 2 и 3?


MySQL 8

Я просто для примера на одной строке разобрал, поэтому в других строках не трогал
...
Рейтинг: 0 / 0
20.03.2020, 15:09
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39939587
_DenSeo_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_DenSeo_
Гость
Akina
В принципе вот тебе решение для версии 8:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
WITH RECURSIVE
cte1 AS ( SELECT id, 
                 name,
                 url,
                 HEX_old, 
                 HEX_new, 
                 rel_gall, 
                 1 level
          FROM contents
          UNION ALL
          SELECT cte1.id, 
                 cte1.name, 
                 cte1.url, 
                 cte1.HEX_old, 
                 cte1.HEX_new, 
                 REPLACE(cte1.rel_gall, contents.HEX_old, contents.HEX_new), 
                 1 + cte1.level
          FROM contents, cte1
          WHERE FIND_IN_SET(contents.HEX_old, cte1.rel_gall) ),
cte2 AS ( SELECT id, MAX(level) level
          FROM cte1
          GROUP BY id )
SELECT DISTINCT cte1.id, 
                cte1.name, 
                cte1.url, 
                cte1.HEX_old, 
                cte1.HEX_new, 
                cte1.rel_gall
FROM cte1
NATURAL JOIN cte2;


Но оно так, чисто поржать - потому как даже на твоём куцем примере из 5 записей CTE1 генерит промежуточный набор из 672 записей. А на 1,5 млн. оно просто тихо скончается, не родившись.


Спасибо, за такой оперативный ответ.
Еще не протестировал, сразу вопрос, в SELECT перечисляются все столбцы в таблице. Тут в примере шесть столбцов, а по факту у меня в таблице их около 15 будет. Мне их все нужно так же перечислять?


Akina
Но оно так, чисто поржать - потому как даже на твоём куцем примере из 5 записей CTE1 генерит промежуточный набор из 672 записей. А на 1,5 млн. оно просто тихо скончается, не родившись.


А что же длать?
...
Рейтинг: 0 / 0
20.03.2020, 15:15
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39939642
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
_DenSeo_
А что же длать?
На самом деле - менять структуру хранения и нормализовать данные. Уходить от CSV в сторону дополнительной связующей таблицы.
...
Рейтинг: 0 / 0
20.03.2020, 17:25
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39939762
_DenSeo_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_DenSeo_
Гость
Akina
_DenSeo_
А что же длать?
На самом деле - менять структуру хранения и нормализовать данные. Уходить от CSV в сторону дополнительной связующей таблицы.


Я тоже думал о нормализации...
Если я правильно понимаю, то нужно разбить ячейку rel_gall на строки по одному значению, и связующим звеном тут будет колонка HEX.
То есть, на группу строк со значениями из одной ячейки будет идти в колонке рядом один и тот же HEX, который был в строке с ячейкой до разбиения.
И это все будет в отдельной таблице.

Изображу графически.

Таблица contents:

id name url HEX HEX_new rel_gall1 100-dollar-bill 100 Png Download 100-dollar-bill-100-png-download xRbhmw g3b1p5j7f3g1e1 xRbhmw;iiwmRTJ;imhhwm2 My Name's Jess Parkinson And I'm A Yoga Instructor my-names-jess-parkinson-and-im-a-yoga bbJmxT n0m3j7v6h1w8c5 bbJmxT;oimo;iTxJRTx3 Supreme 100 Dollar Bill Trucker Jacket Png Download supreme-100-dollar-bill-trucker-jacket-png-download iTohwbT q4o2p2o0o5l3p6 iTohwbT;iiTiobo;xRbhmw

Новая таблица с разбитыми по строкам ячейками столбца rel_gall, назовем ее related_images:

id HEX rel_img1 xRbhmw xRbhmw2 xRbhmw iiwmRTJ3 xRbhmw imhhwm4 bbJmxT bbJmxT5 bbJmxT oimo6 bbJmxT iTxJRTx7 iTohwbT iTohwbT8 iTohwbT iiTiobo9 iTohwbT xRbhmw

Так должно быть? Или я что то упустил или не понимаю...
...
Рейтинг: 0 / 0
20.03.2020, 22:50
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39939869
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
_DenSeo_
Так должно быть?
В исходной таблице первичный ключ - разве HEX? по-моему, id - вот он и должен быть рефералом в связующей таблице. Плюс должна быть таблица УНИКАЛЬНЫХ HEX - она будет вторым рефералом.
...
Рейтинг: 0 / 0
21.03.2020, 14:56
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39940242
_DenSeo_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_DenSeo_
Гость
Akina
_DenSeo_
Так должно быть?
В исходной таблице первичный ключ - разве HEX? по-моему, id - вот он и должен быть рефералом в связующей таблице.


Первичный id. Просто HEX это идентификатор страницы, а данные в rel_gall - это значения релевантных ей страниц, которые выводятся в блоке Related на этой странице. И у себя в CMS я выводил этот блок с картинками и ссылками на related страницы запросом:
Код: plsql
1.
SELECT * FROM `contents` WHERE `HEX` IN ('xRbhmw', 'iiwmRTJ', 'imhhwm')


Поэтому и думал, что HEX будет связующим...

Тогда два вопроса по новой доп. таблице:
1. Колонка HEX из таблицы contents в этой новой доп. таблице не нужна?
2. Получается нужна колонка contents_id со значением id которое соответствует группе значений из одной ячейки rel_gall? Это и будет реферал в связующей таблице...

То есть новая доп. таблица related_images должна выглядеть так, верно?
id contents_id rel_img1 1 xRbhmw2 1 iiwmRTJ3 1 imhhwm4 2 bbJmxT5 2 oimo6 2 iTxJRTx7 3 iTohwbT8 3 iiTiobo9 3 xRbhmw

Akina
Плюс должна быть таблица УНИКАЛЬНЫХ HEX - она будет вторым рефералом.


Сделал таблицу уникальных HEX - hex_uniq.
Вставил в HEX_old столбец HEX из таблицы contents, удалил дубликаты и обновил значения id, AUTO_INCREMENT, чтобы по порядку шло.
И в этой таблице в HEX_new сгенерировал новый, уникальный HEX. Генерить его в таблице contents было ошибкой, это нарушает логику, там же повторяются строки со значениями HEX. По столбцу HEX туда подставлю новые значения HEX_new из таблицы уникальных HEX, чтобы сохранить логику...

Вот таблица уникальных HEX - hex_uniq. Скажите, она правильно выглядит?
idHEX_oldHEX_new1bbbr9t2j8ah2bbbbbtuhk6vk3bbbbbowjsza95q4bbbbhww0342rau5bbbbih5ws0fsc46bbbbii8l0zkna87bbbbiTzyjl16hm8bbbbJJbh8f6an49bbbbJofej72gvo10bbbbJRh3q30ial
...
Рейтинг: 0 / 0
23.03.2020, 13:09
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39940244
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
_DenSeo_
Колонка HEX из таблицы contents в этой новой доп. таблице не нужна?
Конечно, нет. Там будет только пара (ИД страницы - ИД релевантной страницы). Т.е. две ссылки на первичный ключ таблицы страниц.
...
Рейтинг: 0 / 0
23.03.2020, 13:16
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39940264
_DenSeo_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_DenSeo_
Гость
Akina
_DenSeo_
Колонка HEX из таблицы contents в этой новой доп. таблице не нужна?
Конечно, нет. Там будет только пара (ИД страницы - ИД релевантной страницы). Т.е. две ссылки на первичный ключ таблицы страниц.


Немного не понимаю...
ИД страницы - понятно, это id из таблицы страниц.
А откуда взять ИД релевантной страницы? у меня же есть только релевантные HEX?
...
Рейтинг: 0 / 0
23.03.2020, 14:14
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39940278
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
HEX - они уникальны? т.е. формально пофиг, связывать по ним или по ID. Но ID числовой, по нему удобнее (да и короче, и быстрее).
...
Рейтинг: 0 / 0
23.03.2020, 15:08
| Ответить | Цитировать | Написать  
Заменить в ячейке все значения на значения из другого столбца
    #39940292
_DenSeo_
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
_DenSeo_
Гость
Akina
HEX - они уникальны?


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

По поводу повторов HEX в таблице страниц. Я сейчас задумался, может я и тут неправильно храню данные???... или правильно...
Поясню. Повторы вызваны тем, что один и тот же HEX (одна и та же страница) может относиться одновременно к нескольким группам.

Вот выборка из таблицы страниц:
Код: plsql
1.
SELECT `id`, `name`, `url`, `gp_id`, `gp_url`, `HEX`, `HEX_new`, `rel_gall` FROM `contents` WHERE `HEX` LIKE 'xRbhmw'



idnameurlgp_idgp_urlHEXHEX_newrel_gall1100-dollar-bill 100 Png Download100-dollar-bill-100-png-download1100-dollar-billxRbhmwg3b1p5j7f3g1e1xRbhmw;iiwmRTJ;imhhwm;imixJT;bbJmxT416883Archaicawful Dollar Bill Clip Art Bills Graphic Libraryarchaicawful-dollar-bill-clip-art-bills-graphic-library3dollarxRbhmwg3b1p5j7f3g1e1xRbhmw;iiwmRTJ;imhhwm;imixJT;bbJmxT417611Archaicawful Dollar Bill Clip Art Bills Graphic Libraryarchaicawful-dollar-bill-clip-art-bills-graphic-library4dollar billxRbhmwg3b1p5j7f3g1e1xRbhmw;iiwmRTJ;imhhwm;imixJT;bbJmxT752291Archaicawful Dollar Bill Clip Art Bills Graphic Libraryarchaicawful-dollar-bill-clip-art-bills-graphic-library800libraryxRbhmwg3b1p5j7f3g1e1xRbhmw;iiwmRTJ;imhhwm;imixJT;bbJmxT

Тут gp_id соответствует id группы в таблице групп - groups, и gp_url соответствует gp_url в той же таблице groups.

Может нужна еще какая-нибудь дополнительная таблица?
Или тут все правильно с хранением и структурой базы данных в целом?
...
Рейтинг: 0 / 0
23.03.2020, 16:09
| Ответить | Цитировать | Написать  
15 сообщений из 15, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Заменить в ячейке все значения на значения из другого столбца
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=24&tid=1828684]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 44ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 1ms
others: 13ms
total:  147ms
созд. / загр.: 147ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]