[T-SQL] フルテキスト検索を行う

handcraft 4/26/2009 19085 4 SQL Server

SQL Server でフルテキスト検索を行うための、フルテキスト検索用のカタログ、インデックスを設定する方法や、FREETEXT, CONTAINS , FREETEXTTABLE, CONTAINSTABLE を使用したサンプルを掲載します。

動作確認は、Windows Server 2003 上にインストールした SQL Server 2005 Developer Edition で行っています。

1. フルテキストカタログ、フルテキストインデックスを作成、変更、削除する

フルテキスト検索を行うには、データベースのフルテキストカタログを作成し、カタログに対象テーブルのフルテキストインデックスを作成します。

サンプルでは、Componentというデータベースのdbo.ArticleテーブルのTitle列に対してインデックスを作成しています。カタログを作成するには、CREATE FULLTEXT CATALOG 文を使用します。インデックスを作成するには、 CREATE FULLTEXT INDEX 文を使用します。変更、削除する場合は、ALTER, DROP 文を使用します。　詳細のオプションいついては BOOKS ONLINE を参照してください。

USE Component
GO

-- フルテキストカタログを作成する
CREATE FULLTEXT CATALOG cat_Article

-- 既定のファイルパス以外の場所に
-- フルテキストカタログを作成する
-- "次回の BACKUP LOG 操作が終了するまで、ファイル 'sysft_cat_Article' を再利用できません。"
-- メッセージが表示される場合はトランザクションログをバックアップしてからクエリを実行して
-- ください。
-- CREATE FULLTEXT CATALOG cat_Article
-- IN PATH 'C:\work'

-- フルテキストカタログを変更する
-- 次のコマンドはカタログを再構成す
ALTER FULLTEXT CATALOG cat_Article
REORGANIZE

-- カタログを再構築する
ALTER FULLTEXT CATALOG cat_Article
REBUILD 

-- カタログを作成した後、
-- フルテキストインデックスを作成する
-- Articleテーブルの列Titleに対してインデックスを作成する
-- インデックスは自動更新、でキーにはプライマリキー制約を
-- 指定する
-- TYPE COLUMNはインデックスを作成する列がvarchar(max)
-- かimageの場合にSQL Server にデータタイプを認識
-- させるために使用します。(AdventureWorksデータベースの
-- Production.Document.FileExtentionカラム等)
CREATE FULLTEXT INDEX ON dbo.Article(Title)
  KEY INDEX PK_ArticleID
  ON cat_Article
  WITH CHANGE_TRACKING AUTO

-- フルテキストインデックスを修正する
-- フルテキストインデックスを無効にする
ALTER FULLTEXT INDEX ON dbo.Article
DISABLE

-- 有効にする
ALTER FULLTEXT INDEX ON dbo.Article
ENABLE

-- インデックスを再構築する
-- CHANGE_TRACKING AUTOで定義されていると
-- 構築は自動的行われているので、デモ用に
-- いったんトラッキングをオフにする
ALTER FULLTEXT INDEX ON dbo.Article
SET CHANGE_TRACKING OFF
-- インデックス構築
ALTER FULLTEXT INDEX ON dbo.Article
START FULL POPULATION

-- トラッキングを自動に戻す
ALTER FULLTEXT INDEX ON dbo.Article
SET CHANGE_TRACKING AUTO

-- フルテキストインデックスを削除する
DROP FULLTEXT INDEX ON dbo.Article


-- フルテキストカタログを削除する
DROP FULLTEXT CATALOG cat_Article

2. フルテキストカタログのメタデータを取得する

sys.fulltext_catalogs や sys.fulltext_indexes カタログビューを使用すると、フルテキスト検索用のカタログ、インデックスの情報を取得できます。FULLTEXTCATALOGPROPERTY関数を使用するとカタログのプロパティ値を取得できます。使用できるプロパティ値はAccentSensitivity, IndexSize, ItemCount , MergeStatus ,PopulateCompletionAge , PopulateStatus , UniqueKeyCount 等を指定できます。詳細はBooks Online をさんしょうして下さい。

-- フルテキストカタログのメタデータを取得
select *
 from sys.fulltext_catalogs

