首页 > 代码库 > mysql 全文索引 ranking
mysql 全文索引 ranking
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
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。