問(wèn)題描述
我正在將我所有的站點(diǎn)代碼從使用 mysql_* 函數(shù)轉(zhuǎn)換為 PDO.PDO 上的 PHP 文檔不符合我的需要.提供了使用的功能，但在不同場(chǎng)景下不詳細(xì)說(shuō)明.
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.
基本上，我有一個(gè)mysql全文搜索:
Basically, I have a mysql fulltext search:
$sql = "SELECT ... FROM search_table WHERE MATCH(some_field) AGAINST ('{$searchFor}*' IN BOOLEAN MODE)";
實(shí)際的語(yǔ)句要長(zhǎng)得多，但這就是它的基本作用.
The actual statements much longer, but this is what it basically does.
我的問(wèn)題是，我如何將其合并到 PDO 中?
My question is, how would I incorporate this into PDO?
我知道您不打算在位置標(biāo)記周圍使用引號(hào)，所以您是否將它們留在 AGAINST() 函數(shù)中?我包括他們嗎?如果我將它們排除在外，通配符等會(huì)怎樣?
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 . '*');
推薦答案
不幸的是，這是使用查詢參數(shù)的一個(gè)奇怪的例外(但顯然不是在最近的點(diǎn)發(fā)布每個(gè) 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() 中的模式必須 是一個(gè)常量字符串，而不是一個(gè)查詢參數(shù).與 SQL 查詢中的其他常量字符串不同，這里不能使用查詢參數(shù)，僅僅是因?yàn)?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.
要將搜索模式安全地插入到查詢中，請(qǐng)使用 PDO::quote()功能.請(qǐng)注意，PDO 的 quote() 函數(shù)已經(jīng)添加了引號(hào)分隔符(與 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");
<小時(shí)>
來(lái)自@YourCommonSense 的重新評(píng)論:
Re comment from @YourCommonSense:
你說(shuō)得對(duì)，我剛剛在 MySQL 5.5.31、5.1.68 和 5.0.96 上測(cè)試了這個(gè)(MySQL Sandbox 是一個(gè)很棒的工具)，似乎這些版本確實(shí)接受了 AGAINST() 中的查詢參數(shù)動(dòng)態(tài) 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.
我仍然記得過(guò)去存在的沖突.也許它已在每個(gè)分支的最新版本中得到糾正.例如，我發(fā)現(xiàn)這些相關(guān)的錯(cuò)誤:
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() 子句中使用存儲(chǔ)過(guò)程參數(shù)總是返回相同的結(jié)果:http://bugs.mysql.com/bug.php?id=3734
- 準(zhǔn)備好的語(yǔ)句、MATCH 和 FULLTEXT 導(dǎo)致崩潰或奇怪的結(jié)果:http://bugs.mysql.com/bug.php?id=14496
這篇關(guān)于PDO 和 MySQL 全文搜索的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！