Да, я в курсе, что такое “Многие ко многим”..

Иногда приходится сталкиваться с ситуациями, когда данные хранятся в таблице в “неудобной” для работы форме - ID-шники лежат в одном поле через запятую.. Как и почему - в подробности вдаваться не буду.. думаю, у тех, кто столкнулся с подобной ситуацией вопросов не будет. Как бы получше “развернуть” эту конструкцию в привычную таблицу многие-ко-многим, чтобы работать со связанными таблицами было удобнее (и быстрее - ведь поиск по подстроке намного медленнее, чем по индексу).

В любом случае, разбирать колонку со значениями через запятую нам придётся.. однако, лучше и правильнее сделать это один раз, чем при каждом запросе. Рассмотрим на примере работу с where field_1 IN (`field`)

Ситуация следующая.. есть, к примеру, таблица

CREATE TABLE IF NOT EXISTS `prefix_ids` (
`id` bigint(20) NOT NULL,
`ids` varchar(255) NOT NULL,
PRIMARY KEY (`id`))

В которой id - целое, первичный ключ.. а ids - “связанные id-шники через запятую..” т.е. к примеру, данные могут выглядеть.

INSERT IGNORE INTO `prefix_ids` VALUES
(1, '2,3'),
(2, '3,4'),
(3, '1'),
(4, '1,2,3'),

Задача - получить ту-самую таблицу “многие-ко-многим” (это отдельная таблица вроде связей product-product или product-category)

CREATE TABLE IF NOT EXISTS `prefix_id_id` (
`id1` bigint(20) NOT NULL,
`id2` bigint(20) NOT NULL,
PRIMARY KEY (`id1`,`id2`))

Казалось бы.. ситуация простая.. запрос с WHERE id in (1,2,3)

INSERT INTO `prefix_id_id` (id1,id2) SELECT p1.id,p2.id
FROM prefix_ids p1 INNER JOIN prefix_ids p2 ON p2.id IN(p1.ids)

Запрос отрабатывает довольно быстро, однако количество рядов получается намного меньше ожидаемого..
Дело в том, что происходит приведение типов.. т.е. в выражении IN (’СТРОКА’) используется числовое значение строки.. - до первой запятой.

Как вариант - использовать LIKE ‘%5%’ .. т.е. целиком запрос будет выглядеть
INSERT INTO `prefix_id_id` (id1,id2) SELECT p1.id,p2.id
FROM prefix_ids p1 INNER JOIN prefix_ids p2 ON p1.ids LIKE CONCAT(’%', p2.id,’%')

Естественно. ни о каких индексах в этой ситуации речи быть не может. Но.. что такое.. возможны ситуации, когда появляются “лишние” строчки.. К примеру, для двузначных чисел… (3, ‘1,21′) отработает LIKE ‘%2%’..

Можно дополнить ids запятыми слева-справа и их же использовать в качестве ограничителей в поиске (использовать лучше “раздельно”.. т.е. сначала дополнить ids запятыми, а затем выполнять “сложный” запрос)
INSERT INTO `prefix_id_id` (id1,id2) SELECT p1.id,p2.id
FROM prefix_ids p1 INNER JOIN prefix_ids p2 ON CONCAT(’,'p1.ids,’,') LIKE CONCAT(’%,’, p2.id,’,%’);

Ещё один вариант - использовать функцию FIND_IN_SET(str,strlist), которая возвращает номер элемента в списке (список через запятую)

Однако, наиболее оптимальный, быстрый и универсальный вариант всё-таки оказался самым очевидным, но “в два запроса” и вне базы (вариант с созданием хранимой процедуры не рассматривался, Т.к. на ряде хостингов имеется ограничение на использование хранимых процедур).

Первым запросом получаем все нужные для обработки элементы из таблицы.. для каждого из них, если требуется готовим массив смежных элементов и формируем строку вида $query = (1,2),(1,3),… которую по завершении цикла вставляем в нашу таблицу одним (или несколькими.. с разбивкой по 1000 элементов, например, если строка получилась достаточно длинной)

INSERT INTO `prefix_id_id` VALUES $query

Метки: mysql

Опубликовано Пятница, Август 17, 2012 в 16:58 в следующих категориях: Без рубрики. Вы можете подписаться на комментарии к этому сообщению через RSS 2.0. Вы можете оставить комментарий. Пинг отключен.