MySQLのREGEXP_LIKEの使い方につまずく

やりたいこと

任意のカラムで以下のような条件に一致するレコード数で集計をしたい。

• 「鼠」「牛」「虎」いずれかを含むこと
• 「mouse」「MOUSE」いずれかを含むこと

REGEXP_LIKEは正規表現を使えて便利

-- 「or null」を忘れずにつけてcount()で集計する
select
    -- 条件を書くのが面倒くさい
    count(colomun like '%鼠%' or colomun like '%牛%' or colomun like '%虎%' or null) as '鼠/牛/虎いずれかを含む件数',
    -- 以下の書き方では大文字小文字が区別されない
    count(colomun like '%mouse%' or colomun like '%MOUSE%' or null) as 'mouse/MOUSEいずれか含む件数'
from table;

LIKE句では、条件を書くのが面倒くさい上に大文字小文字が区別されない。

MySQL のデフォルトでは、SQL パターンでは大文字と小文字が区別されません。
引用 : MySQL :: MySQL 8.0 リファレンスマニュアル :: 3.3.4.7 パターンマッチング

そんな時は、正規表現でLikeできる素敵な関数regexp_like()。

REGEXP_LIKE(expr, pat[, match_type])
文字列 expr がパターン pat で指定された正規表現と一致する場合は 1 を返し、一致しない場合は 0 を返します。 expr または pat が NULL の場合、戻り値は NULL です。
引用 : MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.8.2 正規表現

select
    count(regexp_like(colomun,'鼠|牛|虎') or null) as '鼠/牛/虎いずれかを含む件数',
    count(regexp_like(colomun,'mouse|MOUSE','c') or null) as 'mouse/MOUSEいずれか含む件数'
from table;

しかし、地味に使い方を間違える。

間違い① : 正規表現を間違えている

「Likeしたい！」が先行していると正規表現の書き方を間違えて、結果がおかしくなる。

-- 誤 : like句の書き方と正規表現がごちゃごちゃになっている書き方
regexp_like(colomun,'%鼠|牛|虎%')
-- 誤 : like句の書き方に引きずられて「.*」を「*」にしてしまった書き方
regexp_like(colomun,'*(鼠|牛|虎)*')
-- 正 : 検索できるが、一致させたい部分だけ書けばいいのでワイルドカードが無駄
regexp_like(colomun,'.*(鼠|牛|虎).*')
-- 正
regexp_like(colomun,'鼠|牛|虎')

Because a regular expression pattern matches if it occurs anywhere in the value, it is not necessary in the previous query to put a wildcard on either side of the pattern to get it to match the entire value as would be true with an SQL pattern.
引用 : MySQL :: MySQL 9.3 Reference Manual :: 5.3.4.7 Pattern Matching

間違い② : match_typeの指定を間違えている

• 「mouse」の大文字小文字を区別せず含むこと

ドキュメントをよく読んでおらず、match_typeを文字で指定することに気が付かなかった。

-- 誤 : 「Unknown column 'i' in 'where clause'」と怒られる
regexp_like(colomun,'mouse',i)
-- 正 : match_typeはちゃんと文字として書く
regexp_like(colomun,'mouse','i')

The optional match_type argument is a string that may contain any or all the following characters specifying how to perform matching:
引用 : MySQL :: MySQL 9.3 Reference Manual :: 14.8.2 Regular Expressions

間違い③ : やりたいことに合わないのに「大文字小文字」の指定をしてしまう

やりたいのは「mouse」の全部大文字と全部小文字を対象にすることにもかかわらず、
楽をしようとしてmatch_typeに「i」を指定して「mOuse」など余計なものまで対象にしてしまった。

オプションの match_type 引数は、照合の実行方法を指定する次の文字の一部またはすべてを含むことができる文字列です:
c: 大文字小文字を区別する照合。
i: 大文字小文字を区別しない照合。
引用 : MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.8.2 正規表現

-- 誤 : 「mouse」「MOUSE」だけではなく「MousE」など余計なものが対象になる
regexp_like(colomun,'mouse','i')
-- 正 : 「mouse」「MOUSE」だけ対象にする
regexp_like(colomun,'mouse|MOUSE','c')
-- 正 : binaryを使うこともできる
regexp_like(colomun,binary 'mouse|MOUSE')

regexp_like()のシノニムであるregexp演算子でも間違えてしまった。

-- 誤 : デフォルトは大文字小文字は区別しない
colomun regexp 'mouse'
-- 正 : 大文字小文字を区別する場合はbinaryが必要
colomun regexp binary 'mouse|MOUSE'

BINARY expr
BINARY 演算子は、式をバイナリ文字列 (binary 文字セットおよび binary 照合順序を持つ文字列) に変換します。 BINARY の一般的な用途は、文字単位ではなく数値バイト値を使用して、文字列をバイト単位で強制的に比較することです。
引用 : MySQL :: MySQL 8.0 リファレンスマニュアル :: 12.11 キャスト関数と演算子