Skip to content

Is REGEXP (or RLIKE) Always Case Insensitive?

According to MySQL documentation:
MySQL :: MySQL 5.7 Reference Manual :: 12.5.2 Regular Expressions
REGEXP is not case sensitive, except when used with binary strings.
It seems to be very sure about that REGEXP without any modifiers will select rows in case insensitive mode. Is it always true? Not really, I met a REGEXP statement in a trigger selected rows in case sensitive mode.

How did I find out the problem? Because I execute the same statement in command line. It returned a different result set from the one in that trigger.

Finally, I forced the statement to accept insensitive case by adding a COLLATE modifier:
collate utf8mb4_unicode_ci
after REGEXP clause to solve the problem. After that, the query result became correct. What I mean in this post is that REGEXP may not always be case insensitive, we should be aware of that.

Leave a Reply

Your email address will not be published. Required fields are marked *