Full-text indexes can be used with MyISAM tables and InnoDB tables (in MySQL 5.6+ only), and can be created only for varchar, char and text columns only.
A FULLTEXT index definition can be given in the CREATE TABLE statement when a table is created, or added later using ALTER TABLE or CREATE INDEX.
/** create table **/
CREATE TABLE products (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(75) NOT NULL,
sku VARCHAR(75) NOT NULL,
description MEDIUMTEXT NOT NULL,
PRIMARY KEY(id)
)ENGINE=MyISAM;
/** add full text indexes **/
CREATE FULLTEXT INDEX ft_index_name ON products (name, description);
Full-text searching is performed using
MATCH() and
AGAINST syntax.
MATCH() takes a comma-separated list that names the columns in which to be searched.
AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row.
INSERT INTO `abc`.`products` (`id`, `name`, `sku`, `description`) VALUES (NULL, 'watch', 'w2658', 'test watch'), (NULL, 'MOBILE', 'M25744', 'Test mobile')
INSERT INTO `abc`.`products` (`id`, `name`, `sku`, `description`) VALUES (NULL, 'watch1', 'w26581', 'test watch'), (NULL, 'MOBILE1', 'M257441', 'Test mobile');
There are three types of full-text searches:
1. A boolean search interprets the search string using the rules of a special query language. The string contains the words to search for. It can also contain operators that specify requirements such that a word must be present or absent in matching rows, or that it should be weighted higher or lower than usual. Common words such as “some” or “then” are stopwords and do not match if present in the search string. The IN BOOLEAN MODE modifier specifies a Boolean search.
2. A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if no modifier is given.
3. A query expansion search is a modification of a natural language search. The search string is used to perform a natural language search. Then words from the most relevant rows returned by the search are added to the search string and the search is done again. The query returns the rows from the second search. The WITH QUERY EXPANSION modifier specifies a query expansion search. For more information, see Section 12.9.3, “Full-Text Searches with Query Expansion”.
Example
SELECT *
FROM products WHERE MATCH (name,description) AGAINST ('mobile')
SELECT *
FROM products WHERE MATCH (name,description) AGAINST ('mobile' in boolean mode)
select * from products WHERE MATCH(name,description) AGAINST ('+mobile -computer' IN BOOLEAN MODE);
See following for searching....
'mobile computer'
search mobile or computer
'+mobile +computer'
search for both mobile and computer
'+mobile -computer'
search for mobile without computer
'+mobile +computer'
search for both mobile and computer
‘+mobile ~computer’
Find rows that contain the word “mobile”, but if the row also contains the word “computer”, rate it lower than if row does not.
‘+mobile +(>computer <television)’
Find rows that contain the words “mobile” and “computer”, or “mobile” and “television” (in any order), but rank “mobile computer” higher than “mobile television”.
| Operator | Description |
|---|
| + | Include, word must be present. |
| - | Exclude, word must not be present. |
| > | Include, and increase ranking value. |
| < | Include, and decrease ranking value. |
| () | Group words into sub expressions |
| ~ | Negate a word’s ranking value. |
| * | Wildcard at end of word. |
| “” | Defines a phrase (as opposed to a list of individual words, the entire phrase is matched for inclusion or exclusion). |
