如何从逗号分隔ID列匹配指定$user_id？SQL查询求助

Hey there! The problem with your current LIKE query is two-fold: first, it can trigger false matches (for example, if your $user_id is 2, it would incorrectly match IDs like asdaxxdfd2 or 23445rr55), and second, it might miss valid matches when the target ID is at the very start or end of the comma-separated list (since there's no leading or trailing comma to anchor the match). Let's go over the proper solutions:

Storing comma-separated values in a single column goes against relational database best practices—it makes queries inefficient, prone to errors, and hard to maintain. The better approach is to create a join table (also called a junction table) to handle the many-to-many relationship.

For example, create a table named my_table_users with two columns:

• my_table_id (foreign key linking to my_table.id)
• user_id (the individual user IDs you previously stored as comma-separated values)

Then your query becomes clean and reliable:

SELECT mt.id 
FROM my_table mt
JOIN my_table_users mtu ON mt.id = mtu.my_table_id
WHERE mtu.user_id = ?

Pro tip: Always use parameterized queries like this instead of directly concatenating $user_id into your SQL string—it prevents SQL injection attacks.

If you're stuck with the current schema temporarily, use functions built into your database to safely check for the exact ID in the comma-separated list:

MySQL/MariaDB #

Use FIND_IN_SET(), which is designed explicitly for comma-separated lists:

SELECT id FROM my_table WHERE FIND_IN_SET(?, id) > 0

This function will correctly match the exact $user_id regardless of its position in the list (start, middle, or end).

PostgreSQL #

Convert the comma-separated string to an array and check if your target ID is present:

SELECT id FROM my_table WHERE ? = ANY(string_to_array(id, ','))

SQL Server #

Use STRING_SPLIT() to break the list into individual values, then match against them:

SELECT mt.id 
FROM my_table mt
CROSS APPLY STRING_SPLIT(mt.id, ',') AS split_ids
WHERE split_ids.value = ?

Again, make sure to use parameterized queries here instead of string concatenation to keep your code secure.

内容的提问来源于stack exchange，提问作者Vitali Kloster