How can I store multiple values in MySQL that share the same column?

Question

If one account has multiple values that represent the same variable, how would I go about storing both variables? The number of values could range from one to several hundred. I would think arrays or lists would do the trick or something similar. I've looked through several forums but most of which are old and seem to offer workarounds.

Example:

input1 = 5
input2 = 9
structure -> |int|string|,
structure -> |{5,9}|string|

Best way is to replace your column by a table with a foreign key to the first. — Calimero
– Calimero, Commented Sep 7, 2017 at 15:40
Just don't... storing multiple values in a database column always causes masses of problems; normalize your database structure to handle this as a 1-many relation — Mark Baker
– Mark Baker, Commented Sep 7, 2017 at 15:40
I'm not sure if you're asking what I think you are, but take a look at this just in case: stackoverflow.com/questions/3653462/… — Don't Panic
– Don't Panic, Commented Sep 7, 2017 at 15:40
"I've looked through several forums but most of which are old..." ... we're talking data normalisation here, this is not a new concept. — CD001
– CD001, Commented Sep 7, 2017 at 15:47
The accepted response in that link gave a nice explanation and helped clear up some of my confusion. @Don'tPanic — sellc
– sellc, Commented Sep 7, 2017 at 16:05

Noel Llevares · Accepted Answer · 2017-09-07 15:43:54Z

2

My first suggestion would be to normalize these values into another table and use foreign keys. This allows you the most flexibility for future queries that you may need.

If you really want to push through and use the same column, you can use MySQL's JSON Column Type.

answered Sep 7, 2017 at 15:43

Noel Llevares

16.2k5 gold badges60 silver badges87 bronze badges

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

1 Comment

tadman Over a year ago

If you're not using the data in a relational way, JSON is not a bad call. If you're storing data in there that is relational this is asking for a world of hurt.

Collectives™ on Stack Overflow

How can I store multiple values in MySQL that share the same column?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related