Clever Geek Handbook

MySQL Full text hashtag search (including # in index)

I'm pretty sure there must be a way to find hashtags using a full text index on a MyISAM table. The default setting will do the following:

textfield 
hashtag
#hashtag
#two #hashtag #hashtag

SELECT * FROM table WHERE MATCH(textfield) AGAINST ('#hashtag')
> | hashtag                |
> | #hashtag               |
> | #two #hashtag #hashtag |

For now, it should only return 2 and 3 rows. It looks like hashtag is treated as a word separator, so it is "removed" before searching. What can I do to enable indexing and searching for terms containing #

as part of a word?

+2


source to share


1 answer


As described in Fine-tuning MySQL Full-Text Search :



You can change the set of characters that count as word characters in several ways, as described in the following list. After making the changes, rebuild the indexes for each table that contains any indexes FULLTEXT

. Suppose you want to treat the hyphen ( '-'

) character as a word character. Use one of the following methods:

  • Change MySQL source: storage/myisam/ftdefs.h

    See macros true_word_char()

    and misc_word_char()

    . Add '-'

    to one of these macros and recompile MySQL.

  • Modify the character set file: this does not require recompilation. The macro true_word_char()

    uses a "character type" table to distinguish letters and numbers from other characters. You can edit the contents of the array <ctype><map>

    in one of the character set XML files to indicate what '-'

    is a letter. Then use the given character set for your indices FULLTEXT

    . For information on the format of the array, <ctype><map>

    see Section 10.3.1, "Character Definition Matrices" .

  • Add a new collation for the character set used by the indexed columns and modify the columns to use this collation. For general information on adding collations, see Section 10.4, “Adding a Sort to a Character Set” . For an example specific to full-text indexing, see Section 12.9.7, “Adding a Sort for Full-Text Indexing” .

+2


source






More articles:


All Articles