相关函数
String Operators
FIND_IN_SET()
Return the index position of the first argument within the second argument
CONCAT()
Return concatenated string
Aggregate (GROUP BY) Functions
GROUP_CONCAT() Return a concatenated string
mysql> SELECT student_name,GROUP_CONCAT(test_score)
FROM student
GROUP BY student_name;
Or:
mysql> SELECT student_name,
GROUP_CONCAT(DISTINCT test_score
ORDER BY test_score DESC SEPARATOR ' ')
FROM student
GROUP BY student_name
The default separator between values in a group is comma (,).
查询所有子节点,包括当前节点,用,分隔,,限制循环层级最大为10,保证始终可退出递归
CREATE DEFINER=`root`@`%` FUNCTION `getChildList`(rootId bigint) RETURNS varchar(2000) CHARSET utf8
BEGIN
declare str varchar(2000);
declare cid varchar(2000);
declare level_count int;
set str = '';
set cid = cast(rootId as char);
set level_count = 0;
while cid is not null and level_count<10 do
set str = concat(str,',',cid);
select group_concat(id) into cid from product_category where find_in_set(parent_id,cid)>0;
set level_count = level_count+1;
end while;
return substring(str,2);
END
CREATE DEFINER=`root`@`%` FUNCTION `getParentList`(nodeId bigint) RETURNS varchar(2000) CHARSET utf8
BEGIN
declare pid bigint default null;
declare str varchar(2000) default nodeId;
while (nodeId is not null)
do
set pid=(select parent_id from product_category where id=nodeId);
if pid is not null then
set str = concat(str,',',pid);
end if;
set nodeId=pid;
end while;
RETURN str;
END