В этой статье блога мы рассмотрим несколько методов объединения таблиц MySQL, когда один из столбцов содержит идентификаторы, разделенные запятыми. Этот сценарий часто возникает при работе с реляционными базами данных, и мы рассмотрим пять эффективных подходов к решению этой проблемы. Мы познакомим вас с каждым методом, предоставив примеры кода и объяснив их плюсы и минусы. Итак, приступим!

Метод 1: функция FIND_IN_SET
Функция FIND_IN_SET в MySQL позволяет нам искать значение в списке, разделенном запятыми. Мы можем использовать эту функцию для объединения таблиц на основе совпадающих идентификаторов.

SELECT *
FROM table1
JOIN table2 ON FIND_IN_SET(table1.id, table2.ids_column) > 0;

Метод 2: оператор REGEXP
Другой подход заключается в использовании оператора REGEXP, который позволяет нам выполнять сопоставление регулярных выражений. Мы можем создать шаблон регулярного выражения, соответствующий идентификаторам в текстовом столбце.

SELECT *
FROM table1
JOIN table2 ON table2.ids_column REGEXP CONCAT('[[:<:]]', table1.id, '[[:>:]]');

Метод 3: оператор LIKE с CONCAT
Оператор LIKE можно использовать для сопоставления шаблонов в строковом столбце. Объединив идентификаторы с соответствующими разделителями, мы можем сравнить их с текстовым столбцом.

SELECT *
FROM table1
JOIN table2 ON CONCAT(',', table2.ids_column, ',') LIKE CONCAT('%,', table1.id, ',%');

Метод 4: временная таблица
Мы можем создать временную таблицу для хранения идентификаторов, разделенных запятыми, в виде отдельных строк. Затем мы можем выполнить обычную операцию соединения между таблицами.

CREATE TEMPORARY TABLE temp_ids (id INT);
INSERT INTO temp_ids (id)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ids_column, ',', numbers.n), ',', -1) AS id
FROM table2
JOIN (
    SELECT 1 + a.n + b.n * 10 AS n
    FROM
        (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a,
        (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
    ORDER BY n
) AS numbers
WHERE CHAR_LENGTH(ids_column) - CHAR_LENGTH(REPLACE(ids_column, ',', '')) >= numbers.n - 1;
SELECT *
FROM table1
JOIN temp_ids ON table1.id = temp_ids.id;

Метод 5: хранимая процедура
Для более сложных сценариев мы можем создать хранимую процедуру для разделения идентификаторов, разделенных запятыми, и объединения таблиц. Этот метод обеспечивает гибкость и возможность повторного использования.

DELIMITER //
CREATE PROCEDURE join_tables_with_ids()
BEGIN
    -- Splitting IDs and inserting into a temporary table
    CREATE TEMPORARY TABLE temp_ids (id INT);
    INSERT INTO temp_ids (id)
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ids_column, ',', numbers.n), ',', -1) AS id
    FROM table2
    JOIN (
        SELECT 1 + a.n + b.n * 10 AS n
        FROM
            (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a,
            (SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        ORDER BY n
    ) AS numbers
    WHERE CHAR_LENGTH(ids_column) - CHAR_LENGTH(REPLACE(ids_column, ',', '')) >= numbers.n - 1;

    -- Joining tables using the temporary table
    SELECT *
    FROM table1
    JOIN temp_ids ON table1.id = temp_ids.id;

    -- Drop the temporary table
    DROP TEMPORARY TABLE IF EXISTS temp_ids;
END //
DELIMITER ;
-- Call the stored procedure
CALL join_tables_with_ids();

В этой статье мы рассмотрели пять эффективных методов объединения таблиц MySQL, когда один из столбцов содержит идентификаторы, разделенные запятыми. Каждый метод имеет свои преимущества и подходит для разных сценариев. Используя функцию FIND_IN_SET, оператор REGEXP, оператор LIKE с CONCAT, временные таблицы или хранимые процедуры, вы можете эффективно справиться с этой ситуацией. Выберите метод, который лучше всего соответствует вашим конкретным требованиям и структуре базы данных. Приятного кодирования!