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:
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.