Some examples of MySQL full text search

One, the longest use of MySQL search engine for MyISAM and InnoDB, the former support, the latter does not support.

Second, the full text search has more than like matching and regular expression matching more powerful features, in the table for Full-text search, MySQL does not need to view each row table, do not need to analyze and deal with each word separately. MySQL creates an index of the words in the specified column, and the search is available for those words. In this way, MySQL can effectively decide which words to match (which rows contain them), which words do not match, how often they match, and so on.

Third, in order to carry out full text search, must index the searched columns, and as the data changes constantly index. After the table columns are properly designed, MySQL automatically makes all indexes and indexes. After the index, select can be used with match () and against () to actually perform the search.

Generally, you enable full text search when you create a table. The CREATE Table statement accepts the FULLTEXT clause, which gives a comma-delimited list of indexed columns.

The code is as follows	Copy Code
CREATE TABLE Productnotes ( note_id INT not NULL auto_increament, prod_id CHAR (Ten) not NULL, Note_data DATETIME not NULL, Note_text text NULL, Priary KEY (note_id), Fulltext (Note_text) ) Engine=myisam

Only one column Note_text is specified here for indexing, and multiple columns can be specified if necessary. After the definition, MySQL automatically maintains the index. When rows are added, deleted, and updated, the index is automatically updated. You can use Fulltext when you create a table, or you can use it later.
Note: Do not use fulltext when importing data.

Full-text indexing is an fulltext type index in MySQL. The fulltext index is used for MyISAM tables, which can be created on a CHAR, VARCHAR, or TEXT column by using ALTER table or create index at or after the CREATE table. For large databases, it is very quick to load the data into a table with no Fulltext index and then use ALTER table (or CREATE index) to create the index. It will be very slow to load data into a table that already has a fulltext index.

After the index, use the two functions match () and against () to perform a full text search, where match () is the column being searched, against () specifies the search expression to use:

Select Note_text from Productnotes where match (Note_text) against (' Hello ');

Note: 1. The value passed to match () must be the same as the Fulltext () definition. If you specify more than one column, they must be listed and in the correct order. 2. Full-text search is case-insensitive unless the binary method is used.
Try this statement: Select Note_text,match (note_text) against as rank from productnotes

Use query extensions to find possible results, even if they don't exactly contain the words they're looking for.

Select Note_text from Productnotes where match (Note_text) against (' Hello ' with QUERY expansion).

Characteristics:
It's better than like speed.
A good match for English words
Easy to use without extra support
Full-Text Indexing fulltext field information for string types
It's slow to do batch updates if you have this index
Only MyISAM types of tables support fulltext Full-text indexing
The default ft_min_word_len=4 is to ignore words less than 4 characters, and the user can change
Built-in reserved words, like some,big,the and so on, will be directly ignored
For a large database, loading the data into a table without a Fulltext index, and then creating the index using ALTER TABLE (or CREATE index), is very fast, whereas it is very slow

Full-Text search with query extensions

Full-Text Search supports query extension (especially its changeable "blind query extension"). If the length of the search phrase is too short, then users need to rely on the full text search engine often lack of implicit knowledge to query. At this point, query extension functionality is often useful. For example, a user who searches for the word "database" may think that "MySQL", "Oracle", "DB2" and "RDBMS" are items that conform to "databases" and should therefore be returned. This is both implicit knowledge.
Add the WITH query expansion after the following search phrase to activate the blind Query extension feature (commonly referred to as automatic relevance feedback). It performs two searches, where the search phrase for the second search is the original search phrase for the few top-level file connections found at the time of the first search. Thus, if one of these files contains the word "databases" and the word "MySQL", the second search will look for files that contain the word "MySQL", even if they do not contain the word "database."

SELECT * from info where match (title,info) against (' DataBase ' with query expansion);

Boolean Full-text Search

Characteristics:
They do not use a 50%-field value
They do not categorize rows in the order in which they are in a weak correlation. You can see this in the above query results: The most relevant row is a row that contains two "MySQL", but it is listed in the last position, not the beginning position
Even without Fulltext, they can still work, even though the search execution in this way is very slow
Minimum word length full-text parameters and maximum word length full-text parameters are applicable

Stop Word Apply

The performance of Boolean Full-text search supports the following operators:
"+" a leading plus sign indicates that the word must appear at the beginning of each row returned
"–" a leading minus sign indicates that the word must not appear in any returned rows
(no operator) in the default state (when no + or – is specified), the word is optional, but contains a higher row level for that word. This and match () ... Against () does not use in BOOLEAN mode to modify the program is very similar to the operation
The two operators "> <" are used to change the effect of a word on the value assigned to a row. The > operator enhances its impact, while the < operator weakens its impact. See the example below
The "()" bracket is used to divide words into child expressions. The enclosed brackets can be nested
"~" A leading tilde is used as a negative character to negate the effect of a word on the row's relevance. This is useful for words that mark "noise (useless information)." The line containing this type of word is lower than the other line, but it may be used concurrently with the-number, so it will not dispatch all the useless information lines at any time
The "*" asterisk is used as a truncation character. Unlike other symbols, it should be appended to the word to be truncated.
"" "a phrase enclosed in double quotes ('") matches only the line that literally contains the input format of the phrase. The Full-text engine splits the phrase into words and searches for the word in the Fulltext index. Non-word characters do not require rigorous matching: phrase searches require only those words that match the word that the search phrase contains and the words are arranged in the same order. For example, "test phrase" conforms to "test, phrase".
"Configured database" looks for rows that contain at least two words
"+configured +database" looks for lines that are contained in two words
"+configured database" looks for rows that contain the word "configured", and if those lines contain the word "database", they are ranked higher
"+configured-database" for rows that contain the word "configured" but do not contain the word "database"
"+configured + (>database<sql)" looks for rows that contain the word "configured" and "database", or rows that contain "configured" and "SQL" (no order), but contains " Configured database row is higher than the row containing "configured SQL"
"mysql*" looks for lines that contain "MySQL" or "MySQL" at the beginning of a word
"My SQL" to find a line containing the original phrase "my SQL"

SELECT * from info where match (title,info) against ("+mysql-yoursql" in Boolean mode);
SELECT * from info where match (title,info) against (' +configured +database ');

In fact, in addition to the MySQL itself with this function, but for Chinese characters is not very accurate, small series heard that Google has a full text search of MySQL plug-ins you can search for a look.