mysql Связь "Многие ко Многим" -- пример SQL кода таблиц с пояснениями. Таблица связи (ON DELETE CASCADE). Получение данных

Forums:

Ситуация

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

Далее будет использоваться синтаксис mysql.

Проектируем базу для связи Многие-ко-Многим -- sql для создания таблиц

Нам потребуется создать три таблицы:

  1. Таблицу "Заявка"
  2. Таблицу "Номинация"
  3. и т.н. "таблицу связи"

Сделаем это (SQL):

CREATE TABLE `Tickets` (
    `ticketID` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL
        DEFAULT '' COMMENT 'Имя участника/название организации',
    `info` VARCHAR(255) NULL
        DEFAULT '' COMMENT 'Информация о номинанте',
    PRIMARY KEY (`ticketID`)
)
COMMENT='Заявки учасников конкурса'
ENGINE=InnoDB
;
  
CREATE TABLE `Nominations` (
    `nominationID` INT(11) NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NULL
        DEFAULT NULL COMMENT 'Название номинации',
    PRIMARY KEY (`nominationID`)
)
COMMENT='Номинации конкурса'
ENGINE=InnoDB
;
  
CREATE TABLE `Tickets_Nominations` (
    `ticket_id` INT(11) NOT NULL,
    `nomination_id` INT(11) NOT NULL,
    PRIMARY KEY (`ticket_id`, `nomination_id`),
    INDEX `ticket_id` (`ticket_id`),
    INDEX `nomination_id` (`nomination_id`),
    CONSTRAINT `FK_Nominations` FOREIGN KEY (`nomination_id`) 
        REFERENCES `Nominations` (`nominationID`) ON DELETE CASCADE,
    CONSTRAINT `FK_Ticket` FOREIGN KEY (`ticket_id`) 
        REFERENCES `Tickets` (`ticketID`) ON DELETE CASCADE
)
COMMENT='Таблица связи заявок участников и номинаций конкурса'
ENGINE=InnoDB
;

Обратите внимание на:

  1. Свойство "ON DELETE CASCADE" --
    это значит, что если будет удалена запись в другой таблице, на которую ссылается данный кортеж (из таблицы связи), то и этот кортеж будет удалён целиком. В данном случае связь удаляется из таблицы если удалено хотя быть что-то одно из двух:
    • или заявка, на которую он ссылается
    • или номинация, на которую он ссылается

    -- таким образом мы переносим задачу удаления неактуальный связей с приложения на СУБД.

  2. В этом примере использован составной первичный ключ PRIMARY KEY ,ведь там написано:
    PRIMARY KEY (`ticket_id`, `nomination_id`),
    

    -- это автоматически делает (накладывает ограничение) данную комбинацию двух внешний ключей уникальной в рамках таблицы связи (т.е. уже не получится в данную таблицу два раза написать что "Вася подал заявку в номинацию "Лучший повар""), на самом деле, в ряде случаев (например, для оперирования удобным численным ключом) можно было бы просто добавить обычный численные первичный ключ, а на пару внешних ключей каждого кортежа таблицы связи наложить требование уникальности (т.н. "уникальный составной индекс") -- т.е. сделать нашу таблицу связи немного другой:, итак -- таблица связи (другой вариант):

    CREATE TABLE `Tickets_Nominations` (
        `id` INT(11) NOT NULL AUTO_INCREMENT,
        `ticket_id` INT(11) NOT NULL,
        `nomination_id` INT(11) NOT NULL,
        INDEX `ticket_id` (`ticket_id`),
        INDEX `nomination_id` (`nomination_id`),
        CONSTRAINT `FK_Nominations` FOREIGN KEY (`nomination_id`) 
    		REFERENCES `Nominations` (`nominationID`) ON DELETE CASCADE,
        CONSTRAINT `FK_Ticket` FOREIGN KEY (`ticket_id`) 
    		REFERENCES `Tickets` (`ticketID`) ON DELETE CASCADE,
    	PRIMARY KEY (`id`),
    	UNIQUE KEY `relation_row_unique` (`ticket_id`,`nomination_id`)
    )
    COMMENT='Таблица связи заявок участников и номинаций конкурса'
    ENGINE=InnoDB
    ; 
    

    -- но часто вполне можно обойтись и изначальным вариантом, не создавая "лишний" столбец.

