PostgreSQL - 整数数组最佳实践

Question

最近在开发一个Web应用程序时，我决定在数据模型中使用integer[]。有两个表，一个是文章数据，另一个是标签（标签ID和描述），我决定将文章所带的标签ID存储在文章.tags integer[]列中。

正如Milen A. Radev指出的那样：

提示：数组不是集合；搜索特定数组元素可能是数据库设计不当的迹象。考虑使用一个单独的表，每个项目都有一行作为数组元素。这样更容易搜索，并且对于大量元素来说，很可能会更好地扩展。

不仅如此，在使用JDBC和iBatis处理integer[]时也变得有些困难。

目前，我可以利用已经实现的工作实现来完成我需要做的事情。但为了简化起见，它可能会被重新设计，使用一个单独的表来存储文章.id和tag.id的关系。

最后，我对integer[]最适合用于什么上下文感到困惑？

我认为我通过艰难的方式找到了它不适合的用途。

- vector

Answer 1

在处理诸如评论线程之类的树结构时，我曾经在PostgreSQL中使用过数组。您可以将从根节点到您的节点的路径存储为一系列分支数字。然后，按正确的显示顺序提取整个树只是一个简单的问题：

SELECT stuff
FROM comments
WHERE thread = X
ORDER BY path -- This would be the array.

PostgreSQL对数组的比较采用了唯一合理的方式。使用数组作为从根节点到路径的路径，还可以轻松计算节点的深度。您可以使用字符串（每个分支号码有3个基于96的数字）和ASCII字母表排序来实现相同的目的，但是数组更加清晰明了。

是的，还有其他处理树的方法更加严谨，但使用数组提供了一个非常清晰的实现。如果我要进行大量的树操作，那么维护路径数组将涉及很多繁琐的工作，因此我可能会选择不同的表示法。

虽然不完全是针对Java的，但在某些情况下，数组是所需数据的自然且有用的表示形式（即使在SQL中也是如此）。

- mu is too short

---

绝佳的例子。 - Dan Nissenbaum

Answer 2

我能想到三个应用场景：

第一个是为了去规范化。权衡包括：您无法轻松地更新或单独处理元素。但是一次性获取所有元素很容易和快速，而且可以节省大量空间。

第二个与之略有关联，是您使用数组不是为了存储，而是为了进行某些中间处理。例如，如果您想在非 SQL 语言中批处理数据，而该语言不易处理适当的集合。

第三个是为了存储有序的数据列表。我遇到过几个这样的应用程序，但很难说清楚。当然，您也可以在具有额外列的表中表示它们的位置，但有时这没有多大意义，因为您不需要在数据库中单独访问各个部分。在某些情况下，这只是客户端应用程序想要存储和稍后检索的列表。

但您的整体感觉是正确的。如果您不知道更好的方法，您的第一反应可能不应该使用数组。

- Peter Eisentraut

Answer 3

在我看来，由于任何数组都是违反了第一范式，所以最好的情景是：......（鼓声）.....没有。

这引出了一个问题，为什么我们会有不希望查询的数据。所有的值都有潜在的可搜索性，如果我们不应该在可搜索的值上使用数组，那么我们再次得出这样的结论，即从未使用它是值得的。

这只留下了一个假设的案例，即您仅出于客户端分析和操作的目的存储了一个数组。我相信这些存在，但并非我的经验。

编辑：上面我说：“我相信这些存在......”请查看 @mu is too short 的回答，其中提供了一个例子。

- Ken Downs

Answer 4

整数数组对于转换表来说是不错的选择。其中键是索引，已知每个索引都有一个值，或者有一种表示空位置的方式（如-1）。我认为在这种情况下比外键更快。

另一个用途是图表。每次测试运行的结果如何。测试运行有6个结果。一行是一个整数数组，包含6个结果。

- Lee Louviere

Answer 5

如果数组作为整体是一个值，我可能会考虑使用数组数据类型。但是，什么情况下数组只是整体的值呢？我并不确定。

也许只有在寻找整个值更有意义的时候才会如此。在你想知道相应行之前，拥有一个应该恰好包含你要搜索的项目的表格似乎并没有太多意义。因此，如果您想将完整的数组作为键进行搜索。

- ontrack