Mysql where

Так, я в курсі, що таке "Багато до багатьох" ..

Іноді доводиться стикатися з ситуаціями, коли дані зберігаються в таблиці в "незручною" для роботи формі - 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

Схожі статті