多对多三表关联

角色表

DROP TABLE IF EXISTS `roles`;

CREATE TABLE `roles` (
  `id` int(9) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `description` varchar(255) NOT NULL COMMENT '描述',
  `name` varchar(32) NOT NULL,
  `router_power` text NOT NULL,
  `api_power` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='角色表';

用户表

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `uid` int(9) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `nickname` varchar(32) NOT NULL,
  `username` varchar(32) NOT NULL,
  `password` varchar(32) NOT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='用户表';

用户-角色关系表

DROP TABLE IF EXISTS `users_roles`;
CREATE TABLE `users_roles` (
  `uid` int(9) unsigned NOT NULL,
  `rid` int(9) unsigned NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='用户-角色关系表';

查询某个用户拥有哪些角色

SELECT u.uid,u.nickname,
group_concat(r.name Separator ',') as role_names,
group_concat(r.id Separator ',') as role_ids
FROM `users` u
LEFT JOIN users_roles ur ON ur.uid = u.uid
LEFT JOIN roles r ON ur.rid = r.id
WHERE u.uid = 2
GROUP by ur.uid
LIMIT 0 , 30

查询某个角色拥有哪些用户

SELECT u.uid, u.nickname, 
r.id AS role_id, 
r.name AS role_name
FROM `users` u
LEFT JOIN users_roles ur ON ur.uid = u.uid
LEFT JOIN roles r ON ur.rid = r.id
WHERE r.id =1
LIMIT 0 , 30