首页 > 代码库 > mysql全文索引(三)查询扩展

mysql全文索引(三)查询扩展

  1. 查询扩展原理:
    1. 开启查询扩展(query expansion)时候,进行两次查询,第一次,查出用户给定的关键词对应的记录;第二次,用第一次查出的结果里的关键词,再去查一次,把两次的结果返回给用户
  2. 实验
    1. mysql> select id,title,body from articles where match(title,body) against(‘fulltext‘ ) order by id asc;
      +----+-------------------------+---------------------------------+
      | id | title                   | body                            |
      +----+-------------------------+---------------------------------+
      |  8 | MySQL Full-Text Indexes | MySQL fulltext indexes use a .. |
      +----+-------------------------+---------------------------------+
      1 row in set (0.00 sec)
      
      mysql> select id,title,body from articles where match(title,body) against(‘fulltext‘ with query expansion ) order by id asc;
      +----+-------------------------+-------------------------------------+
      | id | title                   | body                                |
      +----+-------------------------+-------------------------------------+
      |  1 | MySQL Tutorial          | This database tutorial ...          |
      |  2 | How To Use MySQL        | After you went through a ...        |
      |  4 | MySQL vs. YourSQL       | When comparing databases ...        |
      |  5 | MySQL Security          | When configured properly, MySQL ... |
      |  7 | 1001 MySQL Tricks       | 1. Never run mysqld as root. 2. ... |
      |  8 | MySQL Full-Text Indexes | MySQL fulltext indexes use a ..     |
      +----+-------------------------+-------------------------------------+
      6 rows in set (0.00 sec)
      

       第一次没有开启查询扩展,只有包含关键词:fulltext的记录8被查到;第二次,包含mysql,fulltext的记录全部被返回

  3.  注意:

    1. 运用这个功能,可能会搜到很多不相关的信息,因此,只有当keyword比较short的时候,才使用
  4. 参考资料:https://dev.mysql.com/doc/refman/5.7/en/fulltext-query-expansion.html

mysql全文索引(三)查询扩展