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?
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.
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加