CREATE FUNCTION getTypeChildList
(rootId BIGINT)
RETURNS @service_type TABLE (type_id VARCHAR (1000))
AS
BEGIN
DECLARE sChildList VARCHAR (1000);
DECLARE sChildTemp VARCHAR (1000);
SET sChildTemp = cast(rootId AS CHAR);
WHILE sChildTemp IS NOT NULL DO
IF (sChildList IS NOT NULL) THEN
INSERT INTO @service_type VALUES (sChildTemp);
END IF;
SELECT group_concat(type_id) INTO sChildTemp FROM service_type where FIND_IN_SET(parent_id,sChildTemp)>0;
END WHILE;
RETURN;
END;
表:CREATE TABLE `service_type` (
type_id
bigint(20) NOT NULL,type_name
varchar(100) DEFAULT NULL COMMENT '类型名称',parent_id
bigint(20) DEFAULT NULL COMMENT '上一级类型id',
PRIMARY KEY (type_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;