mysql - 为多对多表添加默认配置

Question

这里是 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 命令可能是什么样子吗？

Answer 1

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 Fiddle