How to Efficiently Use MySQL's LIKE Operator for Multiple Values?

MySQL LIKE Query for Multiple Values

This article addresses a common issue faced when using the LIKE operator in MySQL queries to search for multiple values. The goal is to find rows where a particular field contains at least one of the specified values.

Query and Sample Data

Consider the following MySQL query:

SELECT * FROM table WHERE interests LIKE ('%sports%', '%pub%')

With the following sample data in the "interests" field:

sports,shopping,pool,pc,games
shopping,pool,pc,games
sports,pub,swimming, pool, pc, games

Issue Description

The above query does not work as expected. It fails to return any rows that contain either "sports" or "pub."

Solution: Using Boolean Operators

A simple solution is to use Boolean operators such as OR to specify conditions for multiple values:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

More Efficient Solution: Using REGEXP

MySQL provides a more efficient way to search for multiple values using the REGEXP operator:

WHERE interests REGEXP 'sports|pub'

This solution uses the REGEXP operator to specify a regular expression that matches rows where the "interests" field contains either "sports" or "pub."

Resources

For more information on REGEXP in MySQL, refer to the following resources:

• [MySQL REGEXP](http://www.tutorialspoint.com/mysql/mysql-regexps.htm)
• [MySQL Forums: LIKE multiple values](http://forums.mysql.com/read.php?10,392332,392950#msg-392950)

The above is the detailed content of How to Efficiently Use MySQL's LIKE Operator for Multiple Values?. For more information, please follow other related articles on the PHP Chinese website!