2

I am working on a project that was started with someone else. In the db instead for using a separate table the developer had opted for saving the 1 to many relationships on a single table with comma separated tables. The table structure is like this 

CREATE TABLE pages(
  pageid INT(6) AUTO_INCREMENT PRIMARY KEY,
  newsid INT(6),
  pages  VARCHAR(30)
);

How can I search for a value 1 from the column pages. I have identified a few conditions that may appear, but was not been able to create a solution for it.

If I am searching for 1 the following patterns should be handles

1,  match
11  shouldn't match
11, shouldn't match
,1, match
,1  match
1   match
21  shouldn't match
21, shouldn't match

I have been thinking about this for sometime, but no solution came up. I don't think normal %LIKE% can be used here

Sample sql on sqlfiddle

Also I need to search multiple values too like 1, 7 and 3

Cœur
  • 37,241
  • 25
  • 195
  • 267
livestrong
  • 65
  • 6
  • And this is why databases should be normalized to begin with.. this problem comes up far too often. – user2864740 Jul 05 '15 at 18:59
  • Try [normalizing](http://stackoverflow.com/questions/17308669/reverse-group-concat-in-mysql) your database. It will be worth the effort. – Cohan Jul 05 '15 at 19:00
  • possible duplicate of [MySQL search in comma list](http://stackoverflow.com/questions/5458703/mysql-search-in-comma-list) – user2864740 Jul 05 '15 at 19:02
  • Comes up a good bit.. http://stackoverflow.com/questions/5033047/mysql-query-finding-values-in-a-comma-separated-string?lq=1 , http://stackoverflow.com/questions/2674011/mysql-check-if-numbers-are-in-a-comma-separated-list?lq=1 , http://stackoverflow.com/questions/5458703/mysql-search-in-comma-list , http://stackoverflow.com/questions/14401892/fetch-record-from-mysql-table-based-on-comma-seperated-value-in-column?rq=1 – user2864740 Jul 05 '15 at 19:03
  • @user2864740 I was also wondering of why would he had done it.. Also I think I need to improve my searching techniques.. anyway thanks for the effort – livestrong Jul 05 '15 at 19:05
  • @livestrong Because of improper database normalization (this fails 1NF); as a result of what specific reasoning I know not. It appears 'easier' at first but in most cases - and especially when the value is *not* opaque (eg. it is searched, joined on, or piece-wise updated) - it just doesn't fit the SQL model. – user2864740 Jul 05 '15 at 19:06
  • @user2864740 I n postgres there is a datatype `json` which can store data in json format and its very easy to search and index.. is there anything like that in mysql – livestrong Jul 05 '15 at 19:08
  • No. While PgSQL has a JSON type and SQL Server supports an XML type which *do* support indexing/searching these still fall outside the primary RA model. As such, while useful and powerful and flexible, they should not replace primary normalization. – user2864740 Jul 05 '15 at 19:10

1 Answers1

3

Use FIND_IN_SET().

Example:

SELECT * FROM pages WHERE FIND_IN_SET('1', pages)

From the documentation:

FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.

(highlighting added)

Fabian Schmengler
  • 24,155
  • 9
  • 79
  • 111