What is the best way to map a field to multiple identifiers stored in mySQL

Question

I have a table with a column called "owners", this contains IDS for users who are associated with this particular record.

I am currently restricting data with ",". For example,

ID | Name | Owners
1 | Bob | 1,4,5

When making a selection on this, I intended to use the following SQL:

select * from table where owner='$profile' or owner like '%,$profile%' or owner like '%$profile,%'

but now I understand that this is spoiled (a search of 5 will match 5, 15, 25, even 50).

What would be the right way to do this?

Answer 1

@Amarnasan is correct: do not store multiple values ​​in the same field, separated by commas!

SQL Antipatterns Jaywalking.

- , owners . , :

Record_ID | Owners_ID
1 | 1
1 | 4
1 | 5

:

select * from table
join intersection_table
where intersection_table.record_id = table.id
and intersection_table.owners_id = '$profile'

Answer 2

( "" ).

, , , :

SELECT * FROM table WHERE ',' + owner + ',' LIKE '%,5,%'

Answer 3

, .. , , :

SELECT * 
  FROM table 
 WHERE FIND_IN_SET(:profile, Owners) > 0

. FIND_IN_SET

Answer 4

find_in_set

SELECT * FROM table where find_in_set($profile,owner)

Answer 5

FIND_IN_SET , .

, . , "1, 2, 3", , , "1", " 2" " 3", "1", "2", "3" ( ).