MySql — Избегайте SET () с помощью соединения и внешних ключей
Так вот в чем проблема:
У меня есть таблица «Члены» с членами и их атрибутами (имя, день рождения, почта и т. Д.)
Эти участники могут принадлежать к группам (скажем, есть 3 группы), от ни одной до всех. И эти группы упоминаются в таблице («Группы»), поэтому я могу добавлять / удалять / изменять их, как я хочу.
SET() не похоже на решение, оно не совместимо с внешними ключами / справочной таблицей.
Итак, сначала я думал о TINYINT() столбец, который я использую как SET() : 111 (7) для всех групп, 000 (0) ни для кого, 001 (1) для 1-й группы, 010 (2) для 2-го и т. д. Но так как имена довольно сложные, это сбивает с толку и не намного совместимо с внешними ключами.
Я прочитал, что я должен сделать 3-ю таблицу «Члены группы» с memberID и groupID, чтобы объединить обе мои таблицы, но я не совсем понимаю, как это работает.
Я понимаю, что у меня будет таблица с идентификаторами участников и групп, например:
+----------+---------+
| memberID | groupID |
+----------+---------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
| 3 | 2 |
+----------+---------+
и в сочетании с перекрестком я могу получить то, что я хочу. Это правильно ? В противном случае кто-то может объяснить мне, как я должен делать?
Я уточнил, что в качестве окончательного результата (после sql request + php script) я хотел бы получить члена, его атрибуты и группы, к которым он принадлежит, в одной строке (как в случае с SET()), даже члены, которые не принадлежат ни к одной группе.
Решение
Если предположить,
drop table if exists mg;
drop table if exists m;
create table m (id int primary key, name varchar(3));
insert into m values
(1,'abc'),
(2,'def'),
(3,'ghi');
drop table if exists g;
create table g(id int primary key ,name varchar(3));
insert into g values
(1,'aaa'),
(2,'bbb'),
(3,'ccc');
create table mg
(memid int,grid int,
index fmid(memid,grid) ,
foreign key (memid) references m(id) on delete cascade,
foreign key (grid) references g(id) on delete cascade
);
insert into mg values
(1,1),(1,2),(1,3),
(2,1),(2,3);
Вы можете объединить 3 таблицы и получить результаты, используя group_concat или условное агрегирование.
MariaDB [sandbox]> select m.id,m.name, group_concat(g.name) groups
-> from m
-> join mg on mg.memid = m.id
-> join g on mg.grid = g.id
-> group by m.id,m.name;
+----+------+-------------+
| id | name | groups |
+----+------+-------------+
| 1 | abc | aaa,bbb,ccc |
| 2 | def | aaa,ccc |
+----+------+-------------+
2 rows in set (0.00 sec)
MariaDB [sandbox]>
MariaDB [sandbox]> select m.id,m.name,
-> max(case when g.id = 1 then g.name else '' end) as group1,
-> max(case when g.id = 2 then g.name else '' end) as group2,
-> max(case when g.id = 3 then g.name else '' end) as group3
-> from m
-> join mg on mg.memid = m.id
-> join g on mg.grid = g.id
-> group by m.id,m.name;
+----+------+--------+--------+--------+
| id | name | group1 | group2 | group3 |
+----+------+--------+--------+--------+
| 1 | abc | aaa | bbb | ccc |
| 2 | def | aaa | | ccc |
+----+------+--------+--------+--------+
2 rows in set (0.00 sec)
Если вы хотите, чтобы участники, не принадлежащие к какой-либо группе, изменили объединения на левые.
ariaDB [sandbox]> select m.id,m.name, group_concat(g.name) groups
-> from m
-> left join mg on mg.memid = m.id
-> left join g on mg.grid = g.id
-> group by m.id,m.name;
+----+------+-------------+
| id | name | groups |
+----+------+-------------+
| 1 | abc | aaa,bbb,ccc |
| 2 | def | aaa,ccc |
| 3 | ghi | NULL |
+----+------+-------------+
3 rows in set (0.00 sec)
MariaDB [sandbox]>
MariaDB [sandbox]> select m.id,m.name,
-> max(case when g.id = 1 then g.name else '' end) as group1,
-> max(case when g.id = 2 then g.name else '' end) as group2,
-> max(case when g.id = 3 then g.name else '' end) as group3
-> from m
-> left join mg on mg.memid = m.id
-> left join g on mg.grid = g.id
-> group by m.id,m.name;
+----+------+--------+--------+--------+
| id | name | group1 | group2 | group3 |
+----+------+--------+--------+--------+
| 1 | abc | aaa | bbb | ccc |
| 2 | def | aaa | | ccc |
| 3 | ghi | | | |
+----+------+--------+--------+--------+
3 rows in set (0.00 sec)
Другие решения
Я чувствую, что вопрос наполовину смущен, но я сделаю удар.
Если у вас есть таблица членов, то имеет смысл, чтобы member_id был уникальным первичным ключом. Если вы хотите сохранить, в какие группы входит каждый участник, просто добавьте новый столбец в таблицу Участники для каждой группы.
Что касается значений для столбцов Group1, Group2, Group3 Вы можете установить их как ENUM («0», «1») или ENUM («Нет», «Да») или как угодно и сделать значение по умолчанию отрицательным значением (первым).
С этой структурой БД вам не нужно будет прерывать строку во время запросов — вы просто пишете операторы SELECT или WHERE, которые задают соответствующее значение столбца группы.
Если это не дает прямого ответа, пожалуйста, уточните свой вопрос.