如何在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个问题:
如何按相似性排序(排序)。从上面的示例中,我希望“ Key”作为我的第一个结果。我当前的结果是:Keyword1,Keyword2,Keyword3,Keysomething和Key
我的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