-- フルテキストカタログインデックスのメタデータを取得
select *
  from sys.fulltext_indexes

-- フルテキストインデックスカラム情報を取得
select object_name(object_id), *
  from sys.fulltext_index_columns

-- フルテキストカタログプロパティ関数でカタログのプロパティ値を取得
-- 指定可能なプロパティ名はBooks Online 参照
select FULLTEXTCATALOGPROPERTY('cat_Article', 'PopulateStatus')

3. フルテキスト検索を行う

フルテキストインデックスを使用した検索を行うには、FREETEXT, CONTAINS を使用します。厳密な検索を行う場合は、 CONTAINS を使用します。CONTAINSを使用すると、検索する語句に似ている文字やワイルドカード検索を行うことができます。指定できるすべてのオプションについてはBooks Online を参照してください。

-- 検索を行う
-- 文字ベースのデータ型値を格納している列を検索するときに使用するする
-- FREETEXT を使用するサンプルです.
-- CONTAINS を使用するフルテキスト クエリのほうが正確に検索を行えます。
SELECT ArticleID, Abstract, Title
  FROM dbo.Article
WHERE FREETEXT(Title, 'Crm')

-- CONTAINSは文字ベースのデータ型を含む列を検索するために使用します。
-- 単語または語句の完全一致検索やあいまい検索、特定の範囲内で近くにある単語の検索、
-- 重み付けした検索などを実行する場合に使用されます。 
SELECT ArticleID, Abstract
  from dbo.Article
WHERE CONTAINS(Title, '"LINQ" AND "ファンクション"')

-- ワイルドカード検索
-- Create,Crmなどが該当する
SELECT ArticleID, Abstract, TItle
  from dbo.Article
WHERE CONTAINS(Title, '"CR*"')

-- NEAR 等を使用すると近い単語の検索などを行えます。
-- 詳細はBOOKS Online 参照

FREETEXTTABLE やCONTAINSTABLEを使用するクエリでは、フリーテキストタイプのフルテキストクエリを指定して、各行の関連順位値 (RANK) とフルテキストキー (KEY) からなるテーブルを返します。返されたテーブルはSELECT の FROM 句などでテーブルのように使用できます。

KEYは、フルテキストインデックス作成時に指定したユニークな列です。サンプルではOBJECTPROPERTYEX 関数を使用して、KEYで指定される列を取得するクエリも掲載しています。

-- 関連順位値検索
-- FREETEXTTABLEは文字ベースのデータ型を含む列に対して、freetext_string
-- で指定したテキストの並びと正確に一致しなくても、意味が一致する値を検索し
-- 、0 行、1 行、またはそれ以上の行で構成されるテーブルを返します。
-- FREETEXTTABLE は、SELECT ステートメントの FROM 句の中で通常のテーブル名のように指定できます。

SELECT a2.[Key], a2.Rank, a1.Title
 FROM dbo.Article a1
INNER JOIN FREETEXTTABLE(dbo.Article, Title, 'CRM') a2
   ON a1.ArticleID = a2.[KEY]

-- 単語または語句の完全一致検索やあいまい検索、特定の範囲内で近くにある単語の検索、
-- および重み付き検索を行う場合、文字ベースのデータ型を含む列に対して、0 行以上の行を含む
-- テーブルを返します。
-- CONTAINSTABLE は、標準のテーブル名と同じように、SELECT ステートメントの FROM 句で参照できます。
SELECT a2.[Key], a2.Rank, a1.Title
 FROM dbo.Article a1
INNER JOIN CONTAINSTABLE(dbo.Article, Title, 'CRM') a2
   ON a1.ArticleID = a2.[KEY]

-- KEYがどの列なのかを表示します。ArticleIDが選択されます。
select name
 from sys.columns
 where OBJECT_ID('dbo.Article') = object_id
  and  OBJECTPROPERTYEX(OBJECT_ID('dbo.Article'),'TableFulltextKeyColumn') = column_id

4. まとめ

今回の説明は以上です。間違い、指摘点などがありましたら、ご連絡ください。

フルテキスト検索に関しては、指定できるオプションや検索方法が複数あるので、より詳細な情報は Books Online を参照していただければと思います。

// Net planet-es V2