用户 & 角色 & 用户角色关联表设计

YYAugenstern

已于 2024-05-07 23:26:25 修改

阅读量133

点赞数

分类专栏：经验总结系列文章标签：数据库 java 前端

于 2023-11-12 16:46:38 首次发布

本文链接：https://blog.csdn.net/HelloWorld20161112/article/details/134362566

版权

经验总结系列专栏收录该内容

62 篇文章 0 订阅

订阅专栏

DROP TABLE IF EXISTS `mybatis_role`;
CREATE TABLE `mybatis_role`  (
                                 `id` int NOT NULL AUTO_INCREMENT COMMENT '角色编号',
                                 `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '角色名称（英文）',
                                 `name_zh` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '角色名称（中文）',
                                 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '角色表' ROW_FORMAT = DYNAMIC;

INSERT INTO `mybatis_role` VALUES (1, 'dba', '数据库管理员');
INSERT INTO `mybatis_role` VALUES (2, 'admin', '超级管理员');
INSERT INTO `mybatis_role` VALUES (3, 'user', '用户');

DROP TABLE IF EXISTS `mybatis_user`;
CREATE TABLE `mybatis_user`  (
                                 `id` int NOT NULL AUTO_INCREMENT COMMENT '用户编号',
                                 `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '用户名',
                                 `password` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '密码',
                                 PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户表' ROW_FORMAT = DYNAMIC;


INSERT INTO `mybatis_user` VALUES (1, 'root', 'root123');
INSERT INTO `mybatis_user` VALUES (2, 'admin', 'admin123');
INSERT INTO `mybatis_user` VALUES (3, 'user', 'user123');

DROP TABLE IF EXISTS `mybatis_user_role`;
CREATE TABLE `mybatis_user_role`  (
                                      `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
                                      `u_id` int NULL DEFAULT NULL COMMENT '用户ID',
                                      `r_id` int NULL DEFAULT NULL COMMENT '角色ID',
                                      PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '用户角色关联表' ROW_FORMAT = DYNAMIC;

INSERT INTO `mybatis_user_role` VALUES (1, 1, 1);
INSERT INTO `mybatis_user_role` VALUES (2, 1, 2);
INSERT INTO `mybatis_user_role` VALUES (3, 2, 2);
INSERT INTO `mybatis_user_role` VALUES (4, 3, 3);