I. demand.

Existing user table, user role table, role table. One of the users corresponds to multiple roles. When querying user details, you need to directly query the role names of users, separated by commas.

2, sql table creation script

2.1 user table

CREATE TABLE `user` (
  `uid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `username` varchar(256) DEFAULT NULL COMMENT 'Account number',
  `password` varchar(256) DEFAULT NULL COMMENT 'Password',
  `state` int(11) DEFAULT NULL COMMENT 'Status 0 delete 1 enable 2 disable',
  `nickname` varchar(256) DEFAULT NULL COMMENT 'Nickname?',
  `position` varchar(256) DEFAULT NULL COMMENT 'position',
  `mobile` varchar(32) DEFAULT NULL COMMENT 'Cell-phone number',
  `email` varchar(256) DEFAULT NULL COMMENT 'mailbox',
  `create_uid` bigint(20) DEFAULT NULL COMMENT 'Founder ID',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  `update_uid` bigint(20) DEFAULT NULL COMMENT 'Modifier ID',
  `update_time` datetime DEFAULT NULL COMMENT 'Modification time',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='User table';

2.2 user role table

CREATE TABLE `role_menu` (
  `rmid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `rid` bigint(20) DEFAULT NULL COMMENT 'role ID',
  `mid` bigint(20) DEFAULT NULL COMMENT 'menu ID',
  `state` int(11) DEFAULT NULL COMMENT 'state: 0 Delete 1 normal',
  `create_uid` bigint(20) DEFAULT NULL COMMENT 'Founder ID',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  `update_uid` bigint(20) DEFAULT NULL COMMENT 'Modifier ID',
  `update_time` datetime DEFAULT NULL COMMENT 'Modification time',
  PRIMARY KEY (`rmid`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COMMENT='Role menu relation table';

2.3 role table

CREATE TABLE `role_menu` (
  `rmid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `rid` bigint(20) DEFAULT NULL COMMENT 'role ID',
  `mid` bigint(20) DEFAULT NULL COMMENT 'menu ID',
  `state` int(11) DEFAULT NULL COMMENT 'state: 0 Delete 1 normal',
  `create_uid` bigint(20) DEFAULT NULL COMMENT 'Founder ID',
  `create_time` datetime DEFAULT NULL COMMENT 'Creation time',
  `update_uid` bigint(20) DEFAULT NULL COMMENT 'Modifier ID',
  `update_time` datetime DEFAULT NULL COMMENT 'Modification time',
  PRIMARY KEY (`rmid`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8 COMMENT='Role menu relation table';

2.4 initialization record

INSERT INTO `ucpaas_operate_paltform`.`user`(`uid`, `username`, `password`, `state`, `nickname`, `position`, `mobile`, `email`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (1, 'qjwyss', 'e10adc3949ba59abbe56e057f20f883e', 1, 'yuanshushu', 'java', '15107550001', '1@ucpaas.com', 0, '2018-09-03 16:40:44', NULL, NULL);

INSERT INTO `ucpaas_operate_paltform`.`user_role`(`urid`, `uid`, `rid`, `state`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (2, 1, 3, 1, 0, '2018-09-03 17:03:54', NULL, NULL);
INSERT INTO `ucpaas_operate_paltform`.`user_role`(`urid`, `uid`, `rid`, `state`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (3, 1, 5, 1, 0, '2018-09-03 17:04:03', NULL, NULL);

INSERT INTO `ucpaas_operate_paltform`.`role`(`rid`, `role_name`, `description`, `state`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (0, 'Super administrator', 'Over pipe', 1, 0, '2018-08-27 12:09:59', 0, '2018-08-29 17:20:02');
INSERT INTO `ucpaas_operate_paltform`.`role`(`rid`, `role_name`, `description`, `state`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (3, 'Operator 3', 'Operator description 3', 0, 0, '2018-08-29 14:56:44', 0, '2018-08-30 11:29:08');
INSERT INTO `ucpaas_operate_paltform`.`role`(`rid`, `role_name`, `description`, `state`, `create_uid`, `create_time`, `update_uid`, `update_time`) VALUES (5, 'Supervisor 2', 'Manage a lot of things 2', 1, 0, '2018-08-29 15:37:47', 0, '2018-08-30 11:14:04');

Three. Use

3.1 query out the role according to the user ID and display it as a record. The role names are separated by commas.

SELECT
	GROUP_CONCAT( R.role_name ) AS role_name_list
FROM
	user_role AS UR 
	LEFT JOIN role AS R ON R.rid = UR.rid
WHERE
	UR.state > 0
	AND UR.uid = 1
GROUP BY UR.uid

Result: