的PHP/MySQL的.是否有mysql函数来检测至少一个mysql行字段中是否存在至少2个特定单词 [英] php/mysql. Is there mysql function to detect if at least 2 certain words exists in at least one mysql row-field
问题描述
例如，我有php数组$php_arr_to_check = array("green", "red", "blue");
For example, I have php array $php_arr_to_check = array("green", "red", "blue");
并且有很多这样的mysql行
And have many mysql rows like this
Id | TextToCheckIn
______________________
1 | green brown blue
2 | black yellow white red
3 | yellow green black red grey white
想要循环$php_arr_to_check遍历列TextToCheckIn中的每一行并返回Id，例如，其中TextToCheckIn中至少存在$php_arr_to_check个单词.
Want as if to loop $php_arr_to_check through each row in column TextToCheckIn and return Id where, for example, at least 2 words from $php_arr_to_check exists in TextToCheckIn.
有可能吗?是否存在任何mysql函数?
Is it possible? Does any mysql function exist?
这是php的示例(我想要得到什么)
Here is example with php (what I want to get)
$php_arr_to_check = array("green", "red", "blue");
echo '<pre>', print_r($php_arr_to_check, true), '</pre> php_arr_to_check <br/>';
$mysql_rows = array(
array("green", "brown", "blue"),
array("black", "yellow", "white", "red"),
array("yellow", "green", "black", "red", "grey", "white")
);
echo '<pre>', print_r($mysql_rows, true), '</pre> mysql_rows <br/>';
foreach( $mysql_rows as $one_row ){
//echo count(array_intersect($php_arr_to_check, $one_row)). ' count array_intersect __ <br/>';
if( count(array_intersect($php_arr_to_check, $one_row)) > 1 ){
$arr_with_count_matches[] = count(array_intersect($php_arr_to_check, $one_row));
$maches_exist = true;
}
}
echo '<pre>', print_r($arr_with_count_matches, true), '</pre> arr_with_count_matches <br/>';
想要将php数组与mysql列进行比较得到相同的结果.
Want to get the same result comparing php array to mysql column.
有没有办法做到这一点(没有LIKE)?可能以某种方式与MATCH AGAINST在一起?
Any ideas how to do that (without LIKE)? May be with MATCH AGAINST somehow?
在我在下面使用之前，但好像出现了误报....
Before i used below, but got as if false positives....
SELECT
Id FROM table
WHERE MATCH (TextToCheckIn) AGAINST (?) LIMIT 1
会尝试
SELECT
Id FROM table
WHERE MATCH (SUBSTRING_INDEX(TextToCheckIn, ' ', 3)) AGAINST (?) LIMIT 1
据了解，我将比较TextToCheckIn中的前3个单词与php数组.但是不明白我会得到什么.在此处阅读 http://www3.physnet.uni-hamburg.de/physnet /mysql/manual_Fulltext_Search.html
As understand I will compare first 3 words in TextToCheckIn against php array. But do not understand what i will get. Reading here http://www3.physnet.uni-hamburg.de/physnet/mysql/manual_Fulltext_Search.html
对于表中的每一行，它都会返回相关性-该行中的文本(属于集合的列中的文本)与查询之间的相似性度量.
For every row in a table it returns relevance - a similarity measure between the text in that row (in the columns that are part of the collection) and the query.
似乎完全是必要的...需要检查
Seems it is exactly what is necessary... need to check
推荐答案
我认为您想使用如果不使用运算符+ -来匹配诸如green red blue之类的所有行，则返回所有行，其中一条记录至少包含一个单词:green或red或blue.
If you match without operators + - against such as green red blue all rows are returned, where a record contains at least one word: green or red or blue.
IN BOOLEAN MODE，如果没有运算符，则每个匹配的单词的得分都将为1.因此，如果有一个记录匹配三个词中的两个，它将得到2.
IN BOOLEAN MODE and without operators each matched word will score 1. So if there's a record matching two out of the three words it would score 2.
要获得至少2分的行:
SELECT *,
MATCH (`TextToCheckIn`) AGAINST ('green red blue' IN BOOLEAN MODE) `score`
FROM `my_tab` WHERE
MATCH (`TextToCheckIn`) AGAINST ('green red blue' IN BOOLEAN MODE)
HAVING `score` >= 2
ORDER BY `score` DESC
在自然语言模式评分工作中完全不同.认为它主要基于 BM25 .
In Natural Language mode scoring works completely different. Think it's primarily based on BM25.
在大型数据集上进行布尔全文搜索(使用全文索引)如果匹配文本中的 somewhere 字词，通常通常比REGEXP或LIKE更好.如果可以利用索引，则只会使用like/regexp从起始位置(如REGEXP '^word'或LIKE 'word%')进行匹配.
On large datasets boolean fulltext search (using a fulltext index) usually outperforms REGEXP or LIKE by far if matching words somewhere in the text. Would only use like/regexp for matching from the initial such as REGEXP '^word' or LIKE 'word%' - if an index can be utilized.
这篇关于的PHP/MySQL的.是否有mysql函数来检测至少一个mysql行字段中是否存在至少2个特定单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
