- A+
所属分类：SQL技巧
MySQL全文检索的实现示例！
全文搜索是一种在文本字段中查找关键词的功能。在MySQL中,我们可以使用全文搜索来提高查询的效率,本文主要介绍了MySQL全文检索的实现示例，具有一定的参考价值，感兴趣的可以了解一下。
不是所有的数据表都支持全文检索 MySQL支持多种底层数据库引擎，但是并非所有的引擎支持全文检索 ，目前最常用引擎是是MyISAM和InnoDB；前者支持全文检索，后者不支持。
booolean模式操作符
| 操作符 | 含义 |
| + | 必须有 |
| - | 必须不包含 |
| > | 包含对应关键字的排名靠前 |
| < | 包含对应关键字的排名靠后 |
| ~ | 取反（） |
| * | 放在词尾 类似通配符 |
| " " | 引号中间作为一个完整的值处理 |
实验：
表productnotes ：
1.查询包含rabbit的行，并按照相关性排序
|
1
2
3
4
5
6
7
8
9
10
|
mysql> SELECT note_text-> FROM productnotes-> WHERE Match(note_text) Against('rabbit');+----------------------------------------------------------------------------------------------------------------------+| note_text |+----------------------------------------------------------------------------------------------------------------------+| Customer complaint: rabbit has been able to detect trap, food apparently less effective now. || Quantity varies, sold by the sack load. All guaranteed to be bright and orange, and suitable for use as rabbit bait. |+----------------------------------------------------------------------------------------------------------------------+2 rows in set (0.01 sec) |
2.显示每一条的相关性值
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
mysql> SELECT note_text,-> Match(note_text) Against('rabbit') AS match_rank-> FROM productnotes;+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+| note_text | match_rank |+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+| Customer complaint:Sticks not individually wrapped, too easy to mistakenly detonate all at once.Recommend individual wrapping. | 0 || Can shipped full, refills not available.Need to order new can if refill needed. | 0 || Safe is combination locked, combination not provided with safe.This is rarely a problem as safes are typically blown up or dropped by customers. | 0 || Quantity varies, sold by the sack load.All guaranteed to be bright and orange, and suitable for use as rabbit bait. | 1.5905543565750122 || Included fuses are short and have been known to detonate too quickly for some customers.Longer fuses are available (item FU1) and should be recommended. | 0 || Matches not included, recommend purchase of matches or detonator (item DTNTR). | 0 || Please note that no returns will be accepted if safe opened using explosives. | 0 || Multiple customer returns, anvils failing to drop fast enough or falling backwards on purchaser. Recommend that customer considers using heavier anvils. | 0 || Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes. | 0 || Customer complaint: rabbit has been able to detect trap, food apparently less effective now. | 1.6408053636550903 || Shipped unassembled, requires common tools (including oversized hammer). | 0 || Customer complaint:Circular hole in safe floor can apparently be easily cut with handsaw. | 0 || Customer complaint:Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. | 0 || Call from individual trapped in safe plummeting to the ground, suggests an escape hatch be added.Comment forwarded to vendor. | 0 |+-----------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------+14 rows in set (0.00 sec) |
3.有heavy 但是没有rope
|
1
2
3
4
5
6
7
8
9
10
11
|
mysql> SELECT note_text-> FROM productnotes-> WHERE Match(note_text)-> Against('heavy -rope*' IN BOOLEAN MODE);+---------------------------------------------------------------------------------------------------------------------------------------------------------+| note_text |+---------------------------------------------------------------------------------------------------------------------------------------------------------+| Customer complaint:Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |+---------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec) |
到此这篇关于MySQL 全文检索的文章就介绍到这了。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
你将获得：
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需99元，即可下载本站文章涉及的文件和软件。
- 我的微信
-
- 我的知识星球
-
