MySQL comes with full-text search

Some simple searches can be implemented by MySQL's own full-text index and MATCH against query statement. The experimental steps are as follows.
1. Build a table
DROP table IF exists con_video_file_des_test;
CREATE TABLE ' con_video_file_des_test ' (
' content_id ' bigint (a) not NULL auto_increment COMMENT ' video id ',
' video_title ' varchar CHARACTER SET UTF8 COLLATE utf8_general_ci null DEFAULT null COMMENT ' video title ',
' video_title_phonetic ' text CHARACTER SET UTF8 COLLATE utf8_general_ci NULL COMMENT ' video title Pinyin ',
' video_title_en ' varchar (+) CHARACTER SET UTF8 COLLATE utf8_general_ci null DEFAULT null COMMENT ' video title English ',
PRIMARY KEY (' content_id ')
)
Engine=innodb
DEFAULT CHARACTER Set=utf8 collate=utf8_general_ci
comment= ' Con_ Video class file description information '
auto_increment=7
row_format=compact;
2. Inserting test data
INSERT into ' test '. ' Con_video_file_des_test ' (' content_id ', ' video_title ', ' video_title_phonetic ', ' Video_title_ En ') VALUES (' 1 ', ' founding cause ', ' Jian Guo da ye ', ' blockbuster founding of the Republic ');
INSERT into ' test '. ' Con_video_file_des_test ' (' content_id ', ' video_title ', ' video_title_phonetic ', ' Video_title_ En ') VALUES (' 2 ', ' Travels of the Nut ', ' Jian Guo you ji ', ' Jian Guo Travel ');
3, the index is as follows
ALTER TABLE Con_video_file_des_test add fulltext Search_index (video_title,video_title_phonetic,video_title_en);
4. Query Test
A:select * from Con_video_file_des_test where match (video_title,video_title_phonetic,video_title_en) against (' Jianguo ');
B:select * from Con_video_file_des_test where match (video_title,video_title_phonetic,video_title_en) against (' Founding of the Great Cause ');
C:select * from Con_video_file_des_test where match (video_title,video_title_phonetic,video_title_en) against (' Jian Guo ');
D:select * from Con_video_file_des_test where match (video_title,video_title_phonetic,video_title_en) against (' Jian Guo da ye ');
A query has no data; b The query returns the first record, the C,d query returns two records, and the first record is the nut travels.
The explanation is as follows because MySQL is separated by the space to create the index, so a query does not find data, b query can find data; c,d query all query to the statement of the first record for the NUT travels is because match ... Against are sorted by relevance, from high to low;
Other Precautions:
1, the default search is not the case, to divide the case, columne character set to change from UTF8 to Utf8_bin.
2. Preset MATCH ... Against are sorted by relevance, from high to low.
3. The field in MATCH (title, content) must be identical to the field in Fulltext (title, content). Otherwise prompt Can ' t find fulltext index matching the column list.
4. Ft_min_word_len = Minimum license character for full-text search (default 4, viewable by show VARIABLES like ' Ft_min_word_len '), Chinese is usually two words is a word.
5, the search method has natural language retrieval (in NATURAL LANGUAGE mode) and Boolean retrieval (in Boolean mode) two, two kinds of retrieval is not in-depth study.
6, because the Chinese can not be a single word retrieval, a solution is to add phonetic columns, store Chinese into pinyin, the input of Chinese into pinyin to retrieve and return the results, but this will appear the input "Jianguo" found two records "the founding of the Great Cause", "nut travels."
MySQL's full-text search can deal with some simple application needs, for some of the more demanding applications can be achieved through other full-text search methods, such as Lucene, Nutch, SOLR and so on.

MySQL comes with full-text search