How Can I Efficiently Query Multiple LIKE Patterns in MySQL?

Efficiently query multiple LIKE patterns

When data needs to match any one of multiple LIKE patterns, the question arises: Does MySQL provide an alternative to reusing OR conditions?

Traditional method using OR condition

Traditionally, a query looks like:

SELECT *
FROM fiberbox f
WHERE
  f.fiberBox LIKE '%1740 %' OR f.fiberBox LIKE '%1938 %' OR f.fiberBox LIKE '%1940 %';

Explore alternatives

The goal is to find a cleaner and more efficient method, similar to the hypothetical LIKE IN(). Although there is no direct LIKE IN() construct, there are other options.

Use regular expressions for efficient pattern matching

One way to replace multiple OR conditions is to use regular expressions (regexp):

SELECT *
FROM fiberbox f
WHERE
  f.field REGEXP '1740|1938|1940';

The regular expression in this query matches any value in the field column that contains the pattern '1740', '1938', or '1940'. This approach combines all patterns into a single statement, simplifying queries and potentially improving performance, although this may depend on the circumstances.

The above is the detailed content of How Can I Efficiently Query Multiple LIKE Patterns in MySQL?. For more information, please follow other related articles on the PHP Chinese website!