用户角色表

---

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)                                      
) 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. 根据你的具体需求，你可能需要调整字段类型、大小或外键约束的行为。