MySQL Full Text Search Ngram Mybatis

Source: Internet
Author: User
Tags create index

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.