3

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?

Joe Scotto
  • 10,936
  • 14
  • 66
  • 136
  • 3
    "but wouldn't that be slow in the long run with say 10 million plus rows of data?" No. 10 million rows with a decent index is chump change for a halfways decent database. You can find any value in a table of 10,000,000 with only 25 comparisons (log2 of 10mil is 23.25) – corsiKa Jan 21 '19 at 00:31
  • It will be a lot faster to search 10 million rows of single pieces of data than 2 million rows of (for example) comma separated data, where you won't easily be able to use an index. – Nick Jan 21 '19 at 00:41

1 Answers1

3

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Yup, reading into it some more online this seems like the proper solution. Thank you for the detailed explanation. – Joe Scotto Jan 21 '19 at 00:47