MySQL fetching comma separated values

Question

I am working on a project developed by a developer who left the company. There is a book table which a column theme_id. theme_id store in comma separated value such as

id  theme_id
50  14,15

Currently the previous developer created another table book_theme_list with column book_id and theme_id to do the searching. For example:

book_id theme_id
50      14
50      15

It works but data is duplicated and the two table need to sync on every CRUD.

What is the point on having this? I delete book_theme_list and use %LIKE% in my query and it also work.

You should have kept `book_theme_list` and removed the `theme_id` column from `book`. Read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — Nick, Dec 29 '19 at 04:04

score 1 · Answer 1 · answered Dec 29 '19 at 04:13

You can get the theme_ids in a single column by using below code

SELECT book_id, GROUP_CONCAT(DISTINCT theme_id SEPARATOR ',') 
FROM book_theme_list
GROUP BY book_id

then if any update or insert on book_theme_list, it triggers an update on other table as ;

CREATE TRIGGER theme_Id_check
AFTER INSERT OR UPDATE 
ON book_theme_list 
FOR EACH ROW 

UPDATE  book
SET theme_id = (
   SELECT GROUP_CONCAT(DISTINCT theme_id SEPARATOR ',') 
   FROM book_theme_list
   WHERE book_id = new.book_id
   GROUP BY book_id )
WHERE book_id =  new.book_id
END;

score 1 · Answer 2 · answered Dec 29 '19 at 04:43

Search on book table using %like% will work but will be slower because b-tree index can't be used when search happens only on book.theme_id column. If you search by both book.id and book.theme_id column then it would be ok as long as index is present on book.id. If this table is not searched as much and functionally not very important then you should be fine with this solution, but if it is a heavy traffic table then I will suggest keeping book_theme_list.

MySQL fetching comma separated values

2 Answers2