无界AI教程项目--MySql数据准备（五）

CREATE TABLE `T_TABLE_MENU_NODE` (
`NODE_ID` INT NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR(100) DEFAULT NULL,
`VALUE` VARCHAR(100) DEFAULT NULL,
`LFT` INT DEFAULT NULL,
`RGT` INT DEFAULT NULL,
PRIMARY KEY (`NODE_ID`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

2、插入数据

INSERT INTO `T_TABLE_MENU_NODE` VALUES
(1,"人物","figure",1,20),
(2,"动物","animal",2,13),
(3,"环境","environment",14,19),
(4,"风格","fashion",3,4),
(5,"魔法滤镜","Magic filter",5,10),
(6,"数量","quantity",11,12),
(7,"角色","role",15,16),
(8,"长相","appearance",17,18),
(9,"服装造型","clothing shape",6,7),
(10,"表情","expression",8,9);

三、函数

1、创建函数（获取指定节点的层级）

（1）创建流程

① 找到Functions

② 右键Functions选择“Create Functions..”

③ 撰写函数体后，点击 Apply

CREATE FUNCTION LAYERCALCULATE(P_NODE_ID INT) RETURNS int
BEGIN
DECLARE P_RESULT,P_LFT,P_RGT INT DEFAULT 0;
IF EXISTS (SELECT 1 FROM T_TABLE_MENU_NODE WHERE NODE_ID = P_NODE_ID) THEN
BEGIN
SELECT NODE_LEFT, NODE_RIGHT INTO P_LFT, P_RGT FROM T_TABLE_MENU_NODE WHERE NODE_ID = P_NODE_ID;
SELECT COUNT(*) INTO P_RESULT FROM T_TABLE_MENU_NODE WHERE NODE_LEFT <= P_LFT AND NODE_RIGHT >= P_RGT;
END;
RETURN P_RESULT;
END IF;
RETURN 0;
END

（2）报错处理

① 函数支持错误

报错信息：

[Err] 1418 - This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

结局办法：

set global log_bin_trust_function_creators=TRUE;

四、视图

1、创建视图（获取包含菜单节点及相应层级的视图）

（1）创建流程

① 找到views

② 右键Views选择“Create Views..”

③ 撰写创建SQL后，点击 Apply

CREATE VIEW V_MENU_NODES AS
SELECT
	NODE_ID, 
    NODE_NAME, 
    NODE_VALUE,
    NODE_LEFT,
    NODE_RIGHT, 
    LAYERCALCULATE(NODE_ID) AS LAYER
FROM T_TABLE_MENU_NODE 
ORDER BY NODE_LEFT

（2）查看视图

五、储存过程

1、创建流程

① 找到Stored Procedures

② 右键Stored Procedures选择“Create Stored Procedures..”

③ 撰写创建SQL后，点击 Apply

2、创建具体的储存过程

（1）获取指定节点的全部子节点信息

CREATE PROCEDURE getNamedChildNodes(IN P_NODE_ID INT)
BEGIN
DECLARE P_LFT,P_RGT INT DEFAULT 0;
IF EXISTS (SELECT NODE_ID FROM T_TABLE_MENU_NODE WHERE NODE_ID = P_NODE_ID) THEN
BEGIN
SELECT NODE_LEFT, NODE_RIGHT INTO P_LFT,P_RGT FROM T_TABLE_MENU_NODE WHERE NODE_ID = P_NODE_ID;
SELECT * FROM V_MENU_NODES WHERE NODE_LEFT BETWEEN P_LFT AND P_RGT ORDER BY LAYER, NODE_LEFT;
END;
END IF;
END

（2）获取指定节点的全部父节点信息

CREATE PROCEDURE getNamedParentNodes(IN P_NODE_ID INT)
BEGIN
DECLARE P_LFT,P_RGT INT DEFAULT 0;
IF EXISTS (SELECT NODE_ID FROM T_TABLE_MENU_NODE WHERE NODE_ID = P_NODE_ID) THEN
BEGIN
SELECT NODE_LEFT, NODE_RIGHT INTO P_LFT,P_RGT FROM T_TABLE_MENU_NODE WHERE NODE_ID = P_NODE_ID;
SELECT * FROM V_MENU_NODES  WHERE NODE_LEFT < P_LFT ORDER BY LAYER, NODE_LEFT ASC;
END;
END IF;
END

（3）在指定节点下添加子节点

CREATE DEFINER=`root`@`localhost` PROCEDURE `appendSubNamedNode`(IN P_NODE_ID INT,IN P_NODE_NAME VARCHAR(100), IN P_NODE_VALUE VARCHAR(100))
BEGIN
DECLARE P_RIGHT_NODE INT DEFAULT 0;
IF EXISTS(SELECT NODE_ID FROM T_TABLE_MENU_NODE WHERE NODE_ID = P_NODE_ID) THEN
BEGIN
SET AUTOCOMMIT = 0;
START TRANSACTION;
SELECT NODE_RIGHT INTO P_RIGHT_NODE FROM T_TABLE_MENU_NODE WHERE NODE_ID = P_NODE_ID;
UPDATE T_TABLE_MENU_NODE SET NODE_RIGHT = NODE_RIGHT + 2 WHERE NODE_RIGHT >= P_RIGHT_NODE;
UPDATE T_TABLE_MENU_NODE SET NODE_LEFT = NODE_LEFT + 2 WHERE NODE_LEFT >= P_RIGHT_NODE;
INSERT INTO T_TABLE_MENU_NODE(NODE_NAME, NODE_VALUE, NODE_LEFT, NODE_RIGHT) VALUES(P_NODE_NAME, P_NODE_VALUE, P_RIGHT_NODE, P_RIGHT_NODE + 1);
COMMIT;
END;
END IF;
END

（4）删除指定节点

CREATE PROCEDURE `deleteNamedNode`(IN P_NODE_ID INT)
BEGIN
DECLARE P_LEFT_NODE,P_RIGHT_NODE INT DEFAULT 0;
IF EXISTS(SELECT P_NODE_ID FROM T_TABLE_MENU_NODE WHERE NODE_ID = P_NODE_ID) THEN
START TRANSACTION;
SELECT NODE_LEFT,NODE_RIGHT INTO P_LEFT_NODE,P_RIGHT_NODE FROM T_TABLE_MENU_NODE WHERE NODE_ID = P_NODE_ID;
DELETE FROM T_TABLE_MENU_NODE WHERE NODE_LEFT >= P_LEFT_NODE AND NODE_RIGHT <= P_RIGHT_NODE;
UPDATE T_TABLE_MENU_NODE SET NODE_LEFT = NODE_LEFT - (P_RIGHT_NODE - P_LEFT_NODE + 1) WHERE NODE_LEFT > P_LEFT_NODE;
UPDATE T_TABLE_MENU_NODE SET NODE_RIGHT = NODE_RIGHT - (P_RIGHT_NODE - P_LEFT_NODE + 1) WHERE NODE_RIGHT > P_RIGHT_NODE;
COMMIT;
END IF;
END