この実験 (Lab) では、MySQL の全文検索機能を探求します。まず、id、title、content のカラムを持つ articles という名前のデータベースとテーブルを作成し、サンプルデータでそれを投入します。
次に、ALTER TABLE ステートメントを使用して、articles テーブルに article_index という名前の全文インデックスを、特に title および content カラムに追加します。最後に、SHOW INDEXES コマンドを使用してインデックス作成を確認し、Index_type が FULLTEXT であることを確認します。このセットアップにより、MySQL データベース内での効率的なテキスト検索の準備が整います。
この実験 (Lab) を通して、MySQL シェル内で作業します。MySQL シェルには最初に一度だけ入り、最後に終了します。以降のステップのすべての SQL コマンドは、同じ MySQL セッション内で実行する必要があります。
このステップでは、記事を保存するための MySQL データベースとテーブルを作成します。このテーブルは、全文検索機能を示すために使用されます。
まず、ターミナルを開き、root ユーザーとして MySQL サーバーに接続します。以下のコマンドを使用できます。
sudo mysql -u rootこのコマンドは、root ユーザーを使用して MySQL サーバーに接続します。sudo を使用しているため、パスワードの入力を求められることはありません。
MySQL シェルに接続すると、mysql> プロンプトが表示されます。実験の終了まで、以降のすべてのステップで MySQL シェル内に留まってください。
次に、search_db という名前のデータベースを作成します。このデータベースには、articles テーブルが格納されます。以下の SQL コマンドを実行します。
CREATE DATABASE IF NOT EXISTS search_db;IF NOT EXISTS 句は、データベースがまだ存在しない場合にのみ作成されることを保証します。
次に、USE コマンドを使用して、新しく作成されたデータベースに切り替えます。
USE search_db;データベースが変更されたことを示すメッセージが表示されるはずです。
次に、articles テーブルを作成します。このテーブルには、id、title、content の 3 つのカラムがあります。
CREATE TABLE articles (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
content TEXT
);このコマンドの内訳を見てみましょう。
CREATE TABLE articles: このステートメントは、articles という名前の新しいテーブルを作成します。id INT AUTO_INCREMENT PRIMARY KEY: これは、各新しい行に対して自動的にインクリメントされ、テーブルの主キーとして機能する id という名前の整数カラムを定義します。title VARCHAR(255) NOT NULL: これは、最大長 255 文字の title という名前の文字列カラムを定義します。NOT NULL は、このカラムが空にできないことを意味します。content TEXT: これは、より大きな文字列を格納できる content という名前のテキストカラムを定義します。このコマンドを実行すると、search_db データベース内に articles テーブルが作成されます。
現在のデータベース内のテーブルを一覧表示することで、テーブルの作成を確認できます。
SHOW TABLES;出力に articles が表示されるはずです。
+-----------------------+
| Tables_in_search_db |
+-----------------------+
| articles |
+-----------------------+
1 row in set (0.00 sec)データベースとテーブルを作成したので、次のステップでサンプルデータでそれを投入する準備ができました。
このステップでは、articles テーブルにサンプルデータを挿入し、効率的なテキスト検索を可能にするために全文インデックスを追加します。
MySQL シェル内にいて、search_db データベースを使用していることを確認してください。そうでない場合は、データベースを選択します。
USE search_db;次に、articles テーブルにサンプルデータを挿入しましょう。異なるタイトルとコンテンツを持つ 3 つの行を追加します。
INSERT INTO articles (title, content) VALUES
('MySQL Full-Text Search', 'This article explains how to use full-text search in MySQL.'),
('Indexing in MySQL', 'Learn about different types of indexes in MySQL, including full-text indexes.'),
('Optimizing MySQL Queries', 'Tips and tricks for optimizing your MySQL queries for better performance.');この INSERT ステートメントは、articles テーブルに複数の行を追加します。VALUES の後の各括弧のペアは新しい行を表し、値は title および content カラムに対応します。
テーブルからすべての行を選択することで、データが挿入されたことを確認できます。
SELECT * FROM articles;挿入された 3 行が出力に表示されるはずです。
+----+--------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+--------------------------+---------------------------------------------------------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. |
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)テーブルにデータが追加されたので、全文インデックスを追加しましょう。全文インデックスを使用すると、MySQL はテキストデータに対して高速で関連性の高い検索を実行できます。title および content の両方のカラムに全文インデックスを追加します。
ALTER TABLE ステートメントを使用してインデックスを追加します。
ALTER TABLE articles ADD FULLTEXT INDEX article_index (title, content);このコマンドの内訳を見てみましょう。
ALTER TABLE articles: これは、articles テーブルを変更することを示します。ADD FULLTEXT INDEX article_index: これは、FULLTEXT 型の新しいインデックスを追加し、article_index という名前を付けます。(title, content): これは、全文インデックスに含まれるカラムを指定します。このコマンドを実行すると、MySQL は指定されたカラムに全文インデックスを構築します。
全文インデックスが正常に作成されたことを確認するには、SHOW INDEXES コマンドを使用できます。
SHOW INDEXES FROM articles;出力には、articles テーブルのすべてのインデックスが表示されます。Index_type カラムに FULLTEXT が表示される article_index のエントリが表示されるはずです。
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| articles | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | YES | NULL |
| articles | 1 | article_index | 1 | title | NULL | 3 | NULL | NULL | YES | FULLTEXT | | | YES | NULL |
| articles | 1 | article_index | 2 | content | NULL | 3 | NULL | NULL | YES | FULLTEXT | | | YES | NULL |
+----------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.00 sec)これで、テーブルにデータが正常に挿入され、全文インデックスが追加されました。次のステップでは、このインデックスを使用して全文検索を実行する方法を学びます。
このステップでは、MATCH AGAINST 句を使用して articles テーブルに対して基本的な全文検索を実行します。これは、作成した全文インデックスを活用するための基本的な方法です。
MySQL シェル内にいて、search_db データベースを使用していることを確認してください。そうでない場合は、データベースを選択します。
USE search_db;MATCH AGAINST 句は、SELECT ステートメントの WHERE 句内で使用されます。基本的な構文は次のとおりです。
SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term');ここで、column_list は全文インデックスに含まれるカラムのカンマ区切りリスト（この場合は title と content）であり、'search_term' は検索したい単語またはフレーズです。
"MySQL" という単語を含む記事を検索してみましょう。
SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('MySQL');このクエリは、title または content カラムが "MySQL" という用語と一致する articles テーブルから id、title、content カラムを選択します。
以下の出力が表示されるはずです。
+----+--------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+--------------------------+---------------------------------------------------------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. |
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)すべての記事に "MySQL" という単語が含まれているため、3 つの記事すべてが返されます。
全文検索は、各行が検索用語にどれだけ一致するかを示す関連性スコアも提供します。このスコアを SELECT ステートメントに含めることができます。
SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles;このクエリは、各行のスコアを示す relevance という名前のカラムを追加します。
+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title | content | relevance |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. | 1.34832763671875 |
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)結果を関連性で並べ替えることで、最も一致するものを最初に表示できます。
SELECT id, title, content, MATCH (title, content) AGAINST ('MySQL') AS relevance FROM articles ORDER BY relevance DESC;このクエリは、relevance スコアに基づいて結果を降順に並べ替えます。
+----+--------------------------+---------------------------------------------------------------------+--------------------+
| id | title | content | relevance |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. | 1.34832763671875 |
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. | 0.5215404033660889 |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.5215404033660889 |
+----+--------------------------+---------------------------------------------------------------------+--------------------+
3 rows in set (0.00 sec)このステップでは、MATCH AGAINST を使用して基本的な全文検索を実行する方法と、関連性に基づいて結果を取得および並べ替える方法を学びました。次のステップでは、ブールモードを使用したより高度な検索オプションを調べます。
このステップでは、MATCH AGAINST 句のブールモードを使用して、より正確で柔軟な全文検索を実行します。ブールモードを使用すると、演算子を使用して、結果に含める必要がある単語や除外する必要がある単語を制御できます。
MySQL シェル内にいて、search_db データベースを使用していることを確認してください。そうでない場合は、データベースを選択します。
USE search_db;ブールモードを使用するには、MATCH AGAINST ステートメントに IN BOOLEAN MODE 句を追加します。構文は次のとおりです。
SELECT column1, column2, ...
FROM table_name
WHERE MATCH (column_list) AGAINST ('search_term' IN BOOLEAN MODE);以下に、ブールモードで使用される一般的な演算子をいくつか示します。
+: 結果に単語が存在することを要求します。-: 単語を含む行を除外します。"": 完全なフレーズを検索します。*: ワイルドカード演算子（単語の末尾）。"MySQL" という単語を 必ず含み、"optimizing" という単語を 含まない 記事を見つけましょう。
SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL -optimizing' IN BOOLEAN MODE);このクエリは、+ 演算子を使用して "MySQL" を要求し、- 演算子を使用して "optimizing" を除外します。
以下の出力が表示されるはずです。
+----+------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+------------------------+---------------------------------------------------------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. |
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)"Optimizing MySQL Queries" に関する記事は "optimizing" を含んでいるため除外されます。
次に、"full-text search" という正確なフレーズを検索してみましょう。
SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('"full-text search"' IN BOOLEAN MODE);二重引用符を使用すると、単語を連続したフレーズとして検索できます。
出力は次のようになります。
+----+------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+------------------------+---------------------------------------------------------------------+
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)最初の記事のみが返されます。これは、"full-text search" という正確なフレーズが含まれているためです。
ワイルドカード演算子 * を使用してみましょう。"index" で始まる単語を含む記事を見つけたいとします。
SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('index*' IN BOOLEAN MODE);index* という用語は、"index" や "indexing" のような単語に一致します。
出力は次のようになります。
+----+------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+------------------------+---------------------------------------------------------------------+
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. |
+----+------------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)このステップでは、MATCH AGAINST 句とさまざまな演算子を使用してブールモードを使用し、より制御された具体的な全文検索を実行する方法を学びました。次のステップでは、これらのテクニックを組み合わせる練習をします。
このステップでは、基本的な MATCH AGAINST 検索とブールモード演算子を組み合わせて、より複雑で洗練された全文クエリを実行します。
MySQL シェル内にいて、search_db データベースを使用していることを確認してください。そうでない場合は、データベースを選択します。
USE search_db;"MySQL" という単語を含み、かつ "indexing" または "optimizing" のいずれかの単語を含む記事を検索してみましょう。ブールモードでは、括弧を使用してオプションの用語をグループ化できます。
SELECT id, title, content FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL +(indexing optimizing)' IN BOOLEAN MODE);このクエリは "MySQL" (+MySQL) を必須とし、括弧内の用語の少なくとも 1 つ (+(indexing optimizing)) を必須とします。
出力は次のようになります。
+----+--------------------------+---------------------------------------------------------------------+
| id | title | content |
+----+--------------------------+---------------------------------------------------------------------+
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. |
+----+--------------------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)最初の記事は "MySQL" を含んでいますが "indexing" も "optimizing" も含まないため除外されます。
ブールモードと関連性スコアリングを組み合わせることもできます。> 演算子を使用して "MySQL" を含み、さらに "indexing" について言及している記事の関連性を優先して検索してみましょう。
SELECT id, title, content, MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH (title, content) AGAINST ('+MySQL >indexing' IN BOOLEAN MODE) ORDER BY relevance DESC;このクエリは "MySQL" を必須とし、"indexing" を含む記事の関連性スコアをブーストします。その後、計算された関連性で結果が並べ替えられます。
出力は次のようになります。
+----+--------------------------+---------------------------------------------------------------------+-----------+
| id | title | content | relevance |
+----+--------------------------+---------------------------------------------------------------------+-----------+
| 2 | Indexing in MySQL | Learn about different types of indexes in MySQL, including full-text indexes. | 1.6931 |
| 1 | MySQL Full-Text Search | This article explains how to use full-text search in MySQL. | 0.3068 |
| 3 | Optimizing MySQL Queries | Tips and tricks for optimizing your MySQL queries for better performance. | 0.3068 |
+----+--------------------------+---------------------------------------------------------------------+-----------+
3 rows in set (0.00 sec)>indexing 演算子により、"Indexing in MySQL" の記事が最も高くランク付けされていることに注意してください。
基本的な検索をブール演算子と関連性スコアリングと組み合わせることで、特定のニーズに合わせて強力で柔軟な全文検索クエリを作成できます。
これで、MySQL の全文検索機能に関するラボは終了です。MySQL シェルを終了するには、次のように入力します。
exit全文インデックスを持つテーブルの設定方法、基本的な検索の実行方法、およびより高度なクエリのためのブールモードの使用方法を学びました。
この実験では、MySQL の全文検索機能について学びました。まず、search_db という名前のデータベースと、テキストデータを格納するための articles テーブルを作成しました。次に、サンプル記事でテーブルにデータを投入しました。
重要なステップは、ALTER TABLE ステートメントを使用して、articles テーブルの title および content カラムに article_index という名前の全文インデックスを追加することでした。このインデックスは、効率的な全文検索に不可欠です。SHOW INDEXES を使用してインデックス作成を確認しました。
最後に、MATCH AGAINST 句を使用して全文検索を実行する方法を探りました。基本的な検索の実行方法、関連性スコアの取得方法、および関連性による結果の並べ替え方法を学びました。また、ブールモードについても掘り下げ、+、-、""、* などの演算子を使用して、より正確で複雑な検索クエリを作成しました。これらのテクニックを組み合わせることで、MySQL データベースに格納されているテキストデータから関連情報を効果的に検索および取得できます。
