ä¸ãmsyqlå ¨ææ£ç´¢
1. å®ä¹
æ¯å°åå¨äºæ°æ®åºä¸çæ´æ¬ä¹¦ææ´ç¯æç« ä¸çä»»æå 容信æ¯æ¥æ¾åºæ¥çææ¯ã
2. ç¸å ³è®¾ç½®
//å建æµè¯è¡¨
CREATE TABLE fts_a(
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
body TEXT,
PRIMARY KEY(FTS_DOC_ID)
);
//æå
¥æµè¯æ°æ®
INSERT INTO fts_a SELECT NULL,'Pease porridge in the pot';
INSERT INTO fts_a SELECT NULL,'Pease porridge hot,pease porridge cold';
INSERT INTO fts_a SELECT NULL,'Nine days old';
INSERT INTO fts_a SELECT NULL,'Some like it hot,some like it cold';
INSERT INTO fts_a SELECT NULL,'Some like it in the pot';
INSERT INTO fts_a SELECT NULL,'Nine days old';
INSERT INTO fts_a SELECT NULL,'I like code days';
//å建åæç´¢å¼
CREATE FULLTEXT INDEX idx_fts ON fts_a(body);
//æ¥çåè¯å¯¹åºçä¿¡æ¯ï¼
mysql> show variables like 'innodb_ft_aux_table';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_ft_aux_table | |
+---------------------+-------+
mysql> SET GLOBAL innodb_ft_aux_table='learn/fts_a'; //learn为æ°æ®åºå
mysql> show variables like 'innodb_ft_aux_table';
+---------------------+-------------+
| Variable_name | Value |
+---------------------+-------------+
| innodb_ft_aux_table | learn/fts_a |
+---------------------+-------------+
//å¯ä»¥çå°æ¯ä¸ªwordé½å¯¹åºäºä¸ä¸ªDOC_IDåPOSITIONãæ¤å¤ï¼è¿è®°å½äºFIRST_DOC_IDãLAST_DOC_ID以åDOC_COUNTï¼åå«ä»£è¡¨äºè¯¥word第ä¸æ¬¡åºç°çææ¡£IDï¼æåä¸æ¬¡åºç°çææ¡£IDï¼ä»¥å该wordå¨å¤å°ä¸ªææ¡£ä¸åå¨ã
mysql> SELECT*FROM information_schema.INNODB_FT_INDEX_TABLE;
+----------+--------------+-------------+-----------+--------+----------+
| WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION |
+----------+--------------+-------------+-----------+--------+----------+
| code | 7 | 7 | 1 | 7 | 7 |
| cold | 2 | 4 | 2 | 2 | 34 |
| cold | 2 | 4 | 2 | 4 | 30 |
| days | 3 | 7 | 3 | 3 | 5 |
| days | 3 | 7 | 3 | 6 | 5 |
| days | 3 | 7 | 3 | 7 | 12 |
| hot | 2 | 4 | 2 | 2 | 15 |
| hot | 2 | 4 | 2 | 4 | 13 |
| like | 4 | 7 | 3 | 4 | 5 |
| like | 4 | 7 | 3 | 4 | 17 |
| like | 4 | 7 | 3 | 5 | 5 |
| like | 4 | 7 | 3 | 7 | 2 |
| nine | 3 | 6 | 2 | 3 | 0 |
| nine | 3 | 6 | 2 | 6 | 0 |
| old | 3 | 6 | 2 | 3 | 10 |
| old | 3 | 6 | 2 | 6 | 10 |
| pease | 1 | 2 | 2 | 1 | 0 |
| pease | 1 | 2 | 2 | 2 | 0 |
| pease | 1 | 2 | 2 | 2 | 19 |
| porridge | 1 | 2 | 2 | 1 | 6 |
| porridge | 1 | 2 | 2 | 2 | 6 |
| porridge | 1 | 2 | 2 | 2 | 19 |
| pot | 1 | 5 | 2 | 1 | 22 |
| pot | 1 | 5 | 2 | 5 | 20 |
| some | 4 | 5 | 2 | 4 | 0 |
| some | 4 | 5 | 2 | 4 | 17 |
| some | 4 | 5 | 2 | 5 | 0 |
+----------+--------------+-------------+-----------+--------+----------+
//å
¨ææ£ç´¢:stopwordå表:表示该å表ä¸çwordä¸éè¦å¯¹å
¶è¿è¡ç´¢å¼åè¯æä½
//å建ç¨æ·è¡¨
CREATE TABLE `user_stopword` (
`value` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
//设置路å¾
mysql> show variables like 'innodb_ft_server_stopword_table';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| innodb_ft_server_stopword_table | |
+---------------------------------+-------+
mysql> SET GLOBAL innodb_ft_server_stopword_table="learn/user_stopword";
mysql> show variables like 'innodb_ft_server_stopword_table';
+---------------------------------+---------------------+
| Variable_name | Value |
+---------------------------------+---------------------+
| innodb_ft_server_stopword_table | learn/user_stopword |
+---------------------------------+---------------------+
3. å ¨ææ£ç´¢æºå¶
对äºInnoDBåå¨å¼æçå ¨ææ£ç´¢ï¼è¿éè¦èè以ä¸çå ç´ ï¼
- æ¥è¯¢çwordå¨stopwordåä¸ï¼å¿½ç¥è¯¥å符串çæ¥è¯¢ã
-
æ¥è¯¢çwordçå符é¿åº¦æ¯å¦å¨åºé´[innodb_ft_min_token_sizeï¼innodb_ft_max_token_size]å ã
åæ°innodb_ft_min_token_sizeåinnodb_ft_max_token_sizeæ§å¶InnoDBåå¨å¼ææ¥è¯¢å符çé¿åº¦ï¼å½é¿åº¦å°äºinnodb_ft_min_token_sizeï¼æè é¿åº¦å¤§äºinnodb_ft_max_token_sizeæ¶ï¼ä¼å¿½ç¥è¯¥è¯çæç´¢ã
mysql> show variables like 'innodb_ft_min_token_size';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_ft_min_token_size | 3 |
+--------------------------+-------+
mysql> show variables like 'innodb_ft_max_token_size';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_ft_max_token_size | 84 |
+--------------------------+-------+
4. 使ç¨éå¶
å½åInnoDBåå¨å¼æçå ¨ææ£ç´¢è¿åå¨ä»¥ä¸çéå¶ï¼
- æ¯å¼ 表åªè½æä¸ä¸ªå ¨ææ£ç´¢çç´¢å¼ã
- ç±å¤åç»åèæçå ¨ææ£ç´¢çç´¢å¼åå¿ é¡»ä½¿ç¨ç¸åçå符éä¸æåºè§åã
- ä¸æ¯æ没æåè¯çå®ç¬¦ï¼delimiterï¼çè¯è¨ï¼å¦ä¸æãæ¥è¯ãé©è¯çã
ä¸ãmsyqlå ¨ææ£ç´¢ä½¿ç¨
1. Natural Language
//(1) ä¸ä½¿ç¨å
¨ææç´ ææ¯
mysql> SELECT*FROM fts_a WHERE body LIKE'%Pease%';
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 1 | Pease porridge in the pot |
| 2 | Pease porridge hot,pease porridge cold |
+------------+----------------------------------------+
mysql> explain SELECT*FROM fts_a WHERE body LIKE'%Pease%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | fts_a | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
//(2) 使ç¨å
¨ææ£ç´¢ææ¯
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST('Porridge' IN NATURAL LANGUAGE MODE);
//mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST('Porridge');
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 2 | Pease porridge hot,pease porridge cold |
| 1 | Pease porridge in the pot |
+------------+----------------------------------------+
mysql> explain SELECT * FROM fts_a WHERE MATCH(body) AGAINST('Porridge' IN NATURAL LANGUAGE MODE);
+----+-------------+-------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
| 1 | SIMPLE | fts_a | NULL | fulltext | idx_fts | idx_fts | 0 | const | 1 | 100.00 | Using where; Ft_hints: sorted |
+----+-------------+-------+------------+----------+---------------+---------+---------+-------+------+----------+-------------------------------+
//ç»è®¡æ¬¡æ°
//æ
¢:éè¦è¿è¡ç¸å
³æ§çæåºç»è®¡
mysql> SELECT count(*) FROM fts_a WHERE MATCH(body) AGAINST('Porridge');
+----------+
| count(*) |
+----------+
| 2 |
+----------+
//æ´å¿«:ä¸éè¦è¿è¡ç¸å
³æ§çæåºç»è®¡
mysql> SELECT COUNT(IF(MATCH(body) AGAINST('Porridge'),1,NULL)) AS count FROM fts_a;
+-------+
| count |
+-------+
| 2 |
+-------+
//æ¥çç¸å
³æ§
mysql> SELECT fts_doc_id,body, MATCH(body)AGAINST('Porridge') AS Relevance FROM fts_a;
+------------+----------------------------------------+--------------------+
| fts_doc_id | body | Relevance |
+------------+----------------------------------------+--------------------+
| 1 | Pease porridge in the pot | 0.2960100471973419 |
| 2 | Pease porridge hot,pease porridge cold | 0.5920200943946838 |
| 3 | Nine days old | 0 |
| 4 | Some like it hot,some like it cold | 0 |
| 5 | Some like it in the pot | 0 |
| 6 | Nine days old | 0 |
| 7 | I like code days | 0 |
+------------+----------------------------------------+--------------------+
//the å¨stopwordä¸ æ
ç¸å
³æ§ä¸º0
mysql> insert into user_stopword values('the');
mysql> select * from user_stopword;
+-------+
| value |
+-------+
| the |
+-------+
mysql> SELECT fts_doc_id AS id,body, MATCH(body)AGAINST('the') AS rl FROM fts_a;
+----+----------------------------------------+------+
| id | body | rl |
+----+----------------------------------------+------+
| 1 | Pease porridge in the pot | 0 |
| 2 | Pease porridge hot,pease porridge cold | 0 |
| 3 | Nine days old | 0 |
| 4 | Some like it hot,some like it cold | 0 |
| 5 | Some like it in the pot | 0 |
| 6 | Nine days old | 0 |
| 7 | I like code days | 0 |
+----+----------------------------------------+------+
2. Boolean
(1) æ¯æçæä½
Booleanå ¨ææ£ç´¢æ¯æ以ä¸å ç§æä½ç¬¦ï¼
- +表示该wordå¿ é¡»åå¨ã
- -表示该wordå¿ é¡»è¢«æé¤ã
- (no operator)表示该wordæ¯å¯éçï¼ä½æ¯å¦æåºç°ï¼å ¶ç¸å ³æ§ä¼æ´é«
- @distance表示æ¥è¯¢çå¤ä¸ªåè¯ä¹é´çè·ç¦»æ¯å¦å¨distanceä¹å ï¼distanceçåä½æ¯åèãè¿ç§å ¨ææ£ç´¢çæ¥è¯¢ä¹ç§°ä¸ºProximity Searchãå¦MATCHï¼bodyï¼AGAINSTï¼ââPease potâ@30âIN BOOLEAN MODEï¼è¡¨ç¤ºå符串Peaseåpotä¹é´çè·ç¦»éå¨30åèå ã
- ï¼è¡¨ç¤ºåºç°è¯¥åè¯æ¶å¢å ç¸å ³æ§ã
- <表示åºç°è¯¥åè¯æ¶éä½ç¸å ³æ§ã
- ~表示å 许åºç°è¯¥åè¯ï¼ä½æ¯åºç°æ¶ç¸å ³æ§ä¸ºè´ï¼å ¨ææ£ç´¢æ¥è¯¢å 许è´ç¸å ³æ§ï¼ã
- * 表示以该åè¯å¼å¤´çåè¯ï¼å¦lik*ï¼è¡¨ç¤ºå¯ä»¥æ¯likãlikeï¼åæè likesã
- "表示çè¯ã
(2) 举ä¾
//è¦æ±æ¥è¯¢æå符串Peaseä½æ²¡æhotçææ¡£
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST('+Pease-hot' IN BOOLEAN MODE);
+------------+---------------------------+
| FTS_DOC_ID | body |
+------------+---------------------------+
| 1 | Pease porridge in the pot |
+------------+---------------------------+
//è¦æ±æ¥è¯¢æå符串Peaseä½æhotçææ¡£
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST('+Pease+hot' IN BOOLEAN MODE);
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 2 | Pease porridge hot,pease porridge cold |
+------------+----------------------------------------+
//è¦æ±æ¥è¯¢æå符串Peaseä½æhotçææ¡£
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST('Pease hot' IN BOOLEAN MODE);
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 2 | Pease porridge hot,pease porridge cold |
| 1 | Pease porridge in the pot |
| 4 | Some like it hot,some like it cold |
+------------+----------------------------------------+
//é»è¿æç´¢:æ¥è¯¢Peaseåhotç¸å·®å¤å°åè
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST('"Pease hot"@30'IN BOOLEAN MODE);
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 2 | Pease porridge hot,pease porridge cold |
+------------+----------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT * FROM fts_a WHERE MATCH(body) AGAINST('"Pease hot"@1'IN BOOLEAN MODE);
Empty set (0.00 sec)
//æ ¹æ®æ¯å¦æåè¯likeæpotè¿è¡ç¸å
³æ§ç»è®¡ï¼å¹¶ä¸åºç°åè¯potåç¸å
³æ§éè¦å¢å ,åºç°someåè¯åç¸å
³æ§éè¦åå°
mysql> SELECT * From fts_a WHERE MATCH(body) AGAINST('like>hot' IN BOOLEAN MODE);
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 4 | Some like it hot,some like it cold |
| 2 | Pease porridge hot,pease porridge cold |
| 5 | Some like it in the pot |
| 7 | I like code days |
+------------+----------------------------------------+
mysql> SELECT fts_doc_id,body,MATCH(body)AGAINST('like>pot' IN BOOLEAN MODE) AS Relevance FROM fts_a;
+------------+----------------------------------------+---------------------+
| fts_doc_id | body | Relevance |
+------------+----------------------------------------+---------------------+
| 1 | Pease porridge in the pot | 1.2960100173950195 |
| 2 | Pease porridge hot,pease porridge cold | 0 |
| 3 | Nine days old | 0 |
| 4 | Some like it hot,some like it cold | 0.27081382274627686 |
| 5 | Some like it in the pot | 1.4314169883728027 |
| 6 | Nine days old | 0 |
| 7 | I like code days | 0.13540691137313843 |
+------------+----------------------------------------+---------------------+
mysql> SELECT * From fts_a WHERE MATCH(body) AGAINST('like>hot<some'IN BOOLEAN MODE);
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 2 | Pease porridge hot,pease porridge cold |
| 4 | Some like it hot,some like it cold |
| 7 | I like code days |
| 5 | Some like it in the pot |
+------------+----------------------------------------+
4 rows in set (0.00 sec)
mysql> SELECT fts_doc_id,body,MATCH(body)AGAINST('like>pot<some' IN BOOLEAN MODE) AS Relevance FROM fts_a;
+------------+----------------------------------------+---------------------+
| fts_doc_id | body | Relevance |
+------------+----------------------------------------+---------------------+
| 1 | Pease porridge in the pot | 1.2960100173950195 |
| 2 | Pease porridge hot,pease porridge cold | 0 |
| 3 | Nine days old | 0 |
| 4 | Some like it hot,some like it cold | -0.1371660828590393 |
| 5 | Some like it in the pot | 0.7274270057678223 |
| 6 | Nine days old | 0 |
| 7 | I like code days | 0.13540691137313843 |
+------------+----------------------------------------+---------------------+
//ææ¡£å
å«ä»¥poå¼å¤´çåè¯
mysql> SELECT * From fts_a WHERE MATCH(body) AGAINST('po*'IN BOOLEAN MODE);
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 2 | Pease porridge hot,pease porridge cold |
| 1 | Pease porridge in the pot |
| 5 | Some like it in the pot |
+------------+----------------------------------------+
//æ¥çæ¯å¦å
å«like hot çè¯(èä¸æ¯2个åè¯)
mysql> SELECT * From fts_a WHERE MATCH(body) AGAINST('"like hot"'IN BOOLEAN MODE);
Empty set (0.00 sec)
mysql> SELECT * From fts_a WHERE MATCH(body) AGAINST('like hot'IN BOOLEAN MODE);
+------------+----------------------------------------+
| FTS_DOC_ID | body |
+------------+----------------------------------------+
| 4 | Some like it hot,some like it cold |
| 2 | Pease porridge hot,pease porridge cold |
| 5 | Some like it in the pot |
| 7 | I like code days |
+------------+----------------------------------------+
3. Query Expansion
(1) å®ä¹
å ¨ææ£ç´¢çæ©å±æ¥è¯¢ãè¿ç§æ¥è¯¢é常å¨æ¥è¯¢çå ³é®è¯å¤ªçï¼ç¨æ·éè¦implied knowledgeï¼éå«ç¥è¯ï¼æ¶è¿è¡ã
(2) æ¥è¯¢æºå¶
- 第ä¸é¶æ®µï¼æ ¹æ®æç´¢çåè¯è¿è¡å ¨æç´¢å¼æ¥è¯¢ã
- 第äºé¶æ®µï¼æ ¹æ®ç¬¬ä¸é¶æ®µäº§ççåè¯åè¿è¡ä¸æ¬¡å ¨ææ£ç´¢çæ¥è¯¢ã
(3) 缺ç¹
ç±äºQuery Expansionçå ¨ææ£ç´¢å¯è½å¸¦æ¥è®¸å¤éç¸å ³æ§çæ¥è¯¢ï¼å æ¤å¨ä½¿ç¨æ¶ï¼ç¨æ·å¯è½éè¦é常谨æ ã
(4) 举ä¾
//å建表
CREATE TABLE articles(
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT(title,body)
)ENGINE=InnoDB;
//æå
¥æ°æ®
INSERT INTO articles(title,body)VALUES
('MySQL Tutorial','DBMS stands for DataBase...'),
('How To Use MySQL Well','After you went through a...'),
('Optimizing MySQL','In this tutorial we will show...'),
('1001 MySQL Tricks','1.Never run mysqld as root.2....'),
('MySQL vs.YourSQL','In the following database comparison...'),
('MySQL Security','When configured properly,MySQL...'),
('Tuning DB2','For IBM database...'),
('IBM History','DB2 hitory for IBM...');
//æ®éæ£ç´¢
mysql> SELECT * FROM articles WHERE MATCH(title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
+----+------------------+-----------------------------------------+
| id | title | body |
+----+------------------+-----------------------------------------+
| 1 | MySQL Tutorial | DBMS stands for DataBase... |
| 5 | MySQL vs.YourSQL | In the following database comparison... |
| 7 | Tuning DB2 | For IBM database... |
+----+------------------+-----------------------------------------+
//Query Expansion
mysql> SELECT * FROM articles WHERE MATCH(title,body) AGAINST('database' WITH QUERY EXPANSION);
+----+-----------------------+-----------------------------------------+
| id | title | body |
+----+-----------------------+-----------------------------------------+
| 5 | MySQL vs.YourSQL | In the following database comparison... |
| 1 | MySQL Tutorial | DBMS stands for DataBase... |
| 7 | Tuning DB2 | For IBM database... |
| 8 | IBM History | DB2 hitory for IBM... |
| 3 | Optimizing MySQL | In this tutorial we will show... |
| 6 | MySQL Security | When configured properly,MySQL... |
| 2 | How To Use MySQL Well | After you went through a... |
| 4 | 1001 MySQL Tricks | 1.Never run mysqld as root.2.... |
+----+-----------------------+-----------------------------------------+