MySQL Full Text Search Ngram Mybatis

Create full-text index (fulltext index)

Create a full-text index while creating a table

Fulltext (name) with PARSER Ngram

To add by ALTER TABLE

ALTER TABLE ' Das '. ' staff_base ' Add fulltext index staff_base_name (' name ') with parser Ngram;

Direct through CREATE INDEX (not tested)

CREATE fulltext INDEX ft_email_name on ' student ' (' name ')

You can also specify the length of the index when you create the index:

CREATE fulltext INDEX ft_email_name on ' student ' (' name ' (20))

Delete Full-text index (not tested)

Use DROP Index directly (note: No drop fulltext Index this usage)

DROP INDEX full_idx_name on Tommy.girl;

How to use ALTER TABLE

ALTER TABLE tommy.girl DROP INDEX FT_EMAIL_ABCD;

Using Full-Text indexing

Format using full-Text indexing: MATCH (columnName) against (' string ')

1. Search by Natural language mode:

Get the number of matching criteria

SELECT COUNT (*fromWHERE MATCH (title,body) against (' database ' in Naturallanguage MODE);

Get a match ratio

SELECT ID, MATCH (title,body) against (' database ' in as from articles;

2. Search in Boolean mode, which is more complex than natural mode search:

Match records that have both management and database

SELECT *  from WHERE MATCH (Title,body) against ('+ database + Admin ' in BOOLEAN MODE);

Matches a record that has a database but no management

SELECT *  from WHERE MATCH (Title,body) against ('+ database-admin ' in BOOLEAN MODE);

Match MySQL, but reduce database dependencies

SELECT *  from WHERE MATCH (Title,body) against ('> Database +mysql' inboolean MODE);

3. Query the extension mode, such as to search the database, then mysql,oracle,db2 will also be searched for

SELECT *  from WHERE MATCH (Title,body) against (' database ' with QUERY EXPANSION);

4. Ft_boolean_syntax (+->< () ~*: "" &|) Examples of Use:

+: Used in front of the word to indicate that the word must be included, and at the start position.

Eg: +apple match: "Apple123", "Tommy, Apple"

-: Does not contain the word, so you cannot use "-yoursql" so that you cannot find any row, it must be used in conjunction with other syntax.

Eg:match (name) against ('-lime +oracle ')

Match to: All records that do not contain lime but contain Oracle

Empty (that is, by default), which is optional and has a higher order of words.

Example:

　　　　　　apple banana　　　　Find the record line that contains at least one of the words above. or the relationship

　　　　　　+apple +juice　　　　Two words are included. With the relationship

　　　　　　+apple macintosh　　　　Contains the word "apple", but if it contains "Macintosh", it will be arranged a bit higher

　　　　　　+apple -macintosh　　　　Contains "Apple" but does not contain "Macintosh"

>: To increase the relevance of the word, the results of the query will be ranked in front of the comparison.

<: Reduce the relevance, the results of the query will be ranked in the post-comparison position.

Do not use >< first can see the exact match of the row compared to the front

Select *  from where match (girl_name) against (' laser ' in boolean mode);

Using > Using > Alone, Li Shuchen is on the front line right now.

Select *  from where match (girl_name) against (' laser > Li Shuchen ' in boolean mode);

Use alone < see no, not people also line up to the front, here is the use of < Oh, agreed to reduce the relevance of it, look down.

Select *  from where match (girl_name) against (' Laser < not human ' in Boolean mode) ;

At the same time use >< here finally have the answer, as long as the use of >< will go to the front row, and > is always in front of the <

1. As long as the use of >< of the total than useless before

2. Use a certain ratio of > to the front of the < row (this is in line with the correlation increase and decrease)

3. Use the same class, the earlier the use, the higher the row.

Select *  from where match (girl_name) against (' laser > Li Shuchen < workbook < not human > being a ghost ' in Boolean mode);

(): You can use the word condition with parentheses.

Eg: +aaa + (>bbb <CCC)

Found with AAA and BBB and CCC,AAA and BBB, or AAA and CCC (because BBB,CCC front does not have +, so means dispensable), then aaa&bbb > AAA&BBB&CCC > AAA&CCC

~: The correlation is negative by positive, indicating that owning the word will decrease the correlation, but not as "-" will exclude it, just in the back.

Eg: +apple ~macintosh matches Apple first, but if it contains the Macintosh at the same time, it will be ranked back.

*: Wildcard character, this can only be followed by a string.

MATCH (Girl_name) against (' +*abc* ') #错误, cannot be placed in front

MATCH (Girl_name) against (' + wield * ') #正确

"": the whole match, enclose a sentence in double quotation marks to be fully consistent, not chaizi.

Eg: "Tommy Huang" can match Tommy Huang xxxxx but does not match Tommy is Huang.

Use full-text indexing in MyBatis Note:

Front-end Incoming data format name: "", Name: "Lime Oracle"

 PackageCom.das.mapper.service;Importorg.apache.commons.lang3.StringUtils;ImportOrg.apache.ibatis.jdbc.SQL;ImportJava.util.HashMap;ImportJava.util.Map;/*** @Author liangmy * @Date 2018/2/26*/ Public classServicebaseprovider { PublicString getservicebaselist (map<string, object>map) {stringbuffer name=NewStringBuffer ();  for(String str: (NULL= = Map.get ("name")? "": Map.get ("name"). ToString (). Trim () + ""). Split ("") {name.append ("+" + str + ""); }        if(Name.length () > 2) {Name.deletecharat (Name.length ()-1); } String Level=NULL= = Map.get ("level")? "": Map.get ("level")). toString ();        SYSTEM.ERR.PRINTLN (name);        System.err.println (level); return NewSQL () {{SELECT} ("id"); From ("Service_base"); if(!stringutils.isempty (name)) {WHERE ("Match (name) against (\" "+ name.tostring () +" \ "in Boolean mode)"); }                if(!Stringutils.isempty (level)) {and (). WHERE ("Json_contains (Level, '" + Level + "')");    }}}.tostring (); }}

La La la

MySQL uses full-text indexing (fulltext index)

InnoDB Full-text index: N-gram Parser "Go"

MySQL Full Text Search Ngram Mybatis