MySQLの全文検索で意図しない行まで取得されてしまう

Question

CREATE TABLE industries (
  code INT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  FULLTEXT INDEX idx (name) WITH PARSER ngram
);

Query OK, 0 rows affected (0.56 sec)


INSERT INTO industries VALUES 
(1, '鉄鋼業'), 
(2, '非鉄金属工業'), 
(3, '鉄鋼・非鉄金属工業'), 
(4, '情報通信業');

Query OK, 4 rows affected (0.21 sec)
Records: 4  Duplicates: 0  Warnings: 0


SELECT * FROM industries WHERE 
MATCH (name) AGAINST ('鉄鋼・非鉄金属' IN BOOLEAN MODE);

+------+-----------------------------+
| code | name                        |
+------+-----------------------------+
|    3 | 鉄鋼・非鉄金属工業          |
|    2 | 非鉄金属工業                |
|    1 | 鉄鋼業                      |
+------+-----------------------------+
3 rows in set (0.01 sec)

MySQLのバージョンは8.0.12です。

上記のテーブルで、name列に「鉄鋼・非鉄金属」を含む行のみを取得したいのですが、意図しない他の行まで取得されてしまいます。（「鉄鋼・非鉄金属工業」の行のみ取得したいです。）

LIKE演算子など他の手法ではなく、全文検索で該当行のみ取得する方法を教えて頂きたいです。区切り文字の「・」が余計な挙動を引き起こしているのでしょうか？

よろしくお願いします。

（2020/10/03 追記）
ちなみに、以下の方法では解決できませんでした。

SELECT * FROM industries WHERE 
MATCH (name) AGAINST ('"鉄鋼・非鉄金属"' IN BOOLEAN MODE);

Empty set (0.01 sec)

参考
MySQL公式ドキュメント 12.9.2 ブール全文検索

（2020/10/04 追記）
回答欄に記した通り、NATURAL LANGUAGE MODEを使うことで、ひとまず問題は解決しております。
しかし、BOOLEAN MODEで中点（・）などの記号が文字として認識されないという根本的な問題は解決しておりませんので、わかる方がいらしたらご回答をよろしくお願いします。

Comments

[kuma_kuma_]

実際に実行されたSQLおよびその実行した結果の追記をおねがいします。

[janak]

失礼しました。実行結果が一部未記載でしたので追記致しました。

[Orlofsky]

参考にしたURLがあったら、質問に URLを https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先の説明](http...) で追記してください。

[kuma_kuma_]

こちらにMySQLが無い為こちらに記載しますが

SELECT * FROM industries WHERE
MATCH (name) AGAINST ('"鉄鋼・非鉄金属"' IN BOOLEAN MODE);

では結果どうなりますか？

[janak]

kuma_kuma_さん
そのクエリは試しましたが、結果は何も返ってきませんでした。

[hihijiji]

照合順序はどうなってますか？
https://dev.mysql.com/doc/refman/5.6/ja/full-text-adding-collation.html
_cs で設定してみては？

[kuma_kuma_]

申し訳ないのですが
SELECT * FROM industries WHERE
MATCH (name) AGAINST ('"鉄鋼・非鉄金属工業"' IN BOOLEAN MODE);
ではどうですか？

[sazi]

中点（・）が文字として扱われていない動作ですね。
https://dev.mysql.com/doc/refman/5.6/ja/fulltext-natural-language.html

[janak]

hihijijiさん
照合順序についてあまり詳しくないのですが、_csに設定すると大文字と小文字が区別されるようですね。SQLでは「大文字と小文字は区別しない」というのが暗黙的なルールになっている面があるので、できればそのような設定は避けたいですね…

[janak]

kuma_kuma_さん
そちらのクエリも結果はEmptyになってしまいます…

Answer 1

回答してくださった方達を参考に自分なりに調べた結果、問題の原因は中点（・）が文字として扱われていないということのようです。
この問題を解決する簡単な方法の一つは、BOOLEAN MODEではなく、NATURAL LANGUAGE MODEで全文検索をすることです。以下のように類似度が最も高くなります。

SELECT code, name, 
MATCH (name) AGAINST ('鉄鋼・非鉄金属工業' IN NATURAL LANGUAGE MODE) AS score 
FROM industries;

+------+-----------------------------+----------------------+
| code | name                        | score                |
+------+-----------------------------+----------------------+
|    1 | 鉄鋼業                      | 0.015609688125550747 |
|    2 | 非鉄金属工業                |  0.07804843783378601 |
|    3 | 鉄鋼・非鉄金属工業          |   0.8186105489730835 |
|    4 | 情報通信業                  |                    0 |
+------+-----------------------------+----------------------+
4 rows in set (0.00 sec)

確かに、BOOLEAN MODEでも類似度は高くなります。

SELECT code, name, 
MATCH (name) AGAINST ('鉄鋼・非鉄金属工業' IN BOOLEAN  MODE) AS score 
FROM industries;

