CODEWITHSUNDEEP
mysql
Andres SK
Andres SK

Reputation: 10974

MYSQL: how to search for fields that hold values sep. by commas?

  1. tags (id_tag,name)
  2. news (id,title,data,tags)

The field news>tags is a varchar(255). Im planning to put data like this in that field: "1,7,34"

That means that a particular row in news is linked to tags 1, 7 and 34 from the tags table.

Then, how can i search for ALL news records that have the 34 value (among others) in the tags field?

Is there a better way to do this?

Upvotes: 2

Views: 130

Answers (2)

paxdiablo
paxdiablo

Reputation: 881253

There is a way to do this. You can do something along the lines of:

select id from news where ','||tags||',' like '%,34,%'

or:

select id from news where tags like '34,%'
union all select id from news where tags like ',34%'
union all select id from news where tags like '%,34,%'

but it will be a horrible hog of resources (as well as looking ugly) because you cannot index parts of columns. Per-row functions in select queries never scale well.

The right way to do this is to separate the comma separated values out and follow third normal form (3NF) - all aspiring DBAs really should read up on this concept as should all coders who want to work with and understand databases:

tags:
    id_tag
    name
    primary key (id_tag)
news
    id
    title
    data
    primary key (id)
news_tags
    id          foreign key news(id)
    id_tag      foreign key tags(id_tag)
    primary key (id,id_tag)
    index       (id_tag)

Then you can use a query like:

select n.id from news n, news_tags nt
    where nt.id_tag = 34
      and n.id = nt.id

(or its explicit-join equivalent). This will allow the DBMS to efficiently use the indexes you have set up for all the tables.

You should never put yourself in the situation where you need to use part of a column for anything, since that inherently destroys scalability. By separating out the tags into separate columns (and making them a true many-to-many relationship by introducing another table), you remove that problem.

All databases should be built in 3NF and only reverted if there's a performance problem and only when you understand the consequences. There are tricks you can use to ensure data integrity (such as insert/update triggers) in those cases but, for a beginner, I would suggest sticking with 3NF.

Upvotes: 3

LesterDove
LesterDove

Reputation: 3044

You could fully normalize your table design by adding a third table NewsTags (PKID, news_ID, tag_ID) and fill it thusly (assuming your example news record is record 100):

1, 100, 1
1, 100, 7
1, 100, 34

Then, for the "how" part of your question, you'd be joining News to NewsTags to Tags, WHERE Tags.TagID = 34

Try it out; I'm sure some sample code will follow..

Upvotes: 4

Related Questions