Добавление конфигураций по умолчанию для таблиц "многие ко многим"

MySQL здесь. У меня есть следующая таблица users: 

describe users;
+----------------------------------+---------------------+------+-----+---------+----------------+
| Field                            | Type                | Null | Key | Default | Extra          |
+----------------------------------+---------------------+------+-----+---------+----------------+
| user_id                          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_status_id                   | bigint(20) unsigned | NO   | MUL | NULL    |                |
| profile_id                       | bigint(20) unsigned | YES  | MUL | NULL    |                |
+----------------------------------+---------------------+------+-----+---------+----------------+

Я хотел бы добавить простую модель RBAC, где:

  • пользователи могут иметь 0 + ролей
  • роли имеют 0 + разрешений

Моя предлагаемая структура таблицы: 

[roles] table
---
role_id : BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
role_name : VARCHAR(50)

[permissions] table
---
permission_id : BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
permission_name : VARCHAR(50)

[roles_x_permissions] many:many table
---
roles_x_permissions_id : BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
role_id : BIGINT UNSIGNED FOREIGN KEY to [roles] table
permission_id : BIGINT UNSIGNED FOREIGN KEY to [permissions] table

[users_x_roles] many:many table
---
users_x_roles_id : BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT
user_id : BIGINT UNSIGNED FOREIGN KEY to [users] table
role_id : BIGINT UNSIGNED FOREIGN KEY to [roles] table

И фактические роли и разрешения, которые приложение будет поддерживать (по крайней мере, первоначально): 

INSERT INTO permissions (
  permission_name
) VALUES (
  'CanDoSomeBasicStuff'
);

INSERT INTO roles (
  role_name
) VALUES (
  'BasicUser'
);

INSERT INTO roles_x_permissions (
  role_id,
  permission_id
) VALUES (
  1,    // BasicUser
  1     // CanDoSomeBasicStuff
);

INSERT INTO users_x_roles (
  user_id,
  role_id
) VALUES (
  ???
);

Где я борюсь это: по умолчанию, все существующие пользователи будут иметь роль BasicUser. Мне нужен элегантный способ присвоить все мои существующие пользователи этой роли BasicUser в одной команде, но я не могу понять, как должна выглядеть команда SQL: 

INSERT INTO users_x_roles uxr (
  user_id,
  role_id
) VALUES (
  ???,   # How to do this for each user record in the users table?
  ???    # How to fetch the correct role_id for the BasicUser role?
)

Есть идеи, как может выглядеть эта команда SQL?

2 2
sql mysql many-to-many ansi-sql

2 ответа:

Синтаксис INSERT ... VALUES вставляет одну запись. Вам нужно использовать синтаксис INSERT ... SELECT, например: 

INSERT INTO users_x_roles (
  user_id,
  role_id
)
 SELECT 
  user_id, 
  (SELECT MAX(role_id) FROM roles where role_name='BasicUser')
 FROM users;

Или, если вам не нравится подзапрос: 

INSERT INTO users_x_roles (
  user_id,
  role_id
)
 SELECT 
  u.user_id, 
  r.role_id 
 FROM users u CROSS JOIN roles r where r.role_name='BasicUser';

Обновление: опечатка была в инструкции INSERT. Я скопировал ваш код и использовал синтаксис типа: INSERT INTO users_x_roles uxr, что недопустимо: вы не можете использовать псевдонимы в предложении Insert Into.

Теперь все работает нормально. Проверьте скрипку SQL

1

Как это сделать для каждой записи Пользователя в таблице users?

Напишите запрос, который выберет ровно одного пользователя, например: 

SELECT user_id FROM users WHERE name = 'John'

Как получить правильный role_id для роли BasicUser?

Напишите запрос, который выберет ровно одну желаемую роль, например: 

SELECT role_id FROM roles WHERE role_name='BasicUser'

В конце поместите эти два запроса в оператор INSERT следующим образом (используя скобки): 

INSERT INTO users_x_roles uxr (
  user_id,
  role_id
) VALUES (
  ( SELECT user_id FROM users WHERE name = 'John' ) ,
  ( SELECT role_id FROM roles WHERE role_name='BasicUser' )
)
0