MySQL第五章 22、全文检索

一个词必须匹配，一个词必须不匹配；以前的 where like 正则 不能完成
需要使用全文索引 匹配、不匹配、匹配频率
1、启用全文索引
innodb 不支持全文索引
myisam 支持全文索引
2、语法的变化 innerdb 使用 like regex
myisam 使用 match against
3、开启表级别的全文索引
数据类型必须是text类型 fulltext= （xxxx）

mysql> select note_text from productnotes where note_text like '%rabbit%';
+---------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                        |
+---------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.08 sec)

full text模式

mysql> select note_text from productnotes where match(note_text) against ('rabbit');

可以体现词的优先级匹配情况

mysql> select note_text, match(note_text) against ('rabbit') from productnotes;
+----------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+
| note_text                                                                                                                                                | match(note_text) against ('rabbit') |
+----------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+
| 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.09 sec)

布尔查询模式（full text精华）

mysql> select note_text from productnotes where match(note_text) against ('heavy' in boolean mode);
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Item is extremely heavy. Designed for dropping, not recommended for use with slings, ropes, pulleys, or tightropes.                                    |
| Customer complaint:
Not heavy enough to generate flying stars around head of victim. If being purchased for dropping, recommend ANV02 or ANV03 instead. |
+--------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.10 sec)

加上-rote*

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.09 sec)

布尔型操作

+ 必须包含

mysql> select note_text from productnotes where match(note_text) against ('+rabbit +bait' in boolean mode);
+---------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                           |
+---------------------------------------------------------------------------------------------------------------------+
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

- 必须不包含

+ > 增加优先等级

mysql> select note_text from productnotes where match(note_text) against ('+safe +(combination)' in boolean mode);
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers. |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

mysql> select note_text from productnotes where match(note_text) against ('+safe +(combination accepted)' in boolean mode);
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| note_text                                                                                                                                        |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Safe is combination locked, combination not provided with safe.
This is rarely a problem as safes are typically blown up or dropped by customers. |
| Please note that no returns will be accepted if safe opened using explosives.                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.06 sec)

mysql> select note_text, match(note_text) against ('+safe +(<combination >accepted)' in boolean mode) from productnotes where match(note_text) against ('+safe +(<combination >accepted)' in boolean mode);
+--------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+
| note_text                                                                                                                                        | match(note_text) against ('+safe +(<combination >accepted)' in boolean mode) |
+--------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+
| 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.8333333730697632 |
| Please note that no returns will be accepted if safe opened using explosives.                                                                    |                                                                         1.25 |
+--------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+
2 rows in set (0.09 sec)

+ < 降低优先等级

+ * 词尾通配符

+ “” 定义短句

累加优先级 >> <<