MySQL - ブール全文検索

こんにちは、データベース愛好家の皆さん!今日は、MySQLのブール全文検索の魅力あふれる世界に飛び込んでみましょう。あなたの近所の親切なコンピュータ教師として、この旅を案内するのが楽しみです。プログラミングが初めてであっても心配しないでください。基本から始めて、少しずつ進んでいきましょう。コーヒー(またはお好みで茶)を片手に、始めましょう!

MySQL - Boolean Fulltext Search

MySQLブール全文検索とは?

巨大な図書館で特定の本を探しているとします。手で一冊ずつ確認する代わりに、図書館司書に「龍についての本を探してほしいが、騎士については欲しくないし、魔法について触れている必要がある」と言えたらどうでしょうか?それは、MySQLのブール全文検索がデータベースで行うことです!

MySQLブール全文検索は、大量のテキストデータ内で特定の単語やフレーズを検索する強力な機能です。データベースに超賢い図書館司書がいるようなものです。

簡単な例から始めましょう:

CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
description TEXT,
FULLTEXT(description)
);

INSERT INTO books (title, description) VALUES
('The Dragon\'s Quest', '魔法の冒険で龍と魔法使いが登場します'),
('Knight\'s Tale', '勇敢な騎士とその冒険の話'),
('Enchanted Forest', '不思議な森に住む魔法の生物');

SELECT * FROM books
WHERE MATCH(description) AGAINST('dragons +magical -knights' IN BOOLEAN MODE);

この例では、'books'というテーブルを作成し、いくつかのサンプルデータを追加しています。最後のクエリが魔法の瞬間です。これは以下を検索します:

  • 「dragons」という単語を含む
  • 「magical」という単語を必ず含む(「+」がその役割)
  • 「knights」という単語を含まない(「-」がその役割)

結果は、「The Dragon's Quest」が条件をすべて満たすため返されます。

MySQLブール全文検索のオペレータ

次に、ブール全文検索で使用できるオペレータを見てみましょう。これらは、データベース司書への特別な指示と考えられます:

オペレータ 説明
+ この単語を必ず含める +dragon
- この単語を含まない -knight
> 関連性を向上させる >magic
< 関連性を低下させる <forest
* ワイルドカード(任意の終わりを一致させる) drag*
" " 正確なフレーズ "magic wand"
() グループ化 (+magic +wand) -broom

これらを実行してみましょう:

SELECT * FROM books
WHERE MATCH(description) AGAINST('>magic +creatures -knights ("enchanted forest")' IN BOOLEAN MODE);

このクエリは以下を検索します:

  • 「magic」を好む(但し必須ではありません)
  • 「creatures」を必ず含める
  • 「knights」を含まない
  • 「enchanted forest」という正確なフレーズを含む

どの本が返されると思いますか?そうです、「Enchanted Forest」ですね!

MySQLブール全文検索の機能

関連性評価

ブール全文検索の最も素晴らしい機能の1つは関連性評価です。MySQLは一致する結果を見つけるだけでなく、検索基準に対する一致度に基づいて評価します。

SELECT *, MATCH(description) AGAINST('magic dragons' IN BOOLEAN MODE) AS relevance
FROM books
WHERE MATCH(description) AGAINST('magic dragons' IN BOOLEAN MODE)
ORDER BY relevance DESC;

このクエリは、「magic」または「dragons」を含むすべての本を返し、それらの単語に対する関連性に基づいて評価します。

ワイルドカード検索

先ほど触れた「*」オペレータは、異なる終わりを持つ単語を見つけるのに非常に便利です:

SELECT * FROM books
WHERE MATCH(description) AGAINST('mag*' IN BOOLEAN MODE);

これは、「magic」、「magical」、「mage」など、終わりが異なる単語を含む本を見つけます。

フレーズ検索

正確なフレーズを見つけたい場合は、引用符を使用します:

SELECT * FROM books
WHERE MATCH(description) AGAINST('"magical adventure"' IN BOOLEAN MODE);

これは、「magical adventure」という正確なフレーズを含む本のみを返します。

クライアントプログラムを使用したブール全文検索

直接SQLクエリを使用している間、実際のシナリオではクライアントプログラムを使用してデータベースと対話することが多いです。以下は、mysql-connectorライブラリを使用したシンプルなPythonの例です:

import mysql.connector

def search_books(keyword):
conn = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="your_database"
)
cursor = conn.cursor()

query = "SELECT * FROM books WHERE MATCH(description) AGAINST(%s IN BOOLEAN MODE)"
cursor.execute(query, (keyword,))

results = cursor.fetchall()
for row in results:
print(f"Title: {row[1]}, Description: {row[2]}")

cursor.close()
conn.close()

search_books('+magic -knights')

この関数は、データベースに接続し、ブール全文検索を実行し、結果を表示します。

それでは、皆さん!MySQLのブール全文検索の領域を旅しました。基本概念から実践的な応用までです。思い出して、強力なツールは練習して慣れる必要があります。ですから、実験を恐れず、間違いを犯しましょう – それが学びの方法です!

最後に、私の教えの日々からの小さな話を:ある生徒がデータベース検索に苦戦していました。彼は針を刈り草の中から1本ずつ探そうとしていました。彼に全文検索を紹介したとき、彼の目は魔法を見つけたかのように輝きました。そして、実際、優れた技術は不可能なことを魔法のように見せるものです。

練習を続け、好奇心を持ち、ハッピーな検索をお楽しみください!

Credits: Image by storyset