logo标签列表

在数据库列中存储分隔列表真的那么糟糕吗？

databasedatabase-designdatabase-normalization
418

想象一个带有一组复选框的Web表单（可以选择任意或所有复选框）。我选择将它们保存在数据库表的一个列中，作为逗号分隔的值列表。

现在，我知道正确的解决方法是创建第二个表并正确规范化数据库。但实施易于操作的简单解决方案更快，并且我想快速地生成该应用程序的概念验证，而不必花费太多时间。

我认为在我的情况下节省时间和代码简洁是值得的，这是否是可维护的设计选择？或者我应该从一开始就进行规范化处理？

更多背景信息：这是一个小型内部应用程序，基本上替换了存储在共享文件夹上的Excel文件。我也问这个问题，因为我正在考虑清理程序并使其更易于维护。其中有一些我不完全满意的东西，其中之一就是这个问题的话题。

32
既然这样，为什么还要麻烦数据库呢？将数据保存在文件中就可以了。 - thavan
8
同意@thavan的观点。为什么要保存概念验证的数据？一旦你完成了验证，再正确地添加数据库。在进行概念验证时使用轻量级的方式可以，但不要做需要之后撤销的事情。 - Jeff Davis
2
在Postgres中，应该优先使用数组列而不是逗号分隔的列表。这至少确保了正确的数据类型，没有区分分隔符和实际数据的问题，并且可以高效地进行索引。 - user330315
@a_horse_with_no_name - 如果包含文本内容的列进行了GIN索引（这是一次性操作），会有什么情况？谢谢。 - fnisi
10个回答
651

除了在单个列中存储重复值的重复组外，逗号分隔列表还违反了第一范式，同时存在很多其他实际问题：

  • 无法确保每个值都是正确的数据类型：无法防止 1,2,3,banana,5 这种情况。
  • 无法使用外键约束将值链接到查找表来强制执行引用完整性。
  • 无法强制唯一性：无法防止 1,2,3,3,3,5 这种情况。
  • 无法从列表中删除值而不获取整个列表。
  • 无法存储超过字符串列所容纳长度的列表。
  • 难以搜索具有给定值的所有实体；必须使用低效的表扫描。例如，在MySQL中可能需要使用正则表达式：
    idlist REGEXP '[[:<:]]2[[:>:]]' 或者在 MySQL 8.0 中：idlist REGEXP '\\b2\\b'
  • 难以计算列表中的元素数量或执行其他汇总查询。
  • 难以将值连接到它们引用的查找表。
  • 难以按排序顺序获取列表。
  • 难以选择保证不会出现在值中的分隔符。

要解决这些问题，您必须编写大量的应用程序代码，重新实现RDBMS已经提供的功能。

逗号分隔列表足够糟糕，以至于我将其作为我的书的第一章：《SQL反模式：避免数据库编程的陷阱》（卷1）。

有时候你需要使用反规范化技术，但正如@OMG Ponies提到的，这些情况属于例外。任何非关系型“优化”都是以牺牲其他数据用途为代价来获得某一种类型查询的性能提升，所以请确保清楚地知道哪些查询需要采用反规范化技术进行特殊处理。

