mysql 全文索引 ranking

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了mysql 全文索引 ranking，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4173字，纯文字阅读大概需要6分钟。

内容图文

mysql> CREATE TABLE articles (
    -> id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    -> title VARCHAR(200),
    -> body TEXT,
    -> FULLTEXT (title,body)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (2.48 sec)

mysql> select * from articles;
Empty set (0.00 sec)

mysql> INSERT INTO articles (title,body) VALUES
    -> (‘MySQL Tutorial‘,‘This database tutorial ...‘),
    -> ("How To Use MySQL",‘After you went through a ...‘),
    -> (‘Optimizing Your Database‘,‘In this database tutorial ...‘),
    -> (‘MySQL vs. YourSQL‘,‘When comparing databases ...‘),
    -> (‘MySQL Security‘,‘When configured properly, MySQL ...‘),
    -> (‘Database, Database, Database‘,‘database database database‘),
    -> (‘1001 MySQL Tricks‘,‘1. Never run mysqld as root. 2. ...‘),
    -> (‘MySQL Full-Text Indexes‘, ‘MySQL fulltext indexes use a ..‘);
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select * from articles;
+----+------------------------------+-------------------------------------+
| id | title                        | body                                |
+----+------------------------------+-------------------------------------+
|  1 | MySQL Tutorial               | This database tutorial ...          |
|  2 | How To Use MySQL             | After you went through a ...        |
|  3 | Optimizing Your Database     | In this database tutorial ...       |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        |
|  5 | MySQL Security               | When configured properly, MySQL ... |
|  6 | Database, Database, Database | database database database          |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     |
+----+------------------------------+-------------------------------------+
8 rows in set (0.00 sec)

mysql> select id,title,body,match(title,body) against("databas fulltext") from articles where match(title,body) against("databas fulltext");
+----+-------------------------+---------------------------------+-----------------------------------------------+
| id | title                   | body                            | match(title,body) against("databas fulltext") |
+----+-------------------------+---------------------------------+-----------------------------------------------+
|  8 | MySQL Full-Text Indexes | MySQL fulltext indexes use a .. |                            0.8155715465545654 |
+----+-------------------------+---------------------------------+-----------------------------------------------+
1 row in set (0.04 sec)

实验：

mysql> select id,title,body,match(title,body) against("database" in boolean mode) as score from articles where match(title,body) against("database fulltext") order by score desc;
+----+------------------------------+---------------------------------+---------------------+
| id | title                        | body                            | score               |
+----+------------------------------+---------------------------------+---------------------+
|  6 | Database, Database, Database | database database database      |  1.0886961221694946 |
|  3 | Optimizing Your Database     | In this database tutorial ...   | 0.36289870738983154 |
|  1 | MySQL Tutorial               | This database tutorial ...      | 0.18144935369491577 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a .. |                   0 |
+----+------------------------------+---------------------------------+---------------------+
4 rows in set (0.00 sec)

score的计算方法：

以id=6的行做例：

总记录数为：8

所有匹配到database的行数为：3

该行一共有database个数为6

IDF = log10（8/3）

TF = 6

score= TF*IDF*IDF

mysql> select (log10(8/3)*6*log10(8/3));
+---------------------------+
| (log10(8/3)*6*log10(8/3)) |
+---------------------------+
|         1.088696164686938 |
+---------------------------+
1 row in set (0.00 sec)

mysql 全文索引 ranking

标签：个数   cte   ted   order   int   root   prim   tricks   where   

本文系统来源：http://www.cnblogs.com/maggie94/p/6781038.html

内容总结

以上是互联网集市为您收集整理的mysql 全文索引 ranking全部内容，希望文章能够帮你解决mysql 全文索引 ranking所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端