在WHERE IN子句中使用自定义MySQL函数的结果

Question

我正在尝试在查询的WHERE IN子句中使用自定义MySQL函数的结果：

SELECT * 
  FROM project p 
 WHERE p.category_id IN (getCategorys(1))

自定义函数的结果如下：

SELECT getCategorys(1)

 1,2,3,4,5

不幸的是，这并不能得到与以下查询相同的结果：

SELECT * FROM project p WHERE p.category_id IN (1,2,3,4,5)

使用自定义函数的查询仅返回category_id = 1的结果。

我在这里缺少了什么？

- ViBoNaCci

Answer 1

需要翻译的内容：

从问题描述中无法确定自定义函数所得到的表单形式，但是 IN 接受列表或结果，而不是字符串。我猜测该函数返回一个字符串。

从性能角度来看并不理想，但是使用 FIND_IN_SET 可能更容易产生您想要的行为。

- Uueerdo

---

FIND_IN_SET确实有效：SELECT * FROM project AS p WHERE FIND_IN_SET(p.category_id, getCategorys(1))你对性能的评论让我有点害怕。我该如何返回类似于csv字符串的东西，以便可以实际使用IN？ - ViBoNaCci

@ViBoNaCci Bill 提供了唯一的替代答案。在 MySQL 中除了这两个建议之外，没有其他方法。 - Shadow

@ViBoNaCci Bill涵盖了我应该详细说明的性能问题。基本上，几乎每当您必须在条件中对数据使用函数(用户甚至内置的，比如FIND_IN_SET)时，就会消除索引帮助查询更快执行的可能性。如果您的数据库访问方法不排除像那样的动态sql，Bill的答案会更好; 虽然在这种情况下，在客户端运行两个具有相同效果的查询仍将优于此方法。 - Uueerdo

是的，如果您使用生成列，则可以索引某些情况，但这不是其中之一。 - Bill Karwin

Answer 2

如果您使用@Uueerdo提到的FIND_IN_SET()解决方案，它将始终执行表扫描。 它无法使用索引。

使此解决方案与索引优化一起使用的唯一方法是在准备查询之前获取类别ID的字符串。 换句话说，执行两个查询。

类似于以下内容（但我还没有测试过）：

SELECT getCategorys(1) INTO @tmp;

SET @sql = CONCAT('
  SELECT * 
  FROM project p 
  WHERE p.category_id IN (', @tmp, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

即使使用你自己的函数生成内容，这在技术上仍然属于SQL注入的情况。请确保你对getCategorys()函数返回的内容具有完全控制。

例如，该函数是否会返回空字符串？这将导致IN ( )语法错误。

- Bill Karwin

---

我显然把事情复杂化了。提前获取IN子句的内容就可以了。 - ViBoNaCci