DROP FUNCTION IF EXISTS diguihanshu; CREATE FUNCTION diguihanshu(areaId INT) RETURNS VARCHAR(4000) BEGIN DECLARE sid VARCHAR(4000); DECLARE sidChd VARCHAR(4000);SET sid='$'; SET sidChd = CAST(areaId AS CHAR);WHILE sidChd IS NOT NULL DO SET sid= CONCAT(sid,',',sidChd); SELECT GROUP_CONCAT(id) INTO sidChd FROM digui WHERE FIND_IN_SET(pid,sidChd)>0; END WHILE; RETURN sid; END;SELECT * FROM digui WHERE FIND_IN_SET(id,diguihanshu(6));
上面是创建一个存储过程。 下面才是查询 SELECT * FROM digui WHERE FIND_IN_SET(id,diguihanshu(1));
分级编码查询效率会高很多,但更新修改复杂。要否采用看你的编码体系是不是稳定,要不要经常变。
CREATE FUNCTION diguihanshu(areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sid VARCHAR(4000);
DECLARE sidChd VARCHAR(4000);SET sid='$';
SET sidChd = CAST(areaId AS CHAR);WHILE sidChd IS NOT NULL DO
SET sid= CONCAT(sid,',',sidChd);
SELECT GROUP_CONCAT(id) INTO sidChd FROM digui WHERE FIND_IN_SET(pid,sidChd)>0;
END WHILE;
RETURN sid;
END;SELECT * FROM digui WHERE FIND_IN_SET(id,diguihanshu(6));
SELECT * FROM digui WHERE FIND_IN_SET(id,diguihanshu(1));