对PDO准备好的语句强制使用精确的字符串MATCH [英] Force exact string MATCH for PDO prepared statements

问题描述

我最近采用了PDO准备的声明.在编写WHERE MATCH子句时，我曾经按照如下方式执行精确的字符串MATCH:

I have recently adopted PDO prepared statements. When writing WHERE MATCH clauses, I used to perform exact string MATCH as follows:

WHERE MATCH(some_column) AGAINST('\"$some_term\"')

效果很好.现在，我已切换为使用PDO bindValue，如下所示:

It worked perfectly. Now I have switched to using PDO bindValue as follows:

WHERE MATCH(some_column) AGAINST(:some_term)

将字符串与多个单词(例如"orange bike")绑定时，我得到"orange"和"bicycle"的结果，而我想要的却是"orange bike"的完全匹配.

When binding a string with multiple words such as "orange bicycle", I get results for "orange" and "bicycle" whereas what I want is an exact match for "orange bicycle".

如何为PDO准备的语句强制使用精确的字符串MATCH?

How do I force an exact string MATCH for my PDO prepared statements?

(我已经在该网站上进行了搜索和搜索，但是我在任何地方都找不到这个问题的讨论.我不能成为第一个提出这个问题的人，但是我找不到答案！)

(I have Googled and searched on this site yet I cannot find any discussion of this question anywhere. I can't be the first person to ask this question but I cannot find the answer!)

推荐答案

确保您在AGAINST中指定的变量周围加上引号.

Make sure you are putting quotes around the variable specified in AGAINST.

在PHP中:

$some_term = '"'.$some_term.'"'; // Adds quotes around string

$stmt = $db->prepare('SELECT * FROM example WHERE MATCH(some_column) AGAINST(:some_term)');
$stmt->bindParam(':some_term', $some_term, PDO::PARAM_STR);
$stmt->execute();

或者您也可以在MySQL语句中做到这一点:

Or you could do it in the MySQL statement as well:

$stmt = $db->prepare('SELECT * FROM example WHERE MATCH(some_column) AGAINST(CONCAT(\'"\',:some_term,\'"\')');
$stmt->bindParam(':some_term', $some_term, PDO::PARAM_STR);
$stmt->execute();

根据有关布尔型全文本搜索的MySQL文档:

用双引号(")字符引起的短语仅与按字面意义包含该短语的行匹配.全文引擎将短语分解为单词，并在FULLTEXT索引中搜索这些单词.非单词字符不需要完全匹配:短语搜索仅要求匹配项包含与短语完全相同的单词，并且顺序相同.例如，测试短语"匹配测试，词组".

A phrase that is enclosed within double quote (""") characters matches only rows that contain the phrase literally, as it was typed. The full-text engine splits the phrase into words and performs a search in the FULLTEXT index for the words. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".

如果该短语不包含索引中的单词，则结果为空.例如，如果所有单词都是停用词或短于索引单词的最小长度，则结果为空.

If the phrase contains no words that are in the index, the result is empty. For example, if all words are either stopwords or shorter than the minimum length of indexed words, the result is empty.

这篇关于对PDO准备好的语句强制使用精确的字符串MATCH的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！