1.逐步单表查询,适合表不在同一个库里
SELECT * FROM tb_users WHERE username="admin"
SELECT role_id FROM tb_user_roles WHERE user_id=1
SELECT menu_id FROM tb_role_menus WHERE role_id=1
SELECT permission FROM tb_menus WHERE id IN (1,2,3)
2.嵌套查询,性能差
SELECT permission
FROM tb_menus
WHERE id IN (SELECT menu_id
FROM tb_role_menus
WHERE role_id=(SELECT role_id
FROM tb_user_roles
WHERE user_id=1
))
3.多表联查
SELECT DISTINCT m.permission
FROM tb_user_roles ur
JOIN tb_role_menus rm
ON ur.role_id=rm.role_id
JOIN tb_menus m
ON rm.menu_id=m.id
WHERE ur.user_id=1