将数字数组存储在数据库字段中
上下文：SQL Server 2008，C#
我有一个整数数组（0-10 个元素）。数据不会经常更改，但会经常检索。
我可以创建一个单独的表来存储数字，但由于某种原因，感觉这不是最佳选择。
问题#1：我应该将数组存储在单独的表中吗？请给出一种或另一种方式的理由。
问题#2：（无论问题#1 的答案是什么），在数据库字段中存储 int[] 的“最佳”方式是什么？ XML？ JSON？ CSV？
编辑： 一些背景：存储的数字只是一些不参与任何关系的系数，并且始终用作数组（即永远不会单独检索或使用值）。
Context: SQL Server 2008, C#
I have an array of integers (0-10 elements). Data doesn't change often, but is retrieved often.
I could create a separate table to store the numbers, but for some reason it feels like that wouldn't be optimal.
Question #1: Should I store my array in a separate table? Please give reasons for one way or the other.
Question #2: (regardless of what the answer to Q#1 is), what's the "best" way to store int[] in database field? XML? JSON? CSV?
EDIT:
Some background: numbers being stored are just some coefficients that don't participate in any relationship, and are always used as an array (i.e. never a value is being retrieved or used in isolation).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
单独的表，标准化
不是 XML 或 json ，而是单独行中的单独数字
无论您怎么想，这都是最好的方法。你可以稍后感谢我
Separate table, normalized
Not as XML or json , but separate numbers in separate rows
No matter what you think, it's the best way. You can thank me later
在数据库中存储数据的“最佳”方式是最有利于对其执行操作并且使维护最容易的方式。后面的要求应该会引导您找到标准化的解决方案，这意味着将整数存储在具有关系的表中。除了更容易更新之外，您之后的下一个开发人员也更容易了解信息的存储内容和方式。
The "best" way to store data in a database is the way that is most conducive to the operations that will be performed on it and the one which makes maintenance easiest. It is this later requirement which should lead you to a normalized solution which means storing the integers in a table with a relationship. Beyond being easier to update, it is easier for the next developer that comes after you to understand what and how the information is stored.
将其存储为 JSON 数组，但要知道所有访问现在都将针对整个数组 - 不再对特定系数进行单独的读/写。
在我们的例子中，我们将它们存储为 json 数组。就像您的情况一样，各个数组编号之间没有关系 - 数组仅作为一个单元才有意义，并且作为一个单元，它与表中的其他列有关系。顺便说一句，其他一切都正常化了。我将其比作：如果要存储 10 字节块，则将其保存在 VARBINARY(10) 的单列中。您不会将其分成 10 个字节，将每个字节存储在 VARBINARY(1) 列中，然后用外键将它们缝合在一起。我的意思是你可以 - 但这没有任何意义。
作为开发人员，您需要了解该 int 数组到底有多么“单一”。
Store it as a JSON array but know that all accesses will now be for the entire array - no individual read/writes to specific coefficients.
In our case, we're storing them as a json array. Like your case, there is no relationship between individual array numbers - the array only make sense as a unit and as a unit it DOES has a relationship with other columns in the table. By the way, everything else IS normalized. I liken it to this: If you were going to store a 10 byte chunk, you'd save it packed in a single column of VARBINARY(10). You wouldn't shard it into 10 bytes, store each in a column of VARBINARY(1) and then stitch them together with a foreign key. I mean you could - but it wouldn't make any sense.
YOU as the developer will need to understand how 'monolithic' that array of int's really is.
单独的表将是最“规范化”的方法。从长远来看，这可能会更好，因为您不必解析列的值来提取每个整数。
如果您愿意，也可以使用 XML 列 来存储数据。
稀疏列可能也是您的另一种选择。
如果你想让它变得非常简单，你可以只分隔值：
10;2;44;1A separate table would be the most "normalized" way to do this. And it is better in the long run, probably, since you won't have to parse the value of the column to extract each integer.
If you want you could use an XML column to store the data, too.
Sparse columns may be another option for you, too.
If you want to keep it really simple you could just delimit the values:
10;2;44;1我认为既然您正在谈论 sql server，则表明您的应用程序可能是数据驱动的应用程序。如果是这种情况，我肯定会将数组作为单独的表保留在数据库中，并为每个值记录一条记录。它将被标准化并优化以供检索。即使数组中只有几个值，您也可能需要将该数据与可能需要与数组值“连接”的其他检索数据结合起来。在这种情况下，sql 通过使用索引、外键等（规范化）进行优化。
话虽这么说，如果不需要更改这些值，您始终可以对代码中的 10 个值进行硬编码，并将往返保存到数据库。这取决于您的应用程序如何工作以及该数组的用途。
I think since you are talking about sql server that indicates that your app may be a data driven application. If that is the case I would keep definately keep the array in the database as a seperate table with a record for each value. It will be normalized and optimized for retreival. Even if you only have a few values in the array you may need to combine that data with other retreived data that may need to be "joined" with your array values. In which case sql is optimized for by using indexes, foreign keys, etc. (normalized).
That being said, you can always hard code the 10 values in your code and save the round trip to the DB if you don't need to change the values. It depends on how your application works and what this array is going to be used for.
我同意所有其他人关于最好是一个单独的标准化表的观点。但是，如果您坚持将所有内容都放在同一个表中，请不要将数组放在唯一的一列中。相反，创建 10 列并将每个数组值存储在不同的列中。它将为您省去解析和更新问题。
I agree with all the others about the best being a separate normalized table. But if you insist in having it all in the same table don't place the array in one only column. In instead create the 10 columns and store each array value in a different column. It will save you the parsing and update problems.