mysql score match_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)