MySQL likeのサンプルコード集 複数のパターンマッチングをするには?
  • facebookページ
  • twitterページ
  • 2019.11.18

    MySQL likeのサンプルコード集 複数のパターンマッチングをするには?

    MySQLのlikeの使い方について、サンプルSQLを紹介しながらまとめています。

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

    MySQLのlike構文

    WHERE句でワイルドカード文字を使って、マッチングをおこなう構文です。

    「_」(アンダースコア)は任意の一文字にマッチ、「%」は0文字以上の文字にマッチします。

    上記のSQLは、employees(社員)テーブルから、first_name(姓名の名)の2文字目が「or」のデータを抽出します。(1文字目が任意、2~3文字目が「or」、3文字目以降が任意)

    実行結果はこうなります。

    Moriyoshi、Morrie、Boriana、Sorinaなどがマッチしています。

    【関連記事】
    sql likeを使ったサンプル集 likeは本当に遅いのか?

    MySQLのlikeの使い方

    数値式でlikeが使える

    MySQLでは、int型のカラムに対して、文字列のような扱いでlikeによるマッチングが可能です。

    上記SQLは、smployees(社員)テーブルから、emp_no(社員番号、int型)を文字列として見た時に’_123%’にマッチするデータを取得します。これは、SQL標準ではなく、MySQLの拡張仕様です。

    実行するとこうなります。

    11230、11231、21230などがマッチしています。

    同様にdate型のカラムに対しても、likeが使用可能です。

    実行するとこうなります。

    birth_date(誕生日)に「-12-」が含まれる、12月生まれのデータのみ抽出できました。

    カラム指定でlikeが使える

    出力カラムリスト(FROMの左側)で、likeが使えます。

    実行すると、「’George’ like ‘G%’」は「1」と出力されます。

    カラムリストでlikeが使えるのは、MySQLの独自仕様です。

    末尾に空白があるとlikeでマッチしないケースあり

    WHERE句で、=(イコール)を使って条件を指定する場合、文字列の末尾に空白が入っていてもマッチします。

    ※上記は、employees(社員)テーブルから、first_name(姓名の名)が’George ‘(末尾に半角スペース)にマッチする行を取得するSQLです。

    実行結果はこうなります。

    もっと極端に、条件のGeorgeの後ろに大量の半角スペースを入れても、結果は同じです。

    しかし、likeを使って同様のSQLを実行すると、マッチしません。

    実行すると「 返り値が空でした (行数 0)。 (Query took 0.0965 seconds.)」と出力されます。

    NULLにnot likeはマッチしない

    likeでのNULLの扱いには、少し注意が必要です。例えば、以下のSQLは、information_schema.tablesのENGINEカラムがNULLのものを取得します。

    実行結果はこうなります。

    ENGINEの項目がNULLになるVIEWがマッチングしました。

    しかし、not like ‘%’には、NULLはマッチングしません。

    上記のSQLだと、「返り値が空でした (行数 0)。 (Query took 0.0106 seconds.)」となります。

    「’%aaa%’にマッチしない、またはNULLのデータ」を取得したい場合は、以下のように記述します。

    複数のマッチングには、正規表現が使えるrlike

    likeのワイルドカードには、_(アンダースコア)と%(パーセント)記号が使えます。MySQLには、正規表現でマッチングができるrlikeが用意されています。

    rlikeは、REGEXPのシノニムです。

    参考)MySQL :: MySQL 5.6 リファレンスマニュアル :: 12.5.2 正規表現

    正規表現による、複雑なマッチングをおこなう場合はrlike(REGEXP)を使うと良いでしょう。

    上記のSQLは、employees(社員)テーブルから、first_name(姓名の名)が、Gで始まりeで終わる、または、Mで始まりoで終わるデータを取得します。

    実行すると、こうなります。

    likeは、前方一致以外はインデックスを使えないので遅い

    likeは、前方一致(’AAA%’など)のマッチングはインデックスを利用できるため速いのですが、それ以外のマッチング(’%AAA’や’%AAA%’など)は、テーブルフルスキャンになるため遅いです。

    employees(社員)テーブルの、first_nameにインデックスを設定して試してみます。

    上記は前方一致のマッチングをおこなうSQLです。

    explainで実行計画を確認すると、インデックス(firstname_index)が使われています。

    実行すると「1657 total, Query took 0.0004 seconds.」

    上記は後方一致のマッチングでexplainすると、テーブルフルスキャンが実行されてます。

    実行すると「509 total, Query took 0.0055 seconds.」となります。

    単純にクエリのレスポンスが返ってくるまで、13倍以上の時間がかかっています。※employeesテーブルの件数は30万件。

    まとめ

    ポテパンダの一言メモ
    • MySQLは数値(int型)や日付(date型)にlikeでマッチングが可能
    • 出力カラムリストでもlikeが使える
    • 末尾に空白があると、マッチしないケースがある
    • not like指定では、NULLはマッチングしない
    • 正規表現は、rlike(REGEXP)を使う


    優良フリーランス案件多数掲載中!
    フリーランスエンジニアの案件をお探しなら
    ポテパンフリーランス

    この記事をシェア

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









    ABOUT US

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

    READ MORE