MySQL выбирает данные с разделителями

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

id_group     Name
-----------------------
1            2|4|5
2            3|4|6
3            1|2

А в другой таблице у меня есть список людей, которые могут принадлежать к одной или нескольким группам

id_names     Names
-----------------------
1            Jack
2            Joe
3            Fred
4            Mary
5            Bill

Я хотел бы выполнить выбор данных группы, в результате чего появится одно поле, содержащее список имен с разделителями-запятыми или пробелами, например, из первой строки группы над "Джо Фред Билл"

Я рассмотрел использование функции для разделения строки с разделителями, а также просмотрел подзапросы, но объединение результатов подзапросов быстро становится огромным.

Спасибо!


mysql sql select delimited
person Commsman    schedule 13.03.2019    source источник
comment
Лучшим решением было бы изменить структуру вашей таблицы и даже не пытаться приступить к этому.   -  person Shadow    schedule 14.03.2019
comment
Поскольку ваши данные разделены вертикальной чертой, а не запятыми, вы не можете использовать FIND_IN_SET(). Это самое распространенное решение, но не в вашем случае. Я понимаю, что вы унаследовали этот проект, и, вероятно, не вам его переделывать, но вы можете проклясть своего предшественника за то, что он оставил вам плохо спроектированное приложение.   -  person Bill Karwin    schedule 14.03.2019
comment
@billkarwin это такая натяжка, чтобы заменить трубы запятыми?   -  person Strawberry    schedule 14.03.2019
comment
@Strawberry, FIND_IN_SET() не поддерживает альтернативный разделитель. Он поддерживает только запятые.   -  person Bill Karwin    schedule 14.03.2019
comment
кроме того, это может быть не просто база данных, а целая система, работающая на множестве, разделенном конвейером. замена запятыми в этом месте может сломать что-то, о чем мы не знаем   -  person Kai Qing    schedule 14.03.2019

Ответы (2)


arrow_upward
2
arrow_downward

Как следует из комментария Strawberry выше, есть способ сделать это, но это так уродливо. Это все равно, что переделывать дорогую кухню с помощью клейкой ленты. Вы должны чувствовать обиду на человека, который спроектировал базу данных таким образом.

SELECT g.id_group, GROUP_CONCAT(n.Names SEPARATOR ' ') AS Names
FROM groups AS g JOIN names AS n
  ON FIND_IN_SET(n.id_names, REPLACE(g.Name, '|', ','))
GROUP BY g.id_group;

Вывод, проверенный на MySQL 5.6:

+----------+---------------+
| id_group | Names         |
+----------+---------------+
|        1 | Joe Mary Bill |
|        2 | Fred Mary     |
|        3 | Jack Joe      |
+----------+---------------+

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

Лучшее решение — создать третью таблицу, в которой вы храните каждого отдельного члена группы в отдельной строке. То есть несколько строк на группу.

CREATE TABLE group_name (
  id_group INT NOT NULL,
  id_name INT NOT NULL,
  PRIMARY KEY (id_group, id_name)
);

Затем вы можете запросить более простым способом, и у вас есть возможность создавать индексы, чтобы сделать запрос очень быстрым.

SELECT id_group, GROUP_CONCAT(names SEPARATOR ' ') AS names
FROM groups
JOIN group_name USING (id_group)
JOIN names USING (id_name)
person Bill Karwin    schedule 13.03.2019
comment
Я рад, что кто-то использует версию MySQL почти такую ​​же старую, как моя :-p - person Strawberry; 14.03.2019
comment
Ну, технически это не EOL до февраля 2021 года. - person Bill Karwin; 14.03.2019
comment
Спасибо, это работает для меня. Я также ценю комментарии других относительно дизайна базы данных cr@p, хотя кажется немного бессмысленным говорить об этом, когда уже было сказано, что это было унаследовано и, следовательно, не в моей власти изменить. - person Commsman; 14.03.2019
comment
Мы все оказывались в таком положении в тот или иной момент. - person Bill Karwin; 14.03.2019

arrow_upward
2
arrow_downward

Тень правильная. Ваша основная проблема заключается в плохом дизайне отношений в базе данных. Обычно такие бизнес-задачи разрабатываются как так называемое отношение M:N (M к N). Для этого вам понадобятся 3 таблицы:

  • первая таблица - groups, в которой есть поле GroupId с первичным ключом и читаемое поле name (например, 'group1' или что-то еще)

  • вторая таблица people выглядит точно так, как вы показали выше. (не забудьте указать первичный ключ в поле PeopleId и здесь)

  • третья таблица — это промежуточная таблица с именем GroupMemberships. У этого есть 2 поля GroupId и PeopleId. Эта таблица связывает первые две таблицы друг с другом и отмечает отношение M:N. В одной группе может быть от 1 до N участников, а люди могут быть членами от 1 до M групп.

Наконец, просто соедините таблицы в select иaggregate:

SELECT 
    g.Name,
    GROUP_CONCAT(p.Name ORDER BY p.PeopleId DESC SEPARATOR ';') AS Members
FROM
    Groups AS g
    INNER JOIN GroupMemberships AS gm ON g.GroupId = gm.GroupId
    INNER JOIN people AS p ON gm.PeopleId = p.PeopleId
GROUP BY g.Name;
person Tarek Salha    schedule 13.03.2019