php - 高级 MySQL 搜索查询 - 使用 RLIKE

Question

我正在为 Joomla/Virtuemart 中的产品搜索模块处理 MySQL 搜索查询。我实际上正在尝试将现有的 MySQL 查询从使用 MATCH/AGAINST 修改为使用 RLIKE，但我修改后的查询出现错误..

这是使用 MATCH/AGAINST 的原始查询，没有错误:

$searchstring = " +search* +string* +test*";
$query ="SELECT p.virtuemart_product_id, l.product_name from #__virtuemart_products AS p, #__virtuemart_products_".VMLANG." AS l WHERE MATCH(product_name,customtitle) AGAINST ('".$searchstring."' IN BOOLEAN MODE) AND p.published = '1' AND p.virtuemart_product_id = l.virtuemart_product_id  LIMIT 0,".$prods." union (select p.virtuemart_product_id, l.product_name from #__virtuemart_products AS p, #__virtuemart_products_".VMLANG." as l where  MATCH(product_sku) AGAINST ('".$searchstring."' IN BOOLEAN MODE) and p.published = '1'  and p.virtuemart_product_id = l.virtuemart_product_id LIMIT 0,".$prods.")";

这是我使用 RLIKE 更改的查询:

$searchstring = "search|string|test";
$query ="SELECT p.virtuemart_product_id, l.product_name from #__virtuemart_products AS p, #__virtuemart_products_".VMLANG." AS l WHERE product_name,customtitle RLIKE '".$searchstring."' AND p.published = '1' AND p.virtuemart_product_id = l.virtuemart_product_id LIMIT 0,".$prods." union (select p.virtuemart_product_id, l.product_name from #__virtuemart_products AS p, #__virtuemart_products_".VMLANG." as l where product_sku RLIKE '".$searchstring."' and p.published = '1'  and p.virtuemart_product_id = l.virtuemart_product_id LIMIT 0,".$prods.")";

我不知道为什么 RLIKE 搜索查询不起作用。我希望有人能指出我在这里做错了什么..

Answer 1

您的查询中有这个奇怪的表达式:

WHERE product_name, customtitle RLIKE '".$searchstring."'

首先尝试使用 concat() 将它们组合起来:

(SELECT p.virtuemart_product_id, l.product_name
 from #__virtuemart_products p join
      #__virtuemart_products_".VMLANG." l
      on p.virtuemart_product_id = l.virtuemart_product_id
 WHERE concat(product_name, customtitle) RLIKE '".$searchstring."' AND
       p.published = '1'
 LIMIT 0,".$prods."
)
union
(select p.virtuemart_product_id, l.product_name
 from #__virtuemart_products p join
      #__virtuemart_products_".VMLANG." l
      on p.virtuemart_product_id = l.virtuemart_product_id
 where product_sku RLIKE '".$searchstring."' and
       p.published = '1'
 LIMIT 0,".$prods.
)

您还可以分别比较每一个:

 WHERE (product_name RLIKE '".$searchstring."' OR
        customtitle RLIKE '".$searchstring."' 
       ) AND . . .

请注意，我还修复了 join 语法以使用显式联接。