mysql:逐个字母匹配查询 [英] mysql: matching a query letter by letter

问题描述

我正在学习 MySQL 和查询，现在开始使用 PHP.出于学习目的，我选择了一个小型字谜求解器类型的项目作为开始.我在互联网上找到了一个非常古老的英语单词列表，可以免费用作数据库.我试过查询，在集合中查找和全文搜索匹配但失败了.

I am in the process of learning MySQL and querying, and right now working with PHP to begin with. For learning purposes I chose a small anagram solver kind of project to begin with. I found a very old English language word list on the internet freely available to use as the DB. I tried querying, find in set and full-text search matching but failed.

我该怎么做:

逐字匹配结果?

例如，假设我有字母 S-L-A-O-G 与数据库条目匹配.

For example, let's say that I have the letters S-L-A-O-G to match against the database entry.

由于我有一个很大的数据库，其中肯定包含很多单词，因此我想要作为查询的回报:

Since I have a large database which surely contains many words, I want to have in return of the query:

lag
goal
goals
slag
log
... and so on.

没有任何其他结果可能会重复使用一个字母.

Without having any other results which might have a letter used twice.

我如何用 SQL 解决这个问题?

How would I solve this with SQL?

非常感谢您抽出宝贵时间.

Thank you very much for your time.

推荐答案

$str_search = 'SLAOG';

SELECT word
FROM table_name
WHERE word REGEXP '^[{$str_search}]+$' # '^[SLAOG]+$'

// Filter the results in php afterwards

// Loop START

$arr = array();
for($i = 0; $i < strlen($row->word); $i++) {

    $h = substr($str_search, $i, 0);
    preg_match_all("/{$h}/", $row->word, $arr_matches);
    preg_match_all("/{$h}/", $str_search, $arr_matches2);

    if (count($arr_matches[0]) > count($arr_matches2[0]))
        FALSE; // Amount doesn't add up

}

// Loop END

基本上对给定的词运行 REGEXP 并根据该词与搜索词相比的出现次数过滤结果.

Basicly run a REGEXP on given words and filter result based on how many occurencies the word compared with the search word.

REGEXP 从头到尾使用给定单词的组合检查所有列.这可能会导致比您需要的更多行，但它仍然会提供一个很好的过滤器.

The REGEXP checks all columns, from beginning to end, with a combination of given words. This may result in more rows then you need, but it will give a nice filter nonetheless.

循环部分是过滤在搜索字符串中使用次数更多的单词.我对找到的单词和搜索单词中的每个字母运行 preg_match_all() 以检查出现次数，并将它们与 count() 进行比较.

The loop part is to filter words where a letter is used more times then in the search string. I run a preg_match_all() on each letter in found the word and the search word to check the amount of occurencies, and compare them with count().

这篇关于mysql:逐个字母匹配查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！