多个REGEXP值在SQL中不起作用

Question

我已经为我的网站创建了一个动态查询。查询使用以下方法完成：

$sql='SELECT CONCAT(issues.type,"0",issues.kbid) as KBID,issue_tasks.PARENTID as Parent,issues.issuesummary as Summary,products.productdescription as Product,organizations.shortname as Organization,issue_priorities.description as Priority,date_format(issues.createddate, "%d/%m/%Y") as Reported,date_format(issues.lastupdated, "%d/%m/%Y") as Updated,issue_status.statusdescription as Status,issue_resolutions.resdescription as Resolution,users.logon as Assigned FROM issues
INNER JOIN issue_priorities ON issue_priorities.VALUE = issues.PRIORITY - 1 
INNER JOIN issue_resolutions ON issue_resolutions.RESID = issues.RESOLUTION 
INNER JOIN users ON users.ID = issues.ASSIGNEDUSERID
INNER JOIN products ON products.PRODUCTID = issues.PRODUCTID
INNER JOIN organizations ON organizations.orgid = issues.creatingorg
INNER JOIN issue_status ON issue_status.STATUSID = issues.STATUS
LEFT JOIN issue_tasks ON issue_tasks.CHILDID = issues.KBID
WHERE ';
if(isset($_SESSION['summ']))
{
$sql.= sprintf('issues.issuesummary REGEXP "%s"', implode('|', $words));
}

编辑：我的完整SQL查询：

SELECT CONCAT(issues.type,"0",issues.kbid) as KBID,issue_tasks.PARENTID as Parent,issues.issuesummary as Summary,products.productdescription as Product,organizations.shortname as Organization,issue_priorities.description as Priority,date_format(issues.createddate, "%d/%m/%Y") as Reported,date_format(issues.lastupdated, "%d/%m/%Y") as Updated,issue_status.statusdescription as Status,issue_resolutions.resdescription as Resolution,users.logon as Assigned FROM issues INNER JOIN issue_priorities ON issue_priorities.VALUE = issues.PRIORITY - 1 INNER JOIN issue_resolutions ON issue_resolutions.RESID = issues.RESOLUTION INNER JOIN users ON users.ID = issues.ASSIGNEDUSERID INNER JOIN products ON products.PRODUCTID = issues.PRODUCTID INNER JOIN organizations ON organizations.orgid = issues.creatingorg INNER JOIN issue_status ON issue_status.STATUSID = issues.STATUS LEFT JOIN issue_tasks ON issue_tasks.CHILDID = issues.KBID WHERE issues.issuesummary REGEXP "sabre|rtf"

但我一直收到这样的错误：

SQLSTATE[42000]：语法错误或访问冲突：1139从regexpSQLSTATE*42000]中得到错误"empty(sub)expression"：语法错误和访问冲突：113从regexpCannot execute query！！！

我不确定为什么我在这里得到一个封装错误。

Answer 1

假设$words包含要在正则表达式中使用的单词，则可以简化代码：

if (isset($_SESSION['summ']) && count($words)) {
$sql .= sprintf('issues.issuesummary REGEXP "%s"', implode('|', $words);
}

Answer 2

您遇到的错误ER_REGEXP_ERROR是MySQL服务器在您提供的正则表达式模式无法解析时生成的错误。换句话说，您的正则表达式在语法上是不正确的。

在特定情况下给出的消息">空(子)表达式"表明正则表达式包含一个空的子表达式。如果看不到代码生成的最后一个正则表达式，就无法确定——但由于您将许多子表达式与|交替运算符组合在一起，因此可以合理地猜测这些子表达式中的一个或多个是空的：如果$words数组的至少一个元素(转换为)空字符串''，就会发生这种情况。

尽管如此，我们还是不清楚为什么要在这里使用正则表达式。如果$words包含正则表达式模式，那么这可能是一种合理的方法——尽管在这种情况下，在连接每个子表达式时，确实应该将它们放在括号中(以避免任何优先级低于交替的运算符干扰您的意图)。另一方面，如果$words只包含您希望精确匹配的文字值，MySQL的IN()运算符将更高效、更合适、更正确：

WHERE issues.issuesummary IN ('abc', 'xyz', ...)

这可以通过以下PHP代码实现：

$sql .= "issues.issuesummary IN ('".implode("','", $words)."')";

但是，请注意，$words的元素必须首先进行转义，以避免出现某些字符的错误(这也会导致SQL注入漏洞)！更好的是，您可以将每个文字参数化。

如何做这两件事将取决于你使用的是哪一个MySQL API——如果你在问题中没有指出任何问题，我认为具体的例子超出了这个答案的范围。