//将new_menu表和new_permission表变成一张表的视图;
DELIMITER $$
USE `weiqi_stage`$$
DROP VIEW IF EXISTS `permissionset`$$
CREATE ALGORITHM=UNDEFINED DEFINER=`backup_user`@`%` SQL SECURITY DEFINER VIEW `permissionset` AS (
SELECT `sm`.`MENU_ID` AS `ID`,`sm`.`PARENT_ID` AS `PID`,`sm`.`TEXT` AS `TEXT`,`sm`.`SORT_VALUE` AS `SORT_VALUE` FROM `new_menu` `sm`) UNION (SELECT `sp`.`ID` AS `ID`,`sp`.`MENU_ID` AS `PID`,`sp`.`NAME` AS `TEXT`,`sp`.`SORT_VALUE` AS `SORT_VALUE` FROM `new_permission` `sp`)$$
DELIMITER ;
//根据子id找到所有的父id的函数
DELIMITER $$
USE `weiqi_stage`$$
DROP FUNCTION IF EXISTS `getNewMenuParentList`$$
CREATE DEFINER=`backup_user`@`%` FUNCTION `getNewMenuParentList`(childId VARCHAR(100)) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPad VARCHAR(1000);
SET sTemp = '';
SET sTempPad =childId;
WHILE sTempPad IS NOT NULL DO
SET sTemp = CONCAT(sTemp,',',sTempPad);
SELECT GROUP_CONCAT(PARENT_ID) INTO sTempPad FROM new_menu WHERE FIND_IN_SET(MENU_ID,sTempPad)>0;
END WHILE;
RETURN sTemp;
END$$
DELIMITER ;
//根据父id找到所有的子id的函数
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
FUNCTION `weiqi_stage`.`getchildrens`(rootId VARCHAR(100)) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPad VARCHAR(1000);
SET sTemp = '';
SET sTempPad =rootId;
WHILE sTempPad IS NOT NULL DO
SET sTemp = CONCAT(sTemp,',',sTempPad);
SELECT GROUP_CONCAT(MENU_ID) INTO sTempPad FROM new_menu WHERE FIND_IN_SET(PARENT_ID,sTempPad)>0;
END WHILE;
RETURN sTemp;
END$$
DELIMITER ;