{"id":2022,"date":"2020-07-29T23:10:48","date_gmt":"2020-07-29T15:10:48","guid":{"rendered":"http:\/\/39.97.231.10\/?p=2022"},"modified":"2020-07-29T23:10:48","modified_gmt":"2020-07-29T15:10:48","slug":"mysql%e5%85%a8%e6%96%87%e7%b4%a2%e5%bc%95-fulltext","status":"publish","type":"post","link":"http:\/\/www.weijing.co\/?p=2022","title":{"rendered":"MySQL\u5168\u6587\u7d22\u5f15-fulltext"},"content":{"rendered":"
\n
\nMySQL 5.6 \u4e4b\u524d\u7684\u7248\u672c\u53ea\u6709MyISAM\u5b58\u50a8\u5f15\u64ce\u652f\u6301\u5168\u6587\u7d22\u5f15\uff0c5.6 \u53ca\u4e4b\u540e\u7684\u7248\u672c\uff0cInnoDB \u624d\u5f00\u59cb\u652f\u6301\u3002\u5168\u6587\u7d22\u5f15\u76f8\u5bf9\u4e8elike %\u8fd9\u79cd\u6a21\u7cca\u67e5\u8be2\u6548\u7387\u66f4\u9ad8\uff0c\u67e5\u8be2\u65b9\u5f0f\u66f4\u591a\u6837\uff0c\u67e5\u8be2\u7ed3\u679c\u4e5f\u66f4\u51c6\u786e\u3002\u5728\u5168\u6587\u68c0\u7d22\u9886\u57df\uff0c\u5168\u6587\u7d22\u5f15\u529f\u80fd\u975e\u5e38\u6709\u7528\uff0c\u6bd4\u5982\u535a\u5ba2\u7c7b\u7f51\u7ad9\uff0c\u53ef\u4ee5\u5bf9\u6587\u7ae0\u6807\u9898\uff0c\u6587\u7ae0\u5185\u5bb9\u521b\u5efa\u5168\u6587\u7d22\u5f15\uff0c\u7528\u6237\u53ef\u4ee5\u6839\u636e\u5173\u952e\u8bcd\u641c\u7d22\u60f3\u8981\u7684\u6587\u7ae0\u3002\n<\/div>\n<\/div>\n
\n
\n
<\/div>\n
1\u3001\u521b\u5efa\u5168\u6587\u7d22\u5f15<\/div>\n
<\/div>\n
\u80fd\u591f\u521b\u5efa\u5168\u6587\u7d22\u5f15\u7684\u5b57\u6bb5\u7c7b\u578b\u5fc5\u987b\u662fchar\uff0cvarchar\uff0ctext\u7b49\u8fd9\u7c7b\u5b57\u7b26\u6587\u672c\u7c7b\u578b\u3002<\/div>\n
<\/div>\n
\u521b\u5efa\u4e00\u4e2a\u5e26\u6709\u5168\u6587\u7d22\u5f15\u7684\u8868\uff1a<\/div>\n
\n
CREATE TABLE articles (<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0id INT UNSIGNED AUTO_INCREMENT NOT NULL\u00a0\u00a0PRIMARY KEY,<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0title VARCHAR(200),<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0body TEXT,<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0FULLTEXT (title,body)<\/div>\n
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0) ENGINE=InnoDB;<\/div>\n<\/div>\n
<\/div>\n
\u901a\u8fc7ALTER TABLE\u521b\u5efa\u3001\u5220\u9664\u5168\u6587\u7d22\u5f15\uff1a<\/div>\n
\n
#\u00a0\u521b\u5efa\u5168\u6587\u7d22\u5f15<\/div>\n
ALTER TABLE articles ADD FULLTEXT KEY ft_title(title);<\/div>\n
<\/div>\n
#\u00a0\u5220\u9664\u7d22\u5f15<\/div>\n
ALTER TABLE articles DROP INDEX ft_title;<\/div>\n<\/div>\n
<\/div>\n
2\u3001\u67e5\u8be2\u5168\u6587\u7d22\u5f15<\/div>\n
<\/div>\n
MySQL\u4f7f\u7528MATCH AGAINST\u8bed\u6cd5\u6765\u67e5\u8be2\u5168\u6587\u7d22\u5f15\uff0c\u652f\u6301\u81ea\u7136\u8bed\u8a00\u548c\u5e03\u5c14\u7c7b\u578b\u4e24\u79cd\u67e5\u8be2\u6a21\u5f0f\u3002<\/div>\n
<\/div>\n
\u81ea\u7136\u8bed\u8a00\u6a21\u5f0f\u67e5\u8be2\uff1a<\/div>\n
<\/div>\n
\u793a\u4f8b\uff1a<\/div>\n
\n
mysql> SELECT * FROM articles WHERE MATCH (title,body)\u00a0\u00a0AGAINST (\\’database\\’\u00a0IN NATURAL LANGUAGE MODE);<\/div>\n
+—-+——————-+——————————————+<\/div>\n
| id | title\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| body\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
+—-+——————-+——————————————+<\/div>\n
|\u00a0\u00a01 | MySQL Tutorial\u00a0\u00a0\u00a0\u00a0| DBMS stands for DataBase …\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0|<\/div>\n
|\u00a0\u00a05 | MySQL vs. YourSQL | In the following database\u00a0\u00a0comparison … |<\/div>\n
+—-+——————-+——————————————+<\/div>\n
2 rows in set (0.01 sec)<\/div>\n<\/div>\n
<\/div>\n
<\/div>\n
\u5e03\u5c14\u7c7b\u578b\u6a21\u5f0f\u67e5\u8be2\uff1a<\/div>\n
<\/div>\n