Use MATCH in where statement

     
mysql>
mysql>
mysql> CREATE TABLE titles (
    ->   titleID int(11),
    ->   title varchar(100),
    ->   subtitle varchar(100),
    ->   edition tinyint(4),
    ->   publID int(11),
    ->   catID int(11),
    ->   langID int(11),
    ->   year int(11),
    ->   isbn varchar(20),
    ->   comment varchar(255),
    ->   ts timestamp,
    ->   authors varchar(255),
    ->   PRIMARY KEY  (titleID)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>
mysql>
mysql> INSERT INTO titles VALUES (1,'Linux','Installation',5,1,57,2,2000,NULL,NULL,'2005-02-28 13:34:21','Michael'),
    ->                           (2,'Excel',NULL,NULL,2,3,NULL,2000,NULL,NULL,'2005-02-28 13:34:22','David'),
    ->                           (3,'XML',NULL,NULL,1,2,NULL,1997,NULL,NULL,'2005-02-28 13:34:22','Edwards'),
    ->                           (4,'PHP',NULL,NULL,3,6,NULL,2000,NULL,NULL,'2005-02-28 13:34:22','Tom'),
    ->                           (5,'MySQL','',0,3,34,NULL,2000,'','','2005-02-28 13:34:22','Paul'),
    ->                           (6,'Java',NULL,NULL,4,34,NULL,1999,NULL,NULL,'2005-02-28 13:34:22','Tim');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql>
mysql> ALTER TABLE titles ADD FULLTEXT(title, subtitle);
Query OK, 6 rows affected (0.01 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> SELECT title, MATCH(title, subtitle) AGAINST('excel') AS fulltextmatch
    -> FROM titles
    -> WHERE MATCH(title, subtitle) AGAINST('excel') > 0.001
    -> ORDER BY fulltextmatch DESC;
+-------+----------------+
| title | fulltextmatch  |
+-------+----------------+
| Excel | 1.591139793396 |
+-------+----------------+
1 row in set (0.00 sec)

mysql>
mysql> drop table titles;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql>

Related examples in the same category

1.	A MATCH expression for fulltext search can be used to order results.
2.	MATCH(TITLE) AGAINST ('to')
3.	Using match in where clause
4.	Using match in select statement
5.	Using the same match...against clause in select clause and where clause
6.	Matches two words
7.	Matches two columns
8.	Matches two columns in boolean mode
9.	Match two words in boolean mode
10.	Match against a long sentence
11.	Get the numbers and relevance values of the books in which distributed appears in the summary.
12.	Get the numbers and titles of the books in which database appears in the title.
13.	Get the numbers and titles of the books in which the phrase design implementation appears.
14.	You can include additional criteria to narrow the search further.
15.	Full-Text Search syntax

Use MATCH in where statement : MATCH « FullText Search « SQL / MySQL

Related examples in the same category