08.函数

标量函数

DELIMITER $$;
CREATE FUNCTION getCategoryNames(categories VARCHAR(500))
RETURNS VARCHAR(500)
BEGIN

    DECLARE rtn VARCHAR(500);
    DECLARE ins VARCHAR(500);
    IF right(categories,1) != ','
      THEN SET ins = concat(categories,',');
    END IF;

    SELECT group_concat(b.name)
    FROM
    (
      SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(ins,',',help_topic_id+1),',',-1) AS categorySid
      FROM mysql.help_topic
      WHERE help_topic_id < LENGTH(ins)-LENGTH(REPLACE(ins,',',''))+1

    ) a
    LEFT OUTER JOIN contactcategory b
    ON a.categorySid = b.sid
    INTO rtn;

    RETURN rtn;
END $$;

DELIMITER ;

表值函数

MYSQL中没有表值函数，可以通过存储过程将字符串以固定分隔符映射为数据表，点我查看 或者使用下面代码简单的实现：

SET @content = '7654,7698,7782,7788';
SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(@content,',',help_topic_id+1),',',-1) AS num
FROM
    mysql.help_topic
WHERE
    help_topic_id < LENGTH(@content)-LENGTH(REPLACE(@content,',',''))+1;