Have Geek Brain

MYSQL: how to search for fields containing sep values. separated by commas?

  • tags (id_tag, name)
  • news (id, title, data, tags)

The field news> tag is varchar (255). I am planning to place such data in this field: "1,7,34"

This means that a particular line in the news is associated with tags 1, 7 and 34 from the tag table.

Then, how can I search for ALL records that have a value of 34 (among other things) in the tags field?

Is there a better way to do this?

+2


a source to share


2 answers


You can completely normalize the design of your table by adding a third NewsTags (PKID, news_ID, tag_ID) table and filling it this way (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 join News to NewsTags to tags, WHERE Tags.TagID = 34

Try it; I'm sure the following code sample will follow.

+4


a source


There is a way to do this. You can do something line by line:

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 terrible resource god (and also looks ugly) because you cannot index parts of the columns. Per-row functions in selected queries never scale well.

The correct way to do this is to separate the comma-separated values ​​and follow third normal form (3NF) - all novice DBAs should really read 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 make efficient use of the indexes that you configured on all tables.

You should never put yourself in a situation where you need to use a portion of a column for anything, as this inherently destroys scalability. By separating the tags into separate columns (and making them true many-to-many relationships by introducing a different table), you remove this problem.

All databases should be built in 3NF and only returned if there is a performance issue and only when you understand the implications. There are tricks you can use to ensure data integrity (such as insert / update triggers) in these cases, but for newbies, I suggest sticking with 3NF.

+3


a source






More articles:


All Articles