一个词必须匹配,一个词必须不匹配;以前的 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)
+ < 降低优先等级
+ * 词尾通配符
+ “” 定义短句
累加优先级 >> <<