如何在MySQL中按相似度进行匹配和排序? [英] How to match and sort by similarity in MySQL?

问题描述

当前，我正在执行搜索功能.可以说在我的数据库中，我有以下数据:

Currently, I am doing a search function. Lets say in my database, I have this data:

• 关键字1
• 关键字2
• 关键字3
• 关键事项
• 键

，用户输入:"Key"作为要搜索的关键字.这是我当前的查询:

and the user entered: "Key" as the keyword to search. This is my current query:

SELECT * FROM data WHERE (
  data_string LIKE '$key%' OR 
  data_string LIKE '%$key%' OR
  data_string LIKE '%$key'
)

基本上，我有两个问题:

Basically, I have 2 questions:

1. 如何按相似性排序(排序).从上面的示例中，我希望"Key"作为我的第一个结果.我当前的结果是:Keyword1，Keyword2，Keyword3，Keysomething和Key

1. How do I sort by (order by) similarity. From above example, I wanted "Key" as my first result. My current result is: Keyword1, Keyword2, Keyword3, Keysomething and Key

我的SQL查询仅按"data_string"列进行搜索，如果我想查找其他列怎么办?我需要做这样的事情吗?

My SQL query only search by the "data_string" column, what if I want to seach others column? Do I need to do something like this:

SELECT * FROM data WHERE (
  data_string LIKE '$key%' OR
  data_string LIKE '%$key%' OR
  data_string LIKE '%$key'
) OR (
  data_other LIKE '$key%' OR
  data_other LIKE '%$key%' OR
  data_other LIKE '%$key'
) -- ...

是否有比第二季度更好/更快的查询?

Is there any better/faster query than Q2?

推荐答案

我不确定LIKE是否是正确的方法.如果需要在文本中搜索关键字并按相关性得分对结果进行排序，则应使用 MySQL全文索引和

I am not sure if LIKE is the right way to do this. If you need to search inside your text for keywords and sort results by relevancy score, you should use MySQL Full-Text index and MySQL Full-text Search functions. Sorry if this leads you away from what you are actually trying to do but I do recommend having one look at it. Some quotes from MySQL reference manual:

1)如何在表的多列上创建全文索引

1) How to create full text index on multiple columns of a table

mysql> CREATE TABLE articles (
    ->   id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->   title VARCHAR(200),
    ->   body TEXT,
    ->   FULLTEXT (title,body)
    -> );

2)样本数据

mysql> INSERT INTO articles (title,body) VALUES
    -> ('MySQL Tutorial','DBMS stands for DataBase ...'),
    -> ('How To Use MySQL Well','After you went through a ...'),
    -> ('Optimizing MySQL','In this tutorial we will show ...'),
    -> ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    -> ('MySQL vs. YourSQL','In the following database comparison ...'),
    -> ('MySQL Security','When configured properly, MySQL ...');

3)示例查询，该查询在多个列中搜索关键字并显示结果+得分:

3) Sample query that searches multiple columns for keywords and displays result + the score:

mysql> SELECT id, body, MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root') AS score
    -> FROM articles WHERE MATCH (title,body) AGAINST
    -> ('Security implications of running MySQL as root');
+----+-------------------------------------+-----------------+
| id | body                                | score           |
+----+-------------------------------------+-----------------+
|  4 | 1. Never run mysqld as root. 2. ... | 1.5219271183014 |
|  6 | When configured properly, MySQL ... | 1.3114095926285 |
+----+-------------------------------------+-----------------+

这篇关于如何在MySQL中按相似度进行匹配和排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！