用户角色表

---

users表：

CREATE TABLE `users` (  `id` BIGINT UNSIGNED AUTO_INCREMENT,  `username` VARCHAR(255) NOT NULL UNIQUE, `name` VARCHAR(255) NOT NULL UNIQUE,  `email` VARCHAR(255) NOT NULL UNIQUE,  `password_hash` VARCHAR(255) NOT NULL,  `created_at` DATETIME NOT NULL,  `updated_at` DATETIME DEFAULT NULL,  `is_active` TINYINT(1) NOT NULL DEFAULT 1,  `is_verified` TINYINT(1) NOT NULL DEFAULT 0,  `last_login` DATETIME DEFAULT NULL,  `failed_login_attempts` INT NOT NULL DEFAULT 0,  `lockout_until` DATETIME DEFAULT NULL,  PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO `users` (`name`,`username`, `email`, `password_hash`, `created_at`, `is_active`, `is_verified`)  
VALUES  
('admin','admin', 'admin@example.com', '123456', NOW(), 1, 1), 
('user','user', 'user.doe@example.com', '123456', NOW(), 1, 1);  

---

角色表

  创建一个角色表（roles）。这个表包含基本的字段，如角色ID（作为主键）、角色名称和可选的角色描述。

CREATE TABLE IF NOT EXISTS roles (  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,  name VARCHAR(255) NOT NULL,  description TEXT,  -- 可以根据需要添加其他字段，比如创建时间、更新时间等  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;  -- 示例数据插入  
INSERT INTO roles (name, description) VALUES  
('ADMIN', '拥有系统的所有权限'),  
('USER', '普通用户，具有基本的访问权限'),  
('GUEST', '访客，具有有限的访问权限');

在这个脚本中：

1. CREATE TABLE IF NOT EXISTS 语句用于检查roles表是否已经存在，如果不存在则创建它。这有助于在数据库迁移或初始化时避免错误。
2. id INT AUTO_INCREMENT PRIMARY KEY 定义了一个自增的主键字段，用于唯一标识每个角色。
3. name VARCHAR(255) NOT NULL 定义了一个必须填写的角色名称字段。
4. description TEXT 提供了一个可选的文本字段，用于存储角色的详细描述。
5. created_at 和 updated_at 字段用于自动记录行的创建时间和最后更新时间。这些字段使用了TIMESTAMP类型，并且updated_at字段被配置为在每次行更新时自动更新。

脚本的最后部分包含了一些示例数据插入语句，用于向roles表中插入几个预定义的角色。

请注意，你可能需要根据你的具体需求调整字段类型和大小。例如，如果你预计角色名称会非常长，你可能需要增加name字段的长度。同样，如果你不需要自动时间戳跟踪，你可以省略created_at和updated_at字段。

---

用户角色关联表

  用户角色关联表（通常命名为user_roles）用于建立用户和角色之间的多对多关系。在MySQL中，这个表将包含两个外键：一个指向用户表（如users）的用户ID，另一个指向角色表（如roles）的角色ID。以下是一个创建用户角色关联表的MySQL脚本代码示例：

CREATE TABLE IF NOT EXISTS user_roles (user_id BIGINT UNSIGNED,role_id BIGINT UNSIGNED,FOREIGN KEY (user_id) REFERENCES users(id)ON DELETE CASCADE -- 如果用户被删除，则删除其所有角色关联ON UPDATE CASCADE, -- 如果用户ID更新，则更新其所有角色关联FOREIGN KEY (role_id) REFERENCES roles(id)ON DELETE CASCADE -- 如果角色被删除，则删除所有与该角色关联的用户ON UPDATE CASCADE, -- 如果角色ID更新，则更新所有与该角色关联的用户-- 可选：添加索引以提高查询性能INDEX idx_user_id (user_id),INDEX idx_role_id (role_id)--PRIMARY KEY (user_id, role_id)                                         
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;INSERT INTO user_roles (user_id, role_id) VALUES 
(1, 1),
(2, 2);

请注意以下几点：

1. 我已经包含了两个外键约束，分别指向users表的id字段和roles表的id字段。这些外键确保了用户ID和角色ID的有效性，并且可以通过ON DELETE CASCADE和ON UPDATE CASCADE选项自动管理关联数据的级联删除和更新。
2. 我已经注释掉了PRIMARY KEY (user_id, role_id)行，因为在多对多关系中，这通常是正确的做法（表示一个用户可以有多个角色，并且一个角色可以分配给多个用户）。然而，如果你的业务逻辑要求一个用户只能属于一个角色（这通常不是多对多关系的用例），则应该取消注释该行，并使用它作为主键。但请注意，这实际上会将关系转变为一对一或一对多。
3. 我已经包含了两个索引（idx_user_id和idx_role_id），这可以提高基于用户ID或角色ID的查询性能。索引是可选的，但在处理大量数据时通常是推荐的做法。
4. 请确保在运行此脚本之前，users和roles表已经存在，并且它们的id字段是主键。
5. 根据你的具体需求，你可能需要调整字段类型、大小或外键约束的行为。