PDO和MySQL全文搜索

PDO和MySQL全文搜索

关注
发信
关注(28)粉丝(399)

PDO和MySQL全文搜索

本文介绍了PDO和MySQL全文搜索的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我将所有使用mysql_ *函数的网站代码转换为PDO。关于PDO的PHP文档并不清楚我的需求。它给你使用的功能，但没有详细解释它们在不同的情况。



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

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

实际报表要长得多，但这基本上就是这样。



我的问题是，我将如何将其纳入PDO？



我知道你不打算在位置标记周围使用引号，那么你是否在AGAINST（）函数中保留它们？我包括他们吗？如果我将它们排除在外，通配符等会发生什么情况？

  $ sql = $ this-> db-> prepare（SELECT ... FROM search_table WHERE MATCH（some_field）AGAINST（：searchText IN BOOLEAN MODE）; 
 $ sql-> bindValue（'：searchText'，$ searchFor。'*'）;


解决方案

使用查询参数很奇怪 edit：，但显然不在每个MySQL分支的最新版本中，请参阅下文）。


$ b $ < AGAINST（） must 是一个常量字符串，而不是一个查询参数。与SQL查询中的其他常量字符串不同，这里不能使用查询参数，只是因为限制在MySQL中。



要安全地将搜索模式内插到查询中，请使用函数请注意，PDO的quote（）函数已经增加了现有的te分隔符（不同于mysql_real_escape_string（））。

  $ quoted_search_text = $ this-> db-> quote（'+ word +字'）; 
 
 $ sql = $ this-> db-> prepare（SELECT ... FROM search_table 
 WHERE MATCH（some_field）AGAINST（$ quoted_search_text IN BOOLEAN MODE）;





来自@YourCommonSense的评论：



你说得对，我只是在MySQL 5.5.31,5.1.68和5.0.96（MySQL Sandbox是一个很棒的工具）上测试过这个版本，看起来这些版本确实接受查询参数在动态SQL查询的AGAINST（）子句中。



我仍然记得过去存在的冲突，也许它已在最近一次更正例如，我发现这些相关的错误：




  • 总是在AGAINST（）子句中使用存储过程参数返回相同的结果：

  • 预处理语句MATCH和FULLTEXT导致崩溃或奇怪的结果：


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.

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.

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

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 . '*');
解决方案

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).

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.

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");

Re comment from @YourCommonSense:

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:

这篇关于PDO和MySQL全文搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

07-29 11:10
Powered by LMLPHP ©2024 PDO和MySQL全文搜索 0.048679