Display rows via comma values in MySQL

Question

I have a comma delimited list like this saved in a mysql table:

-----------------
|id | list      |
-----------------
| 4 |12,45,65,4 |

Each number in "list" corresponds with an ID in another table.

Is there a way so I can query the other table based on theses IDs and bring up those rows associated with the numbers in "list"?

possible duplicate of Mysql WHERE problem with comma-separated list — Michael Berkowski
– Michael Berkowski, Commented May 22, 2012 at 20:02

Emil Vikström · Accepted Answer · 2012-05-22 20:00:28Z

2

Not any efficient way with your current schema. The correct and most efficient way to do it is to change the schema to hold multiple rows like this:

-----------------
|id | list      |
-----------------
| 4 |    12     |
| 4 |    45     |
| 4 |    65     |
| 4 |     4     |

Then you use JOIN operations to connect 4 to every related row in your other table.

This is called database normalization and is a very important topic in database design. Relational database systems are built to handle just this types of problems in an efficient manner.

answered May 22, 2012 at 20:00

Emil Vikström

92.3k17 gold badges144 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

adamzwakk Over a year ago

I thought so, looks like I'll have to restructure things slightly. Thanks for the input!

ypercubeᵀᴹ Over a year ago

@adamzwakk: Read this, too: Is storing a comma separated list in a database column really that bad?

Collectives™ on Stack Overflow

Display rows via comma values in MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related