mysql全文索引权重

Question

mysql建立全文索引时，能对不同字段设置不同权重吗？比如有title和content的字段，想让title的权重比content要高

Answer 1

没有直接权重设定, 但你可以通过两条索引加权来完成, 参考以下的代码:

drop table if exists articles ;

CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT (title),
FULLTEXT (content)

) ENGINE=InnoDB;

INSERT INTO articles (title, content) 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 ..');   




-- 让 content 有三倍权值
SELECT id, title, content, MATCH (content)  AGAINST ('database' IN BOOLEAN MODE)
AS score1, MATCH (title)  AGAINST ('database' IN BOOLEAN MODE)
AS score2, (MATCH (content)  AGAINST ('database' IN BOOLEAN MODE))*3+(MATCH (title)  AGAINST ('database' IN BOOLEAN MODE)) as score3 FROM articles ORDER BY score1*3+score2 DESC;

得到结果如下:

| id | title                        | content                                | score1              | score2             | score3             |
|  6 | Database, Database, Database | database database database          |  0.5443480610847473 | 1.0874286890029907 | 2.7204728722572327 |
|  3 | Optimizing Your Database     | In this database tutorial ...       | 0.18144935369491577 | 0.3624762296676636 | 0.9068242907524109 |
|  1 | MySQL Tutorial               | This database tutorial ...          | 0.18144935369491577 |                  0 | 0.5443480610847473 |
|  2 | How To Use MySQL             | After you went through a ...        |                   0 |                  0 |                  0 |
|  4 | MySQL vs. YourSQL            | When comparing databases ...        |                   0 |                  0 |                  0 |
|  5 | MySQL Security               | When configured properly, MySQL ... |                   0 |                  0 |                  0 |
|  7 | 1001 MySQL Tricks            | 1. Never run mysqld as root. 2. ... |                   0 |                  0 |                  0 |
|  8 | MySQL Full-Text Indexes      | MySQL fulltext indexes use a ..     |                   0 |                  0 |                  0 |

Answer 2

可参考 http://m.lao8.org/a1602 文章，通过SQL来解决权重问题