11
一个包含任何数据类型的数组可以解决这个异常，只需要检查 PostgreSQL：http://www.postgresql.org/docs/current/static/arrays.html（@Bill: 这是一本很棒的书，任何开发者或数据库管理员都应该阅读）。 - Frank Heikens
2
@CraigRinger，是的，这是一种非规范化类型。当小心使用时，非规范化可以成为优化某个查询的正确方法，但必须充分理解它会损害其他查询。如果那些其他查询对您的应用程序不重要，则痛苦较小。 - Bill Karwin
3
我知道这样做不被推荐，但我要替魔鬼出谋划策：如果有一个处理唯一性和数据类型的用户界面（否则会出错或不正常），大多数情况下可以将它们移除。无论如何，用户界面都会删除并创建它们，有一个驱动表，其中的值用于使它们唯一，可以使用类似'%P%'的字段，值为P、R、S、T，在这种情况下计数和排序并不重要，取决于用户界面，值可以被分割[]。例如，在最不常见的情况下，可以从驱动表中的复选框列表中检查复选框，而无需转到另一个表格获取它们。 - jmcclure
6
@PrabhuNandanKumar，我会在第二个表中存储174个行，该表引用您的第一个表。不要存储具有类似数据的174列。 - Bill Karwin
5
另一种观点是：挑战不在于设计一个数据库允许一个表现良好的客户端插入正确格式的数据。挑战在于确保任何客户端插入的所有数据都始终正确格式化。 - Bill Karwin
显示剩余12条评论
49

“其中一个原因是懒惰。”

这触动了警报。你这样做的唯一原因应该是你知道如何以“正确的方式”去做，但是你得出结论，有实质性的理由不这样做。

话虽如此：如果你选择存储的数据是你永远不需要查询的数据，那么可能有理由采用你选择的存储方式。

（有些用户会对我上一段话中的陈述提出异议，说“你永远无法知道未来会增加什么要求”。这些用户要么是误导了，要么是表达了宗教信仰。有时候，在你拥有的要求的基础上工作是有优势的。）

3
每次我对那些没有设置外键约束或将列表存储在单个字段中的事情进行质疑时，总会有人说“我的设计比你的更灵活”。在我看来，在这种情况下，灵活性等于没有纪律等于懒惰。 - foresightyj
45

有许多SO上的问题询问：

  • 如何从逗号分隔列表中获取特定值的计数
  • 如何获取仅具有相同2/3 /等特定值的记录来自该逗号分隔列表

逗号分隔列表的另一个问题是确保值的一致性 - 存储文本意味着可能存在拼写错误...

所有这些都是非规范化数据的症状，并突显了为什么您应始终对规范化数据进行建模。非规范化设计可以是查询优化，需要在实际需要时应用。

19

总的来说，只要满足您项目的要求，任何事情都是可被辩护的。这并不意味着人们会同意或者想要为您的决定辩护...

通常，以这种方式存储数据是次优的（例如，难以进行有效的查询），如果您修改表单中的项目可能会导致维护问题。也许你可以找到一个折中方案，使用代表一组位标志的整数？

从性能角度来看，这比中间表更具可扩展性。您正在循环处理N而不是NxM（如果M是每个项目的平均类别数）。 - Vincent
11

是的，我会说它确实很糟糕。这是一个可辩护的选择，但这并不意味着它是正确或好的。

这打破了第一范式。

第二个批评是将原始输入结果直接放入数据库中，没有任何验证或绑定，这让你容易受到SQL注入攻击。

你所称之为懒惰和缺乏SQL知识的东西是新手的特点。我建议花时间来做好它，并将其视为学习的机会。

或者保持现状，并学习SQL注入攻击的痛苦教训。