Извлечение данных для связи "многие ко многим" (SELECT)

Возникает логичный вопрос -- как же получать данные из базы, используя таблицу связи?
Есть разные варианты для разных ситуаций, которые мы сейчас рассмотрим, но прежде чем проиллюстрировать их, заполните созданные выше таблицы данными с помощью такого sql (чтобы вы тоже могли поэкспериментировать с запросами)

Рассмотрим задачу извлечения участников, связанных с данной номинацией -- или короче "номинации, и всех, кто подал в неё заявки" (алгоритм извлечения данных в обратную сторону -- т.е. "участик и все его номинации" абсолютно аналогичен).
На практике приходится сталкиваться с двумя базовыми ситуациями:

  1. Извлечение одной сущности номинации и связанных с ней участников
  2. Извлечение списка сущностей номинаций и связанных с каждой из номинаций участников (т.е. фактически список участников для каждого элемента из списка номинаций).

Извлечение связанных (многие-ко-многим) данных для одной сущности

Пусть у нас известен id () номинации и мы хотим получить сведения об этой номинации и всех участниках в ней.
Во-первых, сделать это можно двумя sql запросами:

  1. Сначала просто получим кортеж этой номинации:
    mysql> SELECT * FROM Nominations WHERE nominationID=4;
    +--------------+-----------------------------+
    | nominationID | title                       |
    +--------------+-----------------------------+
    |            4 | Лучшее пособие              |
    +--------------+-----------------------------+
    
    
  2. После, опять же зная id номинации (используем в WHERE), достаточно просто сделать LEFT JOIN между таблицей связи и таблицей участников:
    SELECT * FROM  Tickets_Nominations LEFT JOIN Tickets 
    	ON ticket_id = ticketID 
    	WHERE Tickets_Nominations.nomination_id = 4;

    Получим:

    +-----------+---------------+----------+-------------------------- +----------------------------------------------+
    | ticket_id | nomination_id | ticketID | name                      | info                                         |
    +-----------+---------------+----------+---------------------------+----------------------------------------------+
    |         3 |             4 |        3 | Программирование для всех | Некоммерческая образовательная организация  
    |         4 |             4 |        4 | Юный программист          | Кружок для детей в д. Простоквашино        
    |         5 |             4 |        5 | IT FOR FREE               | Русскоязычное IT-сообщество с уклоном в web  
    |         6 |             4 |        6 | Саша Петров               | Студент 2 курса, автор пособия по SQL   
    

    -- как видим, тут мы получили вообще все колонки (т.к. в запросе указали звездочку *) двух соединённых таблиц (связи и заявок).
    Также видим что на номинации с id=4 номинировалось 4-ре участника, кроме их имен видны также и описания.
    Все эти данные можно использовать в приложении, после выполнения запроса к БД -- например записать, то что нужно в поле, хранящее массив объекта конкретной номинации.

Если вам требуется от массива связанных сущностей только одно поле (напр. имена участников), то решить задачу можно вообще одним sql запросом, используя группировку (GROUP BY) и применимую к группируемым значения колонки функцию конкатенации GROUP_CONCAT():

SELECT 
    Nominations.*, 
	 GROUP_CONCAT(Tickets.name SEPARATOR ', ') as participants_names
 FROM  
   Nominations LEFT JOIN Tickets_Nominations 
	   ON Nominations.nominationID = Tickets_Nominations.nomination_id 
	LEFT JOIN Tickets  
	   ON Tickets.ticketID = Tickets_Nominations.ticket_id
 
WHERE Tickets_Nominations.nomination_id = 4
 	GROUP BY Nominations.nominationID;

Получим единственный кортеж:

