MySQL层级数据查询

MySQL层级数据查询

address表结构如下:

MySQL层级数据查询

address表有如下数据:

MySQL层级数据查询

现在创建一个函数,获取地址全称:

CREATE DEFINER=`root`@`localhost` FUNCTION `getFullName`(`id` varchar(1000)) RETURNS varchar(1000) CHARSET utf8mb4
    READS SQL DATA
BEGIN

	DECLARE fullName VARCHAR(1000);
	DECLARE parentId VARCHAR(1000);
	DECLARE parentName VARCHAR(1000);
	
	set fullName = (SELECT address.`name` FROM address WHERE address.id = id);
	set parentId = (SELECT address.parentId FROM address WHERE address.id = id);
	
	WHILE parentId IS NOT NULL DO
	    set parentName = (SELECT address.`name` FROM address WHERE address.id = parentId);
			set fullName = CONCAT(parentName,"/",fullName);
	    set parentId = (SELECT address.parentId FROM address WHERE address.id = parentId);
    END WHILE;
	
	RETURN fullName;
END

函数测试:

SELECT
	`address`.`id` AS `id`,
	`address`.`name` AS `name`,
	`getFullName` ( `address`.`id` ) AS `fullName` 
FROM
	`address`

MySQL层级数据查询

MySQL层级数据查询

上一篇:JDBC以及连接池连接MySQL出现时区错误问题--An attempt by a client to checkout a Connection has timed out


下一篇:如何彻底的卸载mysql