How Can I Efficiently Perform Multiple String Searches in MySQL?

Use MySQL find_in_set() function for multi-string search

MySQL’s find_in_set() function can only search a single string at a time. However, in practical applications, it is often necessary to search multiple strings at the same time.

For example:

<code class="language-sql">find_in_set('a', 'a,b,c,d')</code>

Here, 'a' is the only search string. If you want to search for multiple strings, such as 'a,b,c', you can use the following method:

<code class="language-sql">find_in_set('a', 'a,b,c,d') OR find_in_set('b', 'a,b,c,d') OR find_in_set('c', 'a,b,c,d')</code>

This method uses the OR operator to combine multiple find_in_set() calls. However, a more efficient solution exists:

<code class="language-sql">WHERE CONCAT(",", `setcolumn`, ",") REGEXP ",(val1|val2|val3),"</code>

This method adds commas at the beginning and end of the 'setcolumn' field for regular expression pattern matching. Among them:

• The values ​​in 'setcolumn' are represented as ",a,b,c,d,".
• The regular expression ",(val1|val2|val3)," matches the substring "a", "b" or "c" in the string ',a,b,c,d,'.

This trick effectively searches for rows whose 'setcolumn' contains all the specified strings, providing a more flexible and efficient alternative to multiple find_in_set() calls.

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