Zenn
🐬

RDBでfirst_name,last_nameを様々な区切り文字で柔軟に検索する

2025/03/25に公開
MySQL
Rails
DB
RDB
tech

概要

リレーショナルデータベース(MySQL, PostgreSQL, etc...)のusersテーブルなどでfirst_name, last_nameカラムを様々な区切り文字で柔軟に検索する手法を示します。

今回、応えるのは以下のようなニーズです。

  • first_name, last_nameが結合されている
  • first_name, last_nameが" "区切りで結合されている
  • first_name, last_nameが"."区切りで結合されている

前提

  • この記事ではサンプルクエリをMySQLで示します
  • この記事ではサンプルコードをRuby on Railsで示します
  • この記事で前提とするテーブルは以下の構成です
mysql
CREATE TABLE users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    last_name VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

実現方法

検索用のカラムを追加する

以下のsearch_textというカラムを追加します。

mysql
ALTER TABLE users
ADD COLUMN search_text TEXT NOT NULL;

検索用のカラムを更新する

first_name, last_name が更新されたときにsearch_textも更新する

ここでは簡易的にRuby on Railsのbefore_saveで実現しています。

app/models/user.rb
class User < ApplicationRecord
  before_save :set_search_text

  private

  def set_search_text
    name_raw   = "#{first_name}#{last_name}".downcase
    name_spaced = "#{first_name} #{last_name}".downcase

    self.search_text = [name_raw, name_spaced].join(' ')
  end
end

downcaseで保存しているのは、このカラムを用いて検索する際に大文字・小文字を区別せず、小文字のみで検索すれば取得できるようにするため

この例では、first_name, last_nameを結合した文字列、first_name, last_nameを半角スペース区切りで結合した文字列で検索できるようにしています。
さらにドット区切りも加えたり、first_name, last_nameに限らずemailカラムなど他のカラムを追加することもできます。

検索用のカラムで検索する

ruby
User.where("search_text LIKE ?", "%#{query.downcase}%")

downcaseで保存しているので、検索に用いる語句もdowncaseで小文字にする。

完了

上記の手順で完了です。

全文検索エンジンを用いずにfirst_name,last_nameを柔軟に検索できるようになりました。

備考

カラムを追加せずに検索クエリで区切り文字などを調整するのはどうか？

こんな感じです。

SELECT *
FROM users
WHERE
  LOWER(CONCAT(first_name, last_name)) LIKE '%johndoe%'
  OR LOWER(CONCAT(first_name, ' ', last_name)) LIKE '%john doe%'
  OR LOWER(CONCAT(first_name, '.', last_name)) LIKE '%john.doe%'
  OR LOWER(CONCAT(last_name, first_name)) LIKE '%johndoe%'
  OR LOWER(CONCAT(last_name, ' ', first_name)) LIKE '%john doe%'
  OR LOWER(CONCAT(last_name, '.', first_name)) LIKE '%john.doe%';

パフォーマンスが良くないので、おすすめしません。

Elasticsearch, Algoliaなどの全文検索エンジンを使うのはどうか？

選択肢にはあるが、それを使うと、ORMで取得できない。
searchkickなどのGemを使うと取得できるが、実装時にデータストアの違いを意識する必要がある。

仮想カラムを使うのはどうか？

仮想カラムを使うと、整合性を担保できる点は優れている。
しかし、search_textにセットする値やケースを追加するたびにDBマイグレーションが必要となる。
また、search_textにセットする値を加工する（区切り文字を変えるなど）処理をSQLで書く必要がある。複雑であるほど、アプリケーションで書ける方がメンテナンスしやすい。

FULLTEXT INDEXを使うのはどうか？

速度、曖昧検索で優れているのでデータ件数や要件次第では積極的に取り入れたい。
そこまでデータ件数が多くなく、速度的に問題がないなら、この記事で示したLIKE検索で十分。書き方が単純で扱いやすいため
速度が求められたり、曖昧検索などを使いたいなら、FULLTEXT INDEXを使う。

ユーザー、管理者の両方で同カラムを使いたい場合

search_text_for_user, search_text_for_adminといった具合にカラムを分けます。
search_text_for_userには他のユーザーにも公開されている情報のみを格納します。
search_text_for_adminにはemailなどの他のユーザーに公開されていない情報も格納します。

他のユーザーに公開されていない情報での検索をユーザーに許してしまうと、情報漏洩のリスクが生じるので注意しましょう。

まとめ

様々な選択肢があります。
要件次第、技術構成次第で適切に判断しましょう。

（この記事のサンプルコードは生成AIによって生成しており、動作確認を行っていません。ご了承ください。）

Discussion