mysql全文索引字段加权重的实现方法

mysql建立全文索引时，可以实现模糊匹配：

select MATCH (index) AGAINST ('key1 key2 key3') as score

但如果要对多字段搜索，则没有直接对不同字段设置不同权重的，比如有 title 和 content 的字段，如果想让 title 的权重比 content 要高，可以通过两条索引加权后排序来完成，参考以下的代码:

　　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 |

　　原文链接：https://blog.csdn.net/weixin_33515785/article/details/113437319