Re: Free text search in MySQL (again!)

From: Stefan Ram <ram_at_zedat.fu-berlin.de>
Date: 26 Apr 2016 09:41:11 GMT
Message-ID: <fulltext-20160426103910_at_ram.dialup.fu-berlin.de>

bit-naughty_at_hotmail.com writes:

>If I have the text "The rat in the hat" in a particular field
>in a MYSQL table, and I want someone typing in "hat rat" into
>a search box on my website to get that line, what do I do? 

mysql> DROP SCHEMA SCHEMA20160426102308; Query OK, 1 row affected (0.00 sec)

mysql> CREATE SCHEMA SCHEMA20160426102308; Query OK, 1 row affected (0.00 sec)

mysql> USE SCHEMA20160426102308;
Database changed

mysql>
mysql> CREATE TABLE T20160426102334

-> ( particular_field varchar( 255 ) NOT NULL,
-> FULLTEXT KEY particular_field ( particular_field ))
-> ENGINE=MyISAM;

Query OK, 0 rows affected (0.04 sec)

mysql>
mysql> INSERT INTO T20160426102334 ( particular_field )

-> values ( 'The rat in the hat' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T20160426102334 ( particular_field )

-> values ( 'A large garret lit by oil lamps hung from ceiling.' );
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO T20160426102334 ( particular_field )

-> values ( 'It was a rimy morning, and very damp.' );
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT particular_field FROM T20160426102334 WHERE

-> MATCH ( particular_field )
-> AGAINST ( 'damp morning' IN NATURAL LANGUAGE MODE );

+---------------------------------------+
| particular_field                      |
+---------------------------------------+

| It was a rimy morning, and very damp. |

+---------------------------------------+

1 row in set (0.00 sec)

mysql>

  To make words with 3 characters searchable, you need to   adjust the server variable ft_min_word_len via an   option file, restart the server and rebuild the index. Received on Tue Apr 26 2016 - 11:41:11 CEST