node+mysql菜单权限管理

1.创建menu菜单

描述	字段	类型	约束
菜单id	id	int	主键自增
菜单名	name	varchar	不能为空
链接页面	url	varchar
父菜单id	parent_id	int
菜单排序	sort	int
图标	icon	varchar
	perms	varchar
菜单类型	type	timestamp
创建时间	creat_time	timestamp
更新时间	update_time	TIMESTAMP	自动更新

建表sql：

CREATE TABLE menu (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '菜单id',
NAME VARCHAR ( 50 ) NOT NULL COMMENT '菜单名',
parent_id INT COMMENT '链接页面',
sort INT COMMENT '菜单排序',
url VARCHAR ( 500 ) COMMENT '父菜单id',
icon VARCHAR ( 30 ) COMMENT '图标',
perms VARCHAR ( 100 ) COMMENT '菜单id',
type SMALLINT ( 6 ) COMMENT '菜单类型   ',
creat_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' 
);

插入数据：

insert into menu(name,icon,type,sort) value("权限管理","fa fa-bug",0,1);
insert into menu(name,parent_id,url,icon,perms,type,sort) value("菜单管理",2,"/sys/menu.html","fa fa-th-list","svs:menu:list",1,1000);
insert into menu(name,parent_id,url,icon,perms,type,sort) value("角色管理",2,"/sys/role.html","fa fa-key","svs:role:list",1,1000);
insert into menu(name,parent_id,url,icon,perms,type,sort) value("用户管理",2,"/sys/user.html","fa fa-user","svs:user:list",1,1000);

insert into menu(name,parent_id,perms,type,sort) value("添加",5,"sys:user:add",2,1000);
insert into menu(name,parent_id,perms,type,sort) value("修改",5,"sys:user:update",2,1000);
insert into menu(name,parent_id,perms,type,sort) value("删除",5,"sys:user:delete",2,1000);
insert into menu(name,parent_id,perms,type,sort) value("授权",5,"sys:user:assign",2,1000);

insert into menu(name,parent_id,perms,type,sort) value("添加",3,"sys:menu:add",2,1000);
insert into menu(name,parent_id,perms,type,sort) value("修改",3,"sys:menu:update",2,1000);
insert into menu(name,parent_id,perms,type,sort) value("删除",3,"sys:menu:delete",2,1000);

insert into menu(name,parent_id,perms,type,sort) value("添加",4,"sys:role:add",2,1000);
insert into menu(name,parent_id,perms,type,sort) value("修改",4,"sys:role:update",2,1000);
insert into menu(name,parent_id,perms,type,sort) value("删除",4,"sys:role:delete",2,1000);
insert into menu(name,parent_id,perms,type,sort) value("授权",4,"sys:role:assign",2,1000);

2.创建用户表user

描述	字段	类型	约束
用户id	id	int	主键自增
用户名	account	varchar	唯一、不能为空
用户密码	password	varchar
昵称	nickname	varchar
动态盐(用户名md5)	salt	varchar
用户状态	status	int	为1正常，为0表示停用
创建时间	creat_time	timestamp
更新时间	update_time	TIMESTAMP	自动更新

建表sql：

CREATE TABLE USER (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户id',
account VARCHAR ( 50 ) NOT NULL UNIQUE COMMENT '用户名',
PASSWORD VARCHAR ( 50 ) COMMENT '用户密码',
nickname VARCHAR ( 50 ) COMMENT '昵称',
salt VARCHAR ( 50 ) COMMENT '动态盐(用户名md5)',
STATUS INT DEFAULT '1' COMMENT '用户状态',
creat_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间' 
);

插入数据：

INSERT INTO USER ( account, PASSWORD, nickname )
VALUES
    ( "a000001", "123456", "瞪谁谁怀孕" ),
    ( "a000002", "123456", "骑猪上高速" ),
    ( "a000003", "123456", "朕要去幼儿园深造了" ),
    ( "a000004", "123456", "帅到拖网速" ),
    ( "a000005", "123456", "遇蛇撑伞装许仙" ),
    ( "a000006", "123456", "谈情不如逗狗" ),
    ( "a000007", "123456", "地球是哥捏圆的" ),
    ( "a000008", "123456", "不贱不散" ),
    ( "a000009", "123456", "跳进海里躲雨" ),
    ( "a000010", "123456", "此用户下落不明" ),
    ( "a000011", "123456", "软妹轰炸机" ),
    ( "a000012", "123456", "怡红院掌柜" ),
    ( "a000013", "123456", "被丢弃的小盆友" ),
    ( "a000014", "123456", "近猪者吃" ),
    ( "a000015", "123456", "跪是种美德" ),
    ( "a000016", "123456", "老衲逛青楼" ),
    ( "a000017", "123456", "武功再高也怕菜刀" ),
    ( "a000018", "123456", "镜子你又胖了" ),
    ( "a000019", "123456", "骑猪总裁" ),
    ( "a000020", "123456", "抢我辣条还想跑" ),
    ( "a000021", "123456", "骗子被骗子骗了" ),
    ( "a000022", "123456", "农夫三拳" ),
    ( "a000023", "123456", "夜以深,适宜私奔" ),
    ( "a000024", "123456", "卖女孩的小火柴" ),
    ( "a000025", "123456", "唐伯虎点蚊香" ),
    ( "a000026", "123456", "贱男春" ),
    ( "a000027", "123456", "老鼠上了猫" ),
    ( "a000028", "123456", "穷人的孩子早出家" ),
    ( "a000029", "123456", "车到山前是死路" ),
    ( "a000030", "123456", "我在马路边丢了一分钱" ),
    ( "a000031", "123456", "卖身葬楼主" ),
    ( "a000032", "123456", "人贱人爱" ),
    ( "a000033", "123456", "看野花一朵朵" );

3.创建角色表role

描述	字段	类型	约束
角色id	id	int	主键自增
角色名	name	varchar
角色备注	remark	varchar
角色状态	status	int
创建时间	creat_time	timestamp
更新时间	update_time	TIMESTAMP	自动更新

建表sql：

CREATE TABLE role (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '角色id',
NAME VARCHAR ( 50 ) NOT NULL COMMENT '角色名',
remark VARCHAR ( 50 ) NOT NULL COMMENT '角色备注',
STATUS INT NOT NULL COMMENT '角色状态 ',
creat_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
);

插入数据：

INSERT INTO role (NAME, REMARK, STATUS) VALUES ('admin', '超级管理员', 1),
('test1', 'remark', 1),
('aa3', '3333', 0),
('asd', 'sadsa', 0),
('sa', 'asd', 0);

4.创建角色权限表role_menu

描述	字段	类型	约束
角色id	role_id	int	不能为空
菜单名	menu_id	int	不能为空
创建时间	creat_time	timestamp
更新时间	update_time	TIMESTAMP	自动更新

建表sql：

CREATE TABLE role_menu (
    role_id INT NOT NULL COMMENT '角色id',
    menu_id INT NOT NULL COMMENT '菜单名',
    creat_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    CONSTRAINT `PRIMARY` PRIMARY KEY ( role_id, menu_id ) 
);

插入数据：

INSERT INTO role_menu (role_id, menu_id) 
VALUES(1, 2),(1, 3),(1, 4),(1, 5),(1, 6),(1, 7),(1, 8),(1, 9),(1, 10),(1, 11),(1, 12),(1, 13),(1, 14),(1, 15);
INSERT INTO role_menu (role_id, menu_id) 
VALUES(2, 4),(2, 12),(2, 13),(2, 14),(2, 15);

5.创建用户角色表user_role

描述	字段	类型	约束
用户id	user_id	int	不能为空
角色id	role_id	int	不能为空
创建时间	creat_time	timestamp
更新时间	update_time	TIMESTAMP	自动更新

建表sql：

CREATE TABLE user_role (
    user_id INT NOT NULL COMMENT '用户id',
    role_id INT NOT NULL COMMENT '角色id',
    creat_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
CONSTRAINT `PRIMARY` PRIMARY KEY ( user_id, role_id ) 
);

插入数据：

INSERT INTO user_role (USER_ID, ROLE_ID) VALUES (1, 1),(2, 2);