MySQL创建函数不会返回集合 [英] MySQL create function won't return sets

问题描述

我无法在 MySQL 5.x 上完成这项工作.此函数应从 select 语句中返回所有相关行:

I can't get this work with MySQL 5.x. This function should return all relevent rows from the select-statement:

CREATE FUNCTION getuids(root INT)
RETURNS SET OF INT AS $$
BEGIN
RETURN SELECT uid FROM pages WHERE deleted = 0
END
$$ LANGUAGE 'SQL';

知道我做错了什么吗?

推荐答案

MySQL 函数可能不会返回 MySQL 中的一行或一组行.只有存储过程可以返回行.直到并包括 MySQL 5.4 都是如此.请参阅限制页面.将存储过程用作表或查询中的 where 子句值也不可用.

A MySQL function may not return a row or set of rows in MySQL. Only a stored procedure can return rows. This is true up to and including MySQL 5.4. See the Limitations page. Usage of stored procedures as tables or where clause values in queries is also unavailable.

如果您需要使用函数，您可以做的最好的事情是使用 GROUP_CONCAT 将值放入逗号分隔的列表中:

About the best you can do if you need to use a function is to use GROUP_CONCAT to get the values into a comma-separated list:

CREATE FUNCTION getuids() RETURNS VARCHAR(1024)
BEGIN
SELECT GROUP_CONCAT(uid SEPARATOR ',') INTO @uid_list FROM pages WHERE deleted = 0;
RETURN @uid_list;
END

然后在需要检查uid的select语句中，您可以像这样使用FIND_IN_SET函数:

Then in your select statement that needs to check the uids, you can use the FIND_IN_SET function like this:

SELECT ... WHERE FIND_IN_SET(my_field, get_uids());

附言我最初在 MySQL 网站上发布了指向 GROUP_CONCAT 和 FIND_IN_SET 函数的链接，但 Stack Overflow 只允许我使用一个链接.是的，因为是新人.

P.S. I had originally posted links to the GROUP_CONCAT and FIND_IN_SET functions on the MySQL website, but Stack Overflow would only let me use one link. Yay for being new.

这篇关于MySQL创建函数不会返回集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！