Как организовать связь «многие ко многим» в MySQL?

Связь «многие ко многим» (many-to-many) реализуется через промежуточную таблицу (junction table или association table), которая хранит пары внешних ключей, ссылающихся на первичные ключи двух основных таблиц.

Классический пример: Пользователи и Роли Пользователь может иметь несколько ролей, роль может быть назначена нескольким пользователям.

-- 1. Основные сущности
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL UNIQUE,
    description TEXT
) ENGINE=InnoDB;

-- 2. Промежуточная таблица для связи
CREATE TABLE user_roles (
    user_id INT UNSIGNED NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, role_id), -- Составной первичный ключ предотвращает дубликаты связей
    FOREIGN KEY (user_id) 
        REFERENCES users(id) 
        ON DELETE CASCADE, -- При удалении пользователя удаляются все его связи с ролями
    FOREIGN KEY (role_id) 
        REFERENCES roles(id) 
        ON DELETE CASCADE,
    INDEX idx_role_id (role_id) -- Дополнительный индекс для обратных запросов
) ENGINE=InnoDB;

Ключевые принципы:

• Составной первичный ключ (user_id, role_id) гарантирует уникальность каждой пары.
• Внешние ключи (FOREIGN KEY) обеспечивают ссылочную целостность: нельзя добавить связь с несуществующим пользователем или ролью.
• Опция ON DELETE CASCADE автоматически удаляет записи из user_roles при удалении связанной записи в users или roles, что поддерживает целостность базы.
• Дополнительный индекс на role_id ускоряет запросы вида «найти всех пользователей с ролью X».

Примеры запросов:

1. Выборка всех ролей пользователя:

   SELECT r.name 
   FROM users u
   JOIN user_roles ur ON u.id = ur.user_id
   JOIN roles r ON ur.role_id = r.id
   WHERE u.id = 123;

2. Добавление роли пользователю:

   INSERT INTO user_roles (user_id, role_id) VALUES (123, 5);
   -- Благодаря PRIMARY KEY дубликат (123,5) будет отклонен.

3. Проверка, имеет ли пользователь определенную роль:

   SELECT EXISTS (
       SELECT 1 FROM user_roles 
       WHERE user_id = 123 AND role_id = 5
   ) AS has_role;

Расширение промежуточной таблицы: Часто в нее добавляют дополнительные атрибуты самой связи (метаданные).

ALTER TABLE user_roles 
ADD COLUMN assigned_by INT UNSIGNED, -- Кто назначил роль
ADD COLUMN expires_at DATE, -- Срок действия роли
ADD FOREIGN KEY (assigned_by) REFERENCES users(id);

Такой паттерн применим не только к пользователям и ролям, но и к любым сущностям со связью многие-ко-многим: products и categories, students и courses, posts и tags.