MySQL的全文搜索索引
https://zenn.dev/hiroakey/articles/9f68ad249af20c
https://qiita.com/vukujin/items/49c7164061ffca61f1da
こちらは、検索文字列を含むか含まないかの0/1で判断します。また、修飾子を用いてより細かく条件を指定できるのも特長です。例えば
match(text) against ('A' in boolean mode) → 「A」を含むテキスト
match(text) against ('A B' in boolean mode) → 「A」「B」どちらかを含むテキスト
match(text) against ('+A +B' in boolean mode) → 「A」「B」両方含むテキスト
match(text) against ('A -B' in boolean mode) → 「A」を含み、「B」を含まないテキスト
match(text) against ('"A B"' in boolean mode) → 「A」「B」がその順に並んだフレーズを含むテキスト
今回は、厳密に単語一致・部分一致を実現したいため、boolean modeを使うこととします。
这个需要确认一下
innodb_ft_enable_stopword=0
AとかTHEとか、検索で引っかかっても分析にあまり寄与しない単語がストップワードとして事前に登録されています。デフォルト設定が英文に最適化したものなのでそうなっているのですね。日本語がメインのデータベースで、これらを除外したいという強い要件も無いので、今回はストップワードも検索対象とします。「IN THE LIFE」とかが検索できなくなりますし。。。
https://www.mysqltutorial.org/mysql-boolean-text-searches.aspx#:~:text=In%20the%20Boolean%20mode%2C%20MySQL,is%20suitable%20for%20experienced%20users.
+ 包括，该词必须存在。
– 排除，该词不得出现。
> 包括并增加排名值。
< 包括，并降低排名值。
() 将单词分组为子表达式（允许将它们作为一个组包含、排除、排名等）。
~ 否定一个词的排名值。
* 单词末尾的通配符。
“” 定义一个短语（与单个单词的列表相反，整个短语匹配包含或排除）。
The following examples illustrate how to use boolean full-text operators in the search query: To search for rows that contain at least one of the two words: mysql or tutorial ‘mysql tutorial’ To search for rows that contain both words: mysql and tutorial ‘+mysql +tutorial’ To search for rows that contain the word “mysql”, but put the higher rank for the rows that contain “tutorial”: ‘+mysql tutorial’ To search for rows that contain the word “mysql” but not “tutorial” ‘+mysql -tutorial’ To search for rows that contain the word “mysql” and rank the row lower if it contains the word “tutorial”. ‘+mysql ~tutorial’ To search for rows that contain the words “mysql” and “tutorial”, or “mysql” and “training” in whatever order, but put the rows that contain “mysql tutorial” higher than “mysql training”. ‘+mysql +(>tutorial <training)’ To find rows that contain words starting with “my” such as “mysql”, “mydatabase”, etc., you use the following: ‘my*’
The following examples illustrate how to use boolean full-text operators in the search query:
To search for rows that contain at least one of the two words: mysql or tutorial
‘mysql tutorial’
To search for rows that contain both words: mysql and tutorial
‘+mysql +tutorial’
To search for rows that contain the word “mysql”, but put the higher rank for the rows that contain “tutorial”:
‘+mysql tutorial’
To search for rows that contain the word “mysql” but not “tutorial”
‘+mysql -tutorial’
To search for rows that contain the word “mysql” and rank the row lower if it contains the word “tutorial”.
‘+mysql ~tutorial’
To search for rows that contain the words “mysql” and “tutorial”, or “mysql” and “training” in whatever order, but put the rows that contain “mysql tutorial” higher than “mysql training”.
‘+mysql +(>tutorial <training)’
To find rows that contain words starting with “my” such as “mysql”, “mydatabase”, etc., you use the following:
‘my*’
