Skip to content

REGEXP is Case Sensitive?

According to MySQL documentation: MySQL :: MySQL 8.0 Reference Manual :: 12.8.2 Regular Expressions.

match_type may be specified with the c or i characters to override the default case sensitivity. Exception: If either argument is a binary string, the arguments are handled in case-sensitive fashion as binary strings, even if match_type contains the i character.

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 *