sql likeを使ったサンプル集 likeは本当に遅いのか?
  • facebookページ
  • twitterページ
  • 2019.08.18

    sql likeを使ったサンプル集 likeは本当に遅いのか?

    SQLのlikeの使い方をサンプルつきでまとめました。

    なお、MySQLのサンプルデータベースEmployeesを、SQL実行結果の表示にはphpMyAdminを使って解説しています。

    likeを使ったワイルドカード指定の例

    まずは、likeを使ったシンプルな例。社員情報が格納されている、employeesテーブルをワイルドカード指定で抽出してみました。

    first_nameがAから始まる社員を抽出してみます。

    結果はこうなります。

    first_nameがAで始まるデータが抽出されました。

    パターンマッチに使われる文字

    パターンマッチに使われるワイルドカードは、以下の2つ。

    • % 0文字以上の任意の文字列にマッチ
    • _ 任意の一文字にマッチ

    ワイルドカード文字のエスケープ

    「%」や「_」そのものを比較する場合は、バックスラッシュでエスケープする必要があります。

    例) like ‘100\%’

    アプリケーションなどでユーザが入力したキーワードで、SQLを構成する場合「%」や「_」に大してエスケープ処理(サニタイズ)をおこなうべきでしょう。思いがけず、データベースに負荷をかけるクエリが発行されてしまう可能性があります。

    なお、エスケープ文字を変えたい場合は、以下のようにします。

    エスケープ文字がデフォルトの「\」(バックスラッシュ)から、#に変更されます。

    ワイルドカードの使用例

    • ’A%’ 前方一致。Aで始まる文字列にマッチします。
    • ’%A’ 後方一致。Aで終わる文字列にマッチします。
    • ’%A%’ 中間一致。文字の最初、または途中、またさ最後にAが含まれる文字列にマッチします。
    • ‘_%’ 2文字以上の文字列にマッチします。(アンダースコア2個に%1個)
    • ‘__%’ 2文字以上の文字列にマッチします。(アンダースコア2個に%1個)

    例えば、’A__ka%u’は以下の文字列にマッチします。

    • A12kahu
    • AAAkau
    • Akakaaaaau

    not like (マッチした文字列を含まない)の例

    first_nameがAで始まり、かつ、last_nameがSで始まらない社員のみ抽出できました。

    大文字・小文字を区別する例

    通常のlikeでは、大文字と小文字を区別しません。

    ‘%geo’ が、 Geoにもマッチしてしまいます。

    大文字と小文字を区別するには、like binaryを使います。

    小文字のgeoにのみマッチするようになりました。

    SQL文のlikeの遅さを計測

    SQL内で、likeを多用するとスピードが遅くなると言われます。どの程度遅くなるのか、計測してみました。

    where句でワイルドカードを使わないlikeは、「=」と、意味は全く同じになります。

    • where first_name = ‘George’
    • where first_name like ‘George’

    結果はこうなりました。

    Showing rows 0 – 24 (185 total, Query took 0.0088 seconds.)

    約30万件あるemployeesを結合して、以下の条件をつけた場合、0.0088秒かかったことになります。

    以下の条件を指定すると、

    Showing rows 0 – 24 (185 total, Query took 0.0091 seconds.)

    結果は全く同じで、0.0091秒かかりました。

    ポテパンダの一言メモ

    where句に=(イコール)を使用した場合と比べて、likeを使うと1.03倍時間がかかる。

    データベースやチューニング内容によって結果は変わってくるかも知れませんが、今回使ったMySQL Community Server 5.7では、大きな差は確認できませんでした。

    likeで正規表現は使えない

    likeで使えるのは、「%」と[_」(アンダースコア)を使った、簡易的なワイルドカード表現のみです。

    しかし、各データベースでは独自に機能を拡張して正規表現を使えるようになっています。

    MySQLの場合は、REGEXP演算子を使って正規表現が可能。

    OracleやMicrosoft SQLServerにも、正規表現に対応する拡張がされています。

    Oracleは、REGEXP_LIKE で正規表現が可能ですし、MicrosoftのSQLServerは、likeの文字列に[](大カッコ)を使うことで、簡易的な正規表現が使えるようになっています。

    参考)Oracle ドキュメント パターン一致条件

    参考)LIKE (Transact-SQL) – SQL Server | Microsoft Docs

    ポテパンダの一言メモ
    • likeで正規表現は使えない
    • ただし、各データベースの拡張で、正規表現機能が用意されている

    date型のカラムにlikeを使うには

    date型のカラムにlikeでワイルドカード指定をする場合、MySQLだとこのように記述します。

    結果はこうなります。hire_dateが1990で始まるデータが抽出できました。

    ただし、この記述方法はSQL標準ではありません。各データベースによって、記載方法がことなります。

    Oracleの場合は、TO_CHAR、Microsoft SQL Serverの場合は、Convertを使います。

    参考)Oracle TO_CHAR (文字)

    参考)CAST および CONVERT (Transact-SQL) – SQL Server | Microsoft Docs

    ちなみに、MySQLの場合は、DATE_FORMATを使わずに、

    と書いても、動作します。

    SQLのlike使い方まとめ

    ポテパンダの一言メモ
    • 指定内容によっては、大きく速度が落ちる可能性あり
    • 正規表現は、各データベースのSQL拡張。書式などが異なる
    • date型に対するワイルドカード指定は、char型に変換を行えば可能

    ポテパンが提供するサービスについて

    本メディア「ポテパンスタイル」を運営する株式会社ポテパンは、エンジニアキャリア領域で複数サービスを提供しています。

    ポテパンフリーランス

    ポテパンフリーランス

    フリーランスエンジニアの方に高単価案件をご紹介しております。弊社ではフリーランス案件を常時300件ほど保有しており、その中からあなたに適した案件をご案内いたします。また、これから独立してフリーランスになる方の無料個別相談も承っております。フリーランスになった後の案件獲得方法やお金面(税金や保険など)についてお答えいたします!フリーエンジニアになりたい方向けのコンテンツも盛りだくさんです。

    ポテパンキャリア

    ポテパンキャリア

    エンジニア職専門の転職エージェントです。ポテパンキャリアでは、技術のわかるエージェントがあなたの転職をサポートします。エージェント自身がエンジニアなので、あなたと同じ目線で仕事内容や今後のキャリアについて一緒に考えることができます。年収800万円以上のハイスペック転職をご希望の方は「ポテパンプロフェッショナル」もご用意しておりますのでご利用下さいませ。

    ポテパンキャンプ

    ポテパンキャンプ

    ポテパンキャンプでは、RubyにてゼロからオリジナルのECサイトを作り上げてる3ヶ月間の実践型カリキュラムを提供しております。すでに本スクールの卒業生は、エンジニア職として様々な企業様に就職しております。なお、本スクールは受講料25万円と他社スクールに比べ格安となっており、またポテパンからご紹介させていただいた企業へ就職が決まった場合は、全額キャッシュバックいたします。



    株式会社ポテパンは、企業とエンジニアの最適なマッチングを追求しています。気になるサービスがあれば、ぜひ覗いてみてください!

    ポテクラバナー ポテプロバナー

    この記事をシェア

    • Facebookシェア
    • Twitterシェア
    • Hatenaシェア
    • Lineシェア
    pickup









    ABOUT US

    ポテパンはエンジニアと企業の最適なマッチングを追求する企業です。

    READ MORE

    ポテパンおすすめ案件