MySQL与全文搜索匹配，无法与php PDO绑定Param动态工作

Question

我用jQuery UI在我的网站上实现了ajax自动完成搜索，它做得很好，但是我在选择不想工作时遇到了问题。

database中的autocomplete_search_name字段已经FULLTEXT index了。

下面的select在静态模式下工作正常，这就是我希望搜索的工作方式，**

$stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST('*psico*' IN BOOLEAN MODE) ORDER BY MATCH(autocomplete_search_name) AGAINST('*psico*') DESC LIMIT 10");
$stmt->execute();
$moddados = $stmt->fetchAll();
return $moddados;

但是，当我开始使用 PDO 绑定参数或 PDO 引用动态插入参数时，如下例所示，选择不再有效。我研究了很多，但找不到解决这个问题的方法。

$data['query'] = '*'.$data['query'].'*';
$stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST(:query IN BOOLEAN MODE) ORDER BY MATCH(autocomplete_search_name) AGAINST(:query) DESC LIMIT 10");
$stmt->bindParam(':query', $data['query']);
$stmt->execute();
$moddados = $stmt->fetchAll();
return $moddados;

我在下面尝试过这种方式，但它也不起作用：

$stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST(CONCAT('*', :query, '*') IN BOOLEAN MODE) ORDER BY MATCH(autocomplete_search_name) AGAINST(CONCAT('*', :query, '*')) DESC LIMIT 10");
$stmt->bindParam(':query', $data['query']);
$stmt->execute();
$moddados = $stmt->fetchAll();
return $moddados;

而且这种方式也不起作用

$stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST(':query' IN BOOLEAN MODE) ORDER BY MATCH(autocomplete_search_name) AGAINST(':query') DESC LIMIT 10");
$stmt->bindParam(':query', '*' . $data['query'] . '*');
$stmt->execute();
$moddados = $stmt->fetchAll();
return $moddados;

Answer 1

这是因为你在单引号内有:query，所以MySQL将其视为字符串，而不是参数。

可以通过将'*:query*'替换为:query并将绑定更改为：

$stmt->bindParam(':query', '*' . $data['query'] . '*');

或者您可以将'*:query*'替换为CONCAT('*', :query, '*')

您可能还会遇到在一个查询中两次使用相同的参数名称的限制(请参阅手册((仅当您不使用模拟的预准备语句时才会发生(。在这种情况下，您需要更改代码，如下所示：

$stmt = $this->db->prepare("SELECT autocomplete_search_name as label, url as the_link FROM product 
WHERE status = 'active' AND MATCH(autocomplete_search_name) AGAINST(CONCAT('*', :query1, '*') IN BOOLEAN MODE) 
ORDER BY MATCH(autocomplete_search_name) AGAINST(CONCAT('*', :query2, '*')) DESC 
LIMIT 10");
$stmt->bindParam(':query1', $data['query']);
$stmt->bindParam(':query2', $data['query']);
$stmt->execute();