I have a varchar field in mysql that contains some numbers e.g.: 4, 3, 1, 2 seperated by commas. I want to search the data base and select only the rows that contain one number from that field. For example, select all the rows from table where field contains number 4. Anyone know the syntax for that? How can mysql scan the field and see if number 4 is in it?
There's a function called FIND_IN_SET(), however it may be better to break this field outin some fashion such that it only has to store a single number. Not only is this generally considered better design, but also allows for more storage capacity.
Normally, this sort of storage is broken into at least two tables. Table A contains fields which are common to multiple records from table B. B will then reference a record in A. This is often referred to as a one-to-many relationship between A and B, respectively.