CODEWITHSUNDEEP
mysqlsqldatabaserelational-database
Joe Scotto
Joe Scotto

Reputation: 10877

Store multiple values for multiple times in one column?

With MySQL is there a way to store an array of key/values for a single column? For example, I'm wanting to store a few (3 - 6) different values for times of the day such as this:

12AM: 1.2
1AM: 3.0
6AM: 4.0

I've seen that doing a relational table might be the right way to do this but I would have no control over the column timeframes or values, they would be user input data. The data here would also link to a single user.

I could have a separate table linked by UID that would have multiple rows with the key/value but wouldn't that be slow in the long run with say 10 million plus rows of data?

Upvotes: 3

Views: 467

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562631

MySQL does not support an array data type, though it supports a JSON data type in MySQL 5.7 and later.

Strictly speaking, you can store any kind of semi-structured data you want in a TEXT column. But then you will find it's not easy or efficient to search it.

You might find yourself wishing to search your "array" for a specific value. In such a case, you'll be tempted to use LIKE '%pattern%' or regular expression matching or something like that to find a particular time of day. These expressions are hard to impossible to optimize with indexes, so searches generally become table-scans.

The alternative is to use a child table, with one datetime per row referencing your UID. Yes, this can grow to be a long table with millions of rows, but it is much easier to optimize the search with indexes. An indexes search on a table with millions of rows will beat a non-indexes table-scan search on a much smaller table.

You'll experience less gray hair and have a much happier experience with SQL if you live by the rule that every column should be one scalar value — not an "array," or a comma-separated list, or a "document" of JSON or XML, or any other sort of semi-structured data like that.

Upvotes: 3

Related Questions