How to Check for Specific Numbers in a Comma-Separated Blob in MySQL?

Checking for Numbers in a Comma-Separated Blob in MySQL

Consider the following table:

UID(int) | NUMBERS(blob)
----------------------
1        | 1,13,15,20
2        | 3,10,15,20
3        | 3,15

To check if specific numbers (e.g., 3 and 15) exist within the NUMBERS blob, we can utilize the IN operator. This operator tests whether a value is present in a comma-separated string.

Solution:

SELECT * FROM table WHERE 3 IN (NUMBERS) AND 15 IN (NUMBERS)

In this query, the IN operator is used twice:

• 3 IN (NUMBERS) checks if the number 3 is within the NUMBERS blob.
• 15 IN (NUMBERS) checks if the number 15 is within the NUMBERS blob.

The query returns rows only if both conditions are met. Therefore, only row 2 will be selected because it contains both 3 and 15 in its NUMBERS blob.

Additional Note:

Another variant of this query is:

SELECT * FROM table WHERE NUMBERS LIKE '%3%' AND NUMBERS LIKE '%15%'

However, using LIKE %% is not recommended because it can lead to performance issues with large data sets. The IN operator is more efficient for this type of check.

The above is the detailed content of How to Check for Specific Numbers in a Comma-Separated Blob in MySQL?. For more information, please follow other related articles on the PHP Chinese website!