Full-Text Search in MySQL server lets users run full-text queries against character-based data in MySQL tables. You must create a full-text index on the table before you run full-text queries on a table. The full-text index can include one or more character-based columns in the table.
MySQL Version: 8.0
Syntax:
MATCH (col1,col2,col3...) AGAINST (expr [search_modifier])
There are three types of full-text searches :
Note: Some words are ignored in full-text searches.
Natural Language Full-Text Searches
Natural language full-text search interprets the search string as a free text (natural human language) and no special operators are required. Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier (see the following syntax) is given or not. MATCH() function searches a string against a text collection (A set of one or more columns included in a FULLTEXT index.). For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string (given as the argument to AGAINST() function) and the text in that row in the columns named in the MATCH() list.
The basic format of a natural Language null-text searches mode query is as follows:
Code:
SELECT * FROM table_name WHERE MATCH(col1, col2)
AGAINST('search terms' IN NATURAL LANGUAGE MODE)
Example:
mysql> CREATE TABLE tutorial ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), description TEXT, FULLTEXT(title,description) ) ENGINE=InnoDB; Query OK, 0 rows affected (2.40 sec)
Let insert some records in tutorial table:
mysql>INSERT INTO tutorial (title,description) VALUES
('SQL Joins','An SQL JOIN clause combines rows from two or more tables. It creates a set of rows in a temporary table.'),
('SQL Equi Join','SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality.'),
('SQL Left Join','The SQL LEFT JOIN, joins two tables and fetches rows based on a condition, which is matching in both the tables and the unmatched rows will also be available from the table before the JOIN clause.'),
('SQL Cross Join','The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN.'),
('SQL Full Outer Join','In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.'),
('SQL Self Join','A self join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY which references its own PRIMARY KEY.');
Let search the string 'left right' in description field:
Output:
mysql> SELECT * FROM tutorial WHERE MATCH(title,description) AGAINST ('left right' IN NATURAL LANGUAGE MODE);
+----+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | title | description |
+----+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 5 | SQL Full Outer Join | In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause. |
| 3 | SQL Left Join | The SQL LEFT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables, and the unmatched rows will also be available from the table before the JOIN clause. |
+----+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
By default, the search is case-insensitive. To perform a case-sensitive full-text search, use a binary collation for the indexed columns. For example, a column that uses the latin1 character set of can be assigned a collation of latin1_bin to make it case sensitive for full-text searches.
When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first.
The following example shows how to retrieve the relevance values explicitly:
Output:
mysql> SELECT id, MATCH(title,description) AGAINST ('left right' IN NATURAL LANGUAGE MODE) AS score FROM tutorial;
+----+---------------------+
| id | score |
+----+---------------------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0.45528939366340637 |
| 4 | 0 |
| 5 | 0.8331640362739563 |
| 6 | 0 |
+----+---------------------+
6 rows in set (0.00 sec)
Count matches
To count matches, you can use a query like this:
Output:
mysql> SELECT COUNT(*) FROM tutorial WHERE MATCH(title,description) AGAINST ('left right' IN NATURAL LANGUAGE MODE);
+----------+
| COUNT(*) |
+----------+
| 2 |
+----------+
1 row in set (0.03 sec)
Boolean Full-Text Searches
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. Certain common words (stopwords) are omitted from the search index and do not match if present in the search string. MySQL can perform boolean full-text searches using the IN BOOLEAN MODE modifier. With this modifier, certain characters have special meaning at the beginning or end of words in the search string.
The basic format of a boolean mode query is as follows :
Code:
SELECT * FROM table_name WHERE MATCH(col1, col2)
AGAINST('search terms' IN BOOLEAN MODE)
Characteristics of Boolean Full-Text searches :
The boolean full-text search supports the following operators:
Operator Description Example+
A leading plus sign indicates that a word must be present in each row that is returned.'+join +union'
Find rows that contain both words.
'+join union'
Search rows that contain the word 'join', but rank rows higher if they also contain 'union'
Example: Boolean Full-Text Searches
In the following query, the query retrieves all the rows that contain the word 'Joins' but not 'right'.
Output:
mysql> SELECT * FROM tutorial WHERE MATCH(title,description) AGAINST ('+Joins -right' IN BOOLEAN MODE);
+----+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | title | description |
+----+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | SQL Joins | An SQL JOIN clause combines rows from two or more tables. It creates a set of rows in a temporary table. |
| 3 | SQL Left Join | The SQL LEFT JOIN, joins two tables and fetches rows based on a condition, which is matching in both the tables and the unmatched rows will also be available from the table before the JOIN clause. |
+----+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.02 sec)
Previous: LEFT shift and RIGHT shift
Next: MySQL Cast functions and Operators
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4