Word searching in MySQL

How to create a table with word search indexing.

mysql> CREATE TABLE t (a VARCHAR(200), b TEXT, FULLTEXT (a,b));
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t VALUES
    -> ('MySQL has now support', 'for full-text search'),
    -> ('Full-text indexes', 'are called collections'),
    -> ('Only MyISAM tables','support collections'),
    -> ('Function MATCH AGAINST()','is used to do a search'),
    -> ('Full-text search in MySQL', 'implements vector space model');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM t WHERE MATCH (a,b) AGAINST ('MySQL');
+---------------------------+-------------------------------+
| a | b |
+---------------------------+-------------------------------+


| MySQL has now support | for full-text search |
| Full-text search in MySQL | implements vector-space-model |
+---------------------------+-------------------------------+
2 rows in set (0.00 sec)

mysql> SELECT *,MATCH a,b AGAINST ('collections support') as x FROM t;
+---------------------------+-------------------------------+--------+
| a | b | x |
+---------------------------+-------------------------------+--------+
| MySQL has now support | for full-text search | 0.3834 |
| Full-text indexes | are called collections | 0.3834 |
| Only MyISAM tables | support collections | 0.7668 |
| Function MATCH AGAINST() | is used to do a search | 0 |
| Full-text search in MySQL | implements vector space model | 0 |
+---------------------------+-------------------------------+--------+
5 rows in set (0.00 sec)

 

Share this article!

Follow us!

Find more helpful articles: