当开发系统时,在MySQL中,我们设计的菜单表 包括菜单ID ,父级ID,和菜单排序 这个三个字段时,正常查询时,order by 菜单ID 即可,然而当我们添加之前已有菜单的子菜单时,菜单ID又是自增长类型的,此时查询出来的结果的排序就不是我们想要的的了。以下是一张sys_menu菜单表:
CREATE TABLE `sys_menu` (
`MENU_ID` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单ID',
`MENU_NAME` varchar(255) DEFAULT NULL COMMENT '菜单名称',
`MENU_URL` varchar(255) DEFAULT NULL COMMENT '菜单地址',
`PARENT_ID` varchar(100) DEFAULT NULL COMMENT '父级ID',
`MENU_ORDER` varchar(100) DEFAULT NULL COMMENT '菜单顺序',
`MENU_ICON` varchar(30) DEFAULT NULL COMMENT '菜单图标',
`MENU_TYPE` varchar(10) DEFAULT NULL COMMENT '菜单类型',
PRIMARY KEY (`MENU_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COMMENT='*系统菜单表';
INSERT INTO `sys_menu` VALUES ('1', '系统字典', '#', '0', '1', 'fa-desktop', '2');
INSERT INTO `sys_menu` VALUES ('2', '字典类型', 'role.do', '1', '2', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('4', '字典名称', 'happuser/listUsers.do', '1', '4', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('5', '系统用户', 'user/listUsers.do', '1', '3', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('6', '菜单管理', '#', '0', '2', 'fa-list', '2');
INSERT INTO `sys_menu` VALUES ('7', '菜单维护', 'ez/system/sysmenu/list.do', '6', '1', 'fa-caret-right', '2');
INSERT INTO `sys_menu` VALUES ('8', '性能监控', 'druid/index.html', '9', '1', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('9', '系统工具', '#', '0', '3', 'fa-th', '1');
INSERT INTO `sys_menu` VALUES ('10', '接口测试', 'tool/interfaceTest.do', '9', '2', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('11', '发送邮件', 'tool/goSendEmail.do', '9', '3', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('12', '置二维码', 'tool/goTwoDimensionCode.do', '9', '4', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('13', '多级别树', 'tool/ztree.do', '9', '5', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('14', '地图工具', 'tool/map.do', '9', '6', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('15', '微信管理', '#', '0', '2', 'fa-comments', '2');
INSERT INTO `sys_menu` VALUES ('16', '文本回复', 'textmsg/list.do', '15', '2', 'fa-caret-right', '2');
INSERT INTO `sys_menu` VALUES ('17', '应用命令', 'command/list.do', '15', '4', 'fa-caret-right', '2');
INSERT INTO `sys_menu` VALUES ('18', '图文回复', 'imgmsg/list.do', '15', '3', 'fa-caret-right', '2');
INSERT INTO `sys_menu` VALUES ('19', '关注回复', '#', '15', '1', 'fa-caret-right', '2');
INSERT INTO `sys_menu` VALUES ('20', '在线管理', 'onlinemanager/list.do', '1', '5', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('21', '打印测试', 'tool/printTest.do', '9', '7', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('22', '客户管理', 'sdfsdf/index.do', '1', '1', 'fa-caret-right', '1');
INSERT INTO `sys_menu` VALUES ('23', '菜单管理', 'ez/system/sysmenu/list.html', '19', '1', 'fa-leaf green', '2');
INSERT INTO `sys_menu` VALUES ('24', '三级1', '#', '19', '2', 'fa-pencil', '2');
参考:http://stackoverflow.com/questions/14890204/order-sql-tree-hierarchy 上给了答案,但是我的业务比他多MENU_ORDER 的一个字段,顾,我在他答案的基础上加以改造,现将整理如下:
第一步:创建一个函数,我用的Navicat for mysql 工具,点击查询,输入如下代码:
CREATE FUNCTION getPriority (inID INT) RETURNS VARCHAR(255) DETERMINISTIC
begin
DECLARE gParentID INT DEFAULT 0;
DECLARE gOderID INT DEFAULT 0;
DECLARE gPriority VARCHAR(255) DEFAULT '';
SELECT PARENT_ID,MENU_ORDER INTO gParentID,gOderID FROM sys_menu WHERE MENU_ID = inID;
if gParentID > 0 then
SET gPriority = CONCAT(gOderID,inID);
else
SET gPriority = inID;
end if;
WHILE gParentID > 0 DO /*0为根*/
SET gPriority = CONCAT(gParentID, '.', gPriority);
SELECT PARENT_ID INTO gParentID FROM sys_menu WHERE MENU_ID = gParentID;
END WHILE;
RETURN gPriority;
end
点击运行之后,就会出现如下信息,点击函数就会出现刚刚创建的函数,如果没有显示,点击右键刷新一下就会显示出来。
第二步:执行一下函数,就会得到你想要的结果,注意,MENU_ID 为数据库字段名。
SELECT * FROM sys_menu ORDER BY getPriority(MENU_ID)