MySQL Must Know must Know – Concepts Section

MySQL must know must – Install application section

MySQL must know must – retrieve data sections

MySQL must know must know – join tables and advanced query sections

The database files used below are available in themysql_scriptsTo find it.

Full-text search

Not all engines support full-text search, for example MyISAM supports full-text search, InnoDB does not.

The CREATE TABLE statement accepts the FULLTEXT clause, which can index one or more subsequent tables. MySQL maintains the index automatically, updating it as rows are added, updated, or deleted. FULLTEXT can also be specified after the table is created.

CREATE TABLE 'productNotes' (' note_id' int(11) NOT NULL AUTO_INCREMENT, `prod_id` char(10) NOT NULL, `note_date` datetime NOT NULL, `note_text` text, PRIMARY KEY (`note_id`), FULLTEXT KEY `note_text` (`note_text`) ) ENGINE=MyISAM AUTO_INCREMENT=115 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciCopy the code

Once the index is created, you can perform a full-text search with Match() and Against(), where Match() specifies the column to be searched and Against() specifies the search expression to use.

### search for rabbit in node_text. SELECT note_text FROM productNotes WHERE note_text LIKE '%rabbit%'; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Quantity around,... For use as rabbit bait. | | the Customer an: rabbit has... | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + # # # use the text search, can see a rabbit in the third word in the text than ranked 20th one sort of high. SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit'); + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Customer an: rabbit has... | | Quantity around... For use as rabbit bait. | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + # # # can use the following statement to check the sorting of the SELECT note_text grade, Match(note_text) Against('rabbit') AS rank1 FROM productnotes; # # # in addition to found out the two data, other grades are 0 | Customer an: rabbit has... | level is 1.6408053636550903 | Quantity around... For use as rabbit bait. | level is 1.5905543565750122 # # # using QUERY EXPANSION WITH QUERY EXPANSION, find all mentioned anvils annotation, but also find out all the other related to the current search, Even if they do not contain anvils SELECT note_text FROM productNotes WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION); ### will return 7 lines, but only the first line has anvils. The second line, which has nothing to do with anvils, is retrieved because it contains two words from the first line.Copy the code

Pay attention to

  • The value passed to Match() must be the same as in the FULLTEXT() definition. If you specify multiple columns, they must be listed in the correct order.
  • After comparison, it can be found that text search is sorted by default, while the results of LIKE search are arbitrary and output according to the sequence of query.
  • The level of text search is calculated based on the number of words in the line, the number of unique words, the total number of words in the entire index, and the bibliography of lines containing that word.
  • When indexing full-text data, short words are ignored and excluded from the index. Short words are defined as words with three or fewer characters (this number can be changed if necessary)
  • Many words appear so frequently that it is useless to search for them. MySQL has a 50% rule that ignores a word as a non-word if it appears in more than 50% of the lines. The 50% rule does not apply to IN BOOLEAN MODE.
  • If the number of rows in the table is less than 3, the full-text search does not return results
  • Ignore single quotation marks in words. For example, don’t is indexed as dont
  • Full-text search results cannot be returned properly for languages that do not have word separators (including Japanese and Chinese)

Boolean text query

IN BOOLEAN MODE can be used even if there is no FULLTEXT index

SELECT note_text FROM productNotes WHERE Match(note_text) Against('heavy -rope*' IN) BOOLEAN MODE); ### Match the words safe and combination, lowering the level of the latter. SELECT note_text FROM productnotes WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);Copy the code

Full-text Boolean operator

Boolean operator Said Ming
+ Contain, the word must exist
– Exclusion, the word must not appear
> Contains, and increases the rank value
< Contains, and reduces the level value
(a) Grouping words into subexexpressions (allowing these subexexpressions to be included, excluded, arranged, etc., as a group)
~ Cancels the sorting value of a word
* A wildcard character at the end of a word
“” Define a phrase (unlike a list of single words, which matches the entire phrase to include or exclude it)

Front- end-Basics: Watch, Star, Front-End-Basics: Front-End-Basics

MySql must know must know this article