In MySQL, REGEXP and RLIKE are functionally identical operators used for pattern matching with regular expressions. They both allow you to match strings against a regular expression pattern in a SELECT query.

Key Points About REGEXP and RLIKE

1
Synonyms: RLIKE is simply a synonym for REGEXP; using either will produce the same results.
2
Pattern Matching: Both operators support the full MySQL regular expression syntax, including anchors (^, $), character classes ([a-z]), quantifiers (*, +, {n,m}), alternation (|), and wildcards (.).
3
Case Sensitivity: By default, both are case-insensitive for non-binary strings. The BINARY keyword can make the match case-sensitive.
4
Usage Example: You can use column REGEXP 'pattern' or column RLIKE 'pattern' interchangeably in queries.

In practice, there is no functional difference, and the choice between REGEXP and RLIKE is mostly a matter of style or readability.

Example Using REGEXP

Example Using RLIKE

Difficulty: 5/10

Topics: pattern matching, SQL syntax, performance

Scenario Questions

0-2 years experience

1
You need to retrieve rows where the email column ends with '.org'. How would you write the WHERE clause using REGEXP, and would using RLIKE change anything?
2
If a query with WHERE name RLIKE '^J.*' returns no rows, what might be wrong with the pattern or its usage?

2-5 years experience

1
Your team added a REGEXP filter to a high‑traffic search query and observed a noticeable slowdown. Walk me through how you would diagnose the issue and decide whether to keep REGEXP or switch to RLIKE.
2
During a migration to MySQL 8.0, a stored procedure that used RLIKE started failing with a syntax error. Explain why this happened and how you would resolve it.

5-8 years experience

1
We run nightly aggregations on a 500 GB table using complex REGEXP patterns. What architectural changes would you consider to reduce impact, and does choosing REGEXP versus RLIKE affect those choices?
2
Design a plan to replace all REGEXP usage in a legacy codebase with RLIKE, ensuring matching semantics stay the same and downtime is minimal.

8+ years experience

1
Our platform wants to deprecate one of REGEXP or RLIKE across services. How would you argue for the decision, considering readability, compatibility, and optimizer behavior?
2
When migrating from MySQL to a distributed SQL engine that only supports POSIX regex, what concerns would you raise about existing REGEXP/RLIKE usage and how would you mitigate regression risks?

Follow-up Questions

❓Can you give an example where the two operators would produce different results?
❓How does MySQL's optimizer treat REGEXP versus RLIKE in execution plans?
❓What limitations exist when using these operators on indexed columns?