22
我看不出这个问题中有任何暗示他易受SQL注入攻击。SQL注入和数据库规范化是无关的话题，你在注入方面的离题讨论与问题无关。 - Hammerite
输入已经被转义，而且任何有权访问此应用程序的人都有更简单的方法来制造混乱。我正在使用Drupal db_query来访问数据库，分别提供参数。 - Mad Scientist
@Hammerite，即使这种懒惰和不愿学习的行为没有导致SQL注入，其他类似态度的例子也会。 - Paul Tomblin
@Hammerite，也没有排除可能性。我认为提一下SQL注入问题是值得的，以防OP对此一无所知。我同意规范化和SQL注入可以是正交的，但在没有其他信息的情况下，似乎应该提到它。这并不是无关紧要的。 - duffymo
5
@Paul: 或许相同的态度会导致他在过马路时未看清两侧而被公交车撞到，但你却没有对此进行警告。编辑：我之前以为你是这个回答的发布者，我的错误。 - Hammerite
8
我需要一个多值列，可以实现为 XML 字段。
必要时可以将其转换为逗号分隔的形式。 使用 Xquery 在 SQL Server 中查询 XML 列表。
通过成为 XML 字段，可以解决一些问题。 使用 CSV：无法确保每个值都是正确的数据类型：无法防止 1、2、3、banana、5 等情况。 使用 XML：标签中的值可以强制为正确的类型。
使用CSV：无法使用外键约束将值链接到查找表；没有强制执行引用完整性的方法。
使用XML：仍然存在问题。
使用CSV：无法强制唯一性，无法防止1,2,3,3,3,5等情况发生。
使用XML：仍然存在问题。
使用CSV：无法在不获取整个列表的情况下删除列表中的某个值。
使用XML：可以删除单个项目。
使用CSV：在列表中查找具有给定值的所有实体很困难；您必须使用低效的表扫描。
使用XML：可以对xml字段进行索引。
使用CSV: 在列表中计算元素或进行其他聚合查询比较困难。**
使用XML: 不是特别困难。
使用CSV：难以将值与它们所引用的查找表连接起来。
使用XML：不是特别困难。

使用CSV：难以按排序顺序获取列表。

使用XML：不是特别难

使用 CSV：将整数存储为字符串比存储二进制整数多占用约两倍的空间。
使用 XML：存储甚至比 CSV 更糟糕。
使用CSV格式：加上很多逗号字符。
使用XML格式：使用标签代替逗号。
简而言之，使用XML可以避免一些分隔符列表的问题，并且可以根据需要将其转换为分隔符列表。
6

是的，情况确实很糟糕。我的看法是，如果你不喜欢使用关系数据库，那么就寻找一个更适合你的替代品，现在有许多有趣的“NOSQL”项目，具有一些非常先进的功能。

4

我已经使用SQL Server中的NTEXT列中的键/值对制表符分隔列表超过4年了，它很有效。虽然您会失去进行查询的灵活性，但另一方面，如果您有一个库来持久化/解析键值对，那么这并不是一个坏主意。

16
不，这是个可怕的想法。虽然你成功地完成了它，但是你在几分钟的开发时间里省下的代价是糟糕的查询性能、灵活性和代码的可维护性。 - Paul Tomblin
5
保罗，我同意。但是正如我所说的，我使用了 if 语句来完成特定的任务，这个任务是针对一个数据输入操作，涉及多种类型的表单。现在我已经学会了 NHibernate，正在修改设计，但那时我需要在 ASP.NET 中设计表单并使用文本框 ID 作为键/值对中的键，因此需要灵活运用 if 语句。 - Raj
35
向一个已经维护了4年应用程序的人提出维护方面的关注有点自以为是。软件开发中很少有什么“可怕”的想法，大多数只是适用范围非常有限的想法。警告人们注意其局限性是合理的，但是指责那些已经做过并且经历过的人让我感到他们有一种比别人更高尚的态度，这种态度我不需要。 - Mark Brackett
0
我可能会采取折中方案：将 CSV 中的每个字段转换为数据库中的单独列，但暂时不用过多关注规范化。在某些时候，规范化可能变得有趣，但是如果所有数据都被塞到单个列中，您实际上没有从使用数据库中获得任何好处。您需要将数据分成逻辑字段/列/任何您想称呼它们的部分，然后才能有意义地操作它。
-1
如果您有固定数量的布尔字段，可以为每个字段使用INT（1）NOT NULL（如果存在，则为BIT NOT NULL）或CHAR（0）（可为空）。您还可以使用SET（我忘记了确切的语法）。
5
INT(1) 占用 4 字节；(1) 是没有意义的。 - Rick James
1
INT(1) 占用多少字节取决于产品，同时也取决于 INT(1) 的含义。它可以是一个数字、一个字节、一个词或者其他什么东西。 - jarlh
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接