+------+-----------------------------+----------------------+
| code | name                        | score                |
+------+-----------------------------+----------------------+
|    1 | 鉄鋼業                      | 0.009391550906002522 |
|    2 | 非鉄金属工業                |  0.04695775359869003 |
|    3 | 鉄鋼・非鉄金属工業          |  0.05634930357336998 |
|    4 | 情報通信業                  |                    0 |
+------+-----------------------------+----------------------+
4 rows in set (0.00 sec)

しかし、極端な例かも知れませんが「鉄鋼、非鉄金属工業」や「鉄鋼　非鉄金属工業」などのレコードがあった場合、BOOLEAN MODEでは中点（・）が文字として認識されていない以上同じ類似度になってしまいます。

SELECT code, name, 
MATCH (name) AGAINST ('鉄鋼・非鉄金属工業' IN BOOLEAN  MODE) AS score 
FROM industries;

+------+-----------------------------+----------------------+
| code | name                        | score                |
+------+-----------------------------+----------------------+
|    1 | 鉄鋼業                      | 0.004481872543692589 |
|    2 | 非鉄金属工業                | 0.022409362718462944 |
|    3 | 鉄鋼・非鉄金属工業          | 0.026891235262155533 |
|    4 | 情報通信業                  |                    0 |
|    5 | 鉄鋼、非鉄金属工業          | 0.026891235262155533 |
|    6 | 鉄鋼　非鉄金属工業          | 0.026891235262155533 |
+------+-----------------------------+----------------------+
6 rows in set (0.01 sec)

NATURAL LANGUAGE MODEの場合。

SELECT code, name, 
MATCH (name) AGAINST ('鉄鋼・非鉄金属工業' IN NATURAL LANGUAGE MODE) AS score 
FROM industries;

+------+-----------------------------+----------------------+
| code | name                        | score                |
+------+-----------------------------+----------------------+
|    1 | 鉄鋼業                      | 0.015609688125550747 |
|    2 | 非鉄金属工業                |  0.07804843783378601 |
|    3 | 鉄鋼・非鉄金属工業          |    1.304697036743164 |
|    4 | 情報通信業                  |                    0 |
|    5 | 鉄鋼、非鉄金属工業          |  0.09365812689065933 |
|    6 | 鉄鋼　非鉄金属工業          |  0.09365812689065933 |
+------+-----------------------------+----------------------+
6 rows in set (0.00 sec)

ただ、NATURAL LANGUAGE MODEではBOOLEAN MODEでは行える「+」や「-」などのクエリが使えなくなるのがデメリットだと思います。BOOLEAN MODEでも中点などの記号を文字として認識させる方法がもしあれば教えて頂きたいです。

（参考）
MySQL公式ドキュメント 12.9.4 全文ストップワード

「ストップワードリストは自由形式で、改行、空白、カンマなどの英数字以外の文字でストップワードが区切られます。例外として、下線文字 (「_」) と単一アポストロフィー (「'」) は単語の一部として処理されます。ストップワードリストの文字セットは、サーバーのデフォルト文字セットです。セクション10.1.3.1「サーバー文字セットおよび照合順序」を参照してください。」

Comments

[kuma_kuma_]

うまく動くか不安ですが

SELECT code, name,
MATCH (replace(name, "・", "30fb")) AGAINST ('鉄鋼30fb非鉄金属' IN NATURAL mode MODE) AS score
FROM industries;
で試してみて下さい。

[sazi]

解決済みにしたのなら、文中で質問するのではなく、新たに質問した方が良いと思いますよ。

[kuma_kuma_]

saziさん

私も以前この様な事があったとき「新たに質問した方が良い」と書いたのですが
他の方から「2重の質問になるから解決済みを撤回したほうが良い」と指摘されました。
よって「解決済みを撤回」をオススメします。

[sazi]

@kuma_kuma_さん
内容的に続いてますので、私もこの質問での継続を薦めます。
解決済みのままだと、多分他の回答者の目にとまらないと思いますので、解決済みにしたいのなら、という事でコメントしました。

[kuma_kuma_]

saziさん

了解しました。
自分の時はすごい勢いでおこられました為、書かせていただいた次第です。

[janak]

kuma_kuma_さん、saziさん
アドバイスありがとうございます、未解決に戻します。

Answer 2

中点（・）が文字として扱われていない動作ですね。
12.9.1 自然言語全文検索

MySQL FULLTEXT の実装では、トゥルーワード文字 (文字、数字、およびアンダースコア) のシーケンスが単語とみなされます。

若しくはストップワードとして登録されたりしていませんか？

積極的ではありませんが、中点を含む場合にはそれぞれを単語として検索するとか

SELECT * FROM industries WHERE 
MATCH (name) AGAINST ('+鉄鋼 +非鉄金属' IN BOOLEAN MODE)

Comments

[janak]

ご回答ありがとうございます。
おっしゃる通り、中点が文字として認識されていないようですね。

INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORDで確認したところ、ストップワードには登録されていませんでした。（初期設定のまま）
システム変数のft_stopword_fileを「''」に設定することも試しましたが、解決しませんでした。

[sazi]

後は、検索している中点と登録されている中点が同じかどうかですね。