導入
最新のアプリケーションでは、特に大規模なデータベースを扱う場合、効率的なテキスト検索が非常に重要です。 MySQL は基本的な全文検索機能を備えていますが、あいまい一致やスペルミスの処理に関しては不十分です。ここでトライグラムベースの検索が登場します。このブログでは、トライグラムとは何か、トライグラムによって検索パフォーマンスがどのように向上するか、MySQL でトライグラム検索を実装する方法について説明します。

トリグラムとは何ですか?
トライグラムは、指定された文字列からの 3 つの連続する文字のシーケンスです。たとえば、「検索」という単語は次のトリグラムに分解できます:

• 海
• 耳
• アーク
• アーチ 単語をトリグラムに分割することで、特に不完全な用語やわずかにスペルミスのある用語を照合しようとする場合に、より柔軟かつ効率的なテキスト照合を実行できます。

一時テーブルを作成して MySQL にトライグラム検索を実装する

1.MySQLデータベースにTrigram関数を作成します。トリグラム機能コード:

CREATE FUNCTION TRIGRAM_SEARCH(search_string VARCHAR(255), target_string VARCHAR(255)) 
RETURNS FLOAT
DETERMINISTIC
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE total_trigrams INT DEFAULT 0;
    DECLARE matched_trigrams INT DEFAULT 0;
    DECLARE search_length INT;
    DECLARE target_length INT;

    SET search_length = CHAR_LENGTH(search_string);
    SET target_length = CHAR_LENGTH(target_string);

    -- Handle edge cases where strings are too short
    IF search_length  0 THEN
        RETURN matched_trigrams / total_trigrams;
    ELSE
        RETURN 0;
    END IF;
END;

2.現在、必要な列をフルテキストにインデックス付けしています

@Entity()
@Index(['title'], { fulltext: true })
export class Ebook extends BaseEntity {
  @PrimaryGeneratedColumn()
  ebookId: number;

  @Column({ nullable: true })
  title: string;
}

3.トライグラム検索機能のテスト

select *
FROM ebook e 
WHERE TRIGRAM_SEARCH('physis onlu', e.title) > 0.4
ORDER BY TRIGRAM_SEARCH('physis onlu', e.title) desc;

4.コード内にトライグラム検索を実装

 async find(title?: string) {
        const eBooks = await this.dataSource
      .getRepository(Ebook)
      .createQueryBuilder('eBook');
    if (title) {
      eBooks.where(`TRIGRAM_SEARCH(:title, eBook.title) > 0.4`, { title });
    }
    const result = await eBooks.getMany();
    return result;
  }

結論
Trigram 検索は、MySQL データベースでファジー マッチングを実装する強力な方法を提供します。テキストをトリグラムに分割することで、より柔軟で寛容な検索を実行できるようになり、テキスト検索が重要なアプリケーションでのユーザー エクスペリエンスが大幅に向上します。
このアプローチには長所もありますが、特定のユースケースやパフォーマンス要件に応じて、レーベンシュタイン距離や Soundex アルゴリズムなどの代替案を検討することが重要です。
trigram 検索を実装すると、Node.js および MySQL アプリケーションの検索機能が大幅に向上し、よりインテリジェントで寛容な検索結果をユーザーに提供できます。