如何在MySQL中按相似度进行匹配和排序?

目前,我正在执行搜索功能。可以说在我的数据库中,我有以下数据:

  • Keyword1
  • Keyword2
  • Keyword3
  • Keysomething
  • Key

然后用户输入:“ Key”作为要搜索的关键字。这是我当前的查询:

SELECT * FROM data WHERE (

data_string LIKE '$key%' OR

data_string LIKE '%$key%' OR

data_string LIKE '%$key'

)

基本上,我有2个问题:

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

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

    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’

    ) – …

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

回答:

我不确定LIKE这样做是否正确。如果您需要在文本中搜索关键字并按相关性得分对结果进行排序,则应使用MySQL全文索引和MySQL全文搜索功能。抱歉,如果这使您偏离了实际尝试执行的操作,但是我建议您先看看它。MySQL参考手册中的一些引号:

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

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)示例查询,该查询在多列中搜索关键字并显示结果+得分:

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中按相似度进行匹配和排序? 的全部内容, 来源链接: utcz.com/qa/428876.html

回到顶部