+--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+
| nominationID | title                       | participants_names                                                                                                    |
+--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+
|            4 | Лучшее пособие              | Программирование для всех, Юный программист, IT FOR FREE, Саша Петров                                                 |
+--------------+-----------------------------+-----------------------------------------------------------------------------------------------------------------------+

-- здесь мы:

  • провели сразу тройной JOIN, как бы поставив таблицу связи между таблицами номинаций и заявок.
  • нас интересовали имена участников для 4 номинации -- поэтому использовали WHERE Tickets_Nominations.nomination_id = 4
  • Группировка (чтобы в итоге получить только одну строку-кортеж) проходила по id номинации (Nominations.nominationID)
  • Сконкатенированному полю мы назначили псевдоним (participants_names)

Плюсом такого подхода является то, что в приложении можно использовать готовую строку participants_names, а минусом то, что с этим значением уже нельзя работать как с массивом, явно не преобразовав.

Извлечение списка сущностей со связанными данными

Прежде всего можно:

  1. Cначала извлечь (SELECT) необходимые номинации (или вообще все),
  2. а потом уровне приложения в цикле извлечь связанные данные для каждой номинации отдельно (как это показано выше) -- это не оптимальный способ так как он порождает много запросов к БД (так что если список номинаций - - или иных сущностей велик, то и запросы сильно скажутся на суммарном времени выполнении скрипта и нагрузке на процессор)
vedro-compota's picture

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

_____________
матфак вгу и остальная классика =)

vedro-compota's picture

# Заполняем таблицу заявок на участие в конкурсе
INSERT INTO `Tickets` VALUES
(1, 'Василий Иванов', 'Преподаватель математики и инфроматики в средней школе №123'),
(2, 'Анна Сергеева', 'Инженер-программист в очень известной IT-фирме'),
(3, 'Программирование для всех', 'Некоммерческая образовательная организация'),
(4, 'Юный программист', 'Кружок для детей в д. Простоквашино'),
(5, 'IT FOR FREE', 'Русскоязычное IT-сообщество с уклоном в web'),
(6, 'Саша Петров', 'Студент 2 курса, автор пособия по SQL')
;

# Заполняем таблицу номинаций
INSERT INTO `Nominations` VALUES
(1, 'Дополнительное образование'),
(2, 'Открытый исходыный код (лучший проект open-source)'),
(3, 'Детское образование'),
(4, 'Лучшее пособие'),
(5, 'Новый алгоритм (научно-инженерный вклад)')
;

# Заполняем таблицу связей - кто куда номинирован
INSERT INTO `Tickets_Nominations` VALUES
(1, 3),
(2, 5),
(3, 1),
(3, 2),
(3, 3),
(3, 4),
(3, 5),
(4, 3),
(4, 4),
(5, 1),
(5, 2),
(5, 4),
(6, 4)
;

_____________
матфак вгу и остальная классика =)

vedro-compota's picture

Создайте новую тестовую базу данных (три таблицы из основного текста статьи) + заполнение данных
Потренируйтесь в приведенными примерами запросов -- постарайтесь вникнуть в них

Например:

  1. Создайте ещё одну номинацию (INSERT в таблицу номинации) и ещё несколько участников (INSERT в таблицу заявок)
  2. номинируйте вновь созданных участников на вашу новую номинацию и, по выбору, на какие-нибудь из старых (INSERT idшников в таблицу связи по-аналогии)

Если что-то непонятно задавайте вопросы в комментариях здесь же.

_____________
матфак вгу и остальная классика =)

vedro-compota's picture

в примере говориться о mysql или это распространяеться и на другие типы серверов?

Смотря о чем именно речь, например:

  1. три таблицы для связи "многие ко многим" - это общий подход для любой реляционной БД (напр. для postgres то же)
  2. Каскадные операции - типа удалений, это просто удобная возможность, она может быть может не быть в конкретной СУБД, можно её использовать, а можно не использовать по каким-то соображениям.

_____________
матфак вгу и остальная классика =)