PDO 和 MySQL 全文搜索

2021-12-26 00:00:00 full-text-search php mysql pdo

我正在将我所有的站点代码从使用 mysql_* 函数转换为 PDO.PDO 上的 PHP 文档不符合我的需要.提供了使用的功能，但在不同场景下不详细说明.

I'm converting all my sites code from using mysql_* functions to PDO. The PHP documentation on PDO is not clear for my needs. It gives you the functions to use, but does not go into detail to explain them in different scenarios.

基本上，我有一个mysql全文搜索:

Basically, I have a mysql fulltext search:

$sql = "SELECT ... FROM search_table WHERE MATCH(some_field) AGAINST ('{$searchFor}*' IN BOOLEAN MODE)";

实际的语句要长得多，但这就是它的基本作用.

The actual statements much longer, but this is what it basically does.

我的问题是，我如何将其合并到 PDO 中?

My question is, how would I incorporate this into PDO?

我知道您不打算在位置标记周围使用引号，所以您是否将它们留在 AGAINST() 函数中?我包括他们吗?如果我将它们排除在外，通配符等会怎样?

I know you're not meant to use quotes around the place-marker, so do you leave them out in the AGAINST() function? Do I include them? If I leave them out, what happens to the wildcard symbol etc?

$sql = $this->db->prepare("SELECT ... FROM search_table WHERE MATCH(some_field) AGAINST(:searchText IN BOOLEAN MODE");
$sql->bindValue(':searchText', $searchFor . '*');

推荐答案

不幸的是，这是使用查询参数的一个奇怪的例外(但显然不是在最近的点发布每个 MySQL 分支，见下文).

This is unfortunately a weird exception to the use of query parameters (edit: but apparently not in the most recent point-release of each MySQL branch, see below).

AGAINST() 中的模式必须 是一个常量字符串，而不是一个查询参数.与 SQL 查询中的其他常量字符串不同，这里不能使用查询参数，仅仅是因为 MySQL 中的限制.

The pattern in AGAINST() must be a constant string, not a query parameter. Unlike other constant strings in SQL queries, you cannot use a query parameter here, simply because of a limitation in MySQL.

要将搜索模式安全地插入到查询中，请使用 PDO::quote()功能.请注意，PDO 的 quote() 函数已经添加了引号分隔符(与 mysql_real_escape_string() 不同).

To interpolate search patterns into queries safely, use the PDO::quote() function. Note that PDO's quote() function already adds the quote delimiters (unlike mysql_real_escape_string()).

$quoted_search_text = $this->db->quote('+word +word');

$sql = $this->db->prepare("SELECT ... FROM search_table 
    WHERE MATCH(some_field) AGAINST($quoted_search_text IN BOOLEAN MODE");

<小时>

来自@YourCommonSense 的重新评论:

Re comment from @YourCommonSense:

你说得对，我刚刚在 MySQL 5.5.31、5.1.68 和 5.0.96 上测试了这个(MySQL Sandbox 是一个很棒的工具)，似乎这些版本确实接受了 AGAINST() 中的查询参数动态 SQL 查询的子句.

You're right, I just tested this on MySQL 5.5.31, 5.1.68, and 5.0.96 (MySQL Sandbox is a wonderful tool), and it seems that these versions do accept query parameters in the AGAINST() clause of a dynamic SQL query.

我仍然记得过去存在的冲突.也许它已在每个分支的最新版本中得到纠正.例如，我发现这些相关的错误:

I still have a recollection of a conflict existing in the past. Maybe it has been corrected in the most recent point-release of each branch. For example, I find these related bugs:

  • 在 AGAINST() 子句中使用存储过程参数总是返回相同的结果:http://bugs.mysql.com/bug.php?id=3734
  • 准备好的语句、MATCH 和 FULLTEXT 导致崩溃或奇怪的结果:http://bugs.mysql.com/bug.php?id=14496

相关文章