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

想象一个带有一组复选框的web表单(可以选择其中任何一个或所有复选框)。我选择将它们保存在一个以逗号分隔的值列表中，这些值存储在数据库表的一列中。

现在，我知道正确的解决方案是创建第二个表并正确地规范化数据库。它可以更快地实现简单的解决方案，并且我希望快速地对该应用程序进行概念验证，而不必在其上花费太多时间。

我认为在我的情况下，节省的时间和更简单的代码是值得的，这是一个合理的设计选择吗，还是我应该从一开始就将其规范化?

更详细地说，这是一个小型的内部应用程序，本质上是替换存储在共享文件夹中的Excel文件。我问这个问题也是因为我正在考虑清理程序并使其更易于维护。书中有些东西我不是很满意，其中之一就是这个问题的主题。

92402 次浏览
小开

是的，我想说真的很糟糕。这是一个合理的选择，但这并不意味着它是正确的或好的。

它打破了第一正规形式。

第二个批评是，将原始输入结果直接放入数据库，而根本不需要任何验证或绑定，这会使您容易受到SQL注入攻击。

你所谓的懒惰和缺乏SQL知识是新手的东西。我建议你花点时间好好做这件事，并把它看作一个学习的机会。

或者让它保持现状，吸取SQL注入攻击的痛苦教训。

小开

好吧，我已经在SQL Server的NTEXT列中使用键/值对标签分离列表4年多了，现在它工作了。你确实失去了进行查询的灵活性，但另一方面，如果你有一个持久化/取消持久化键值对的库，那么这不是一个坏主意。

小开

关于SO提问有很多问题:

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

逗号分隔列表的另一个问题是确保值是一致的——存储文本意味着可能会出现错别字……

这些都是非规范化数据的症状，并强调了为什么应该始终为规范化数据建模。去规范化可以是一个查询优化，当实际需要出现时应用。

小开

我可能会采取中间立场:将CSV中的每个字段放到数据库中单独的列中，但不太担心规范化(至少目前如此)。在某些情况下，标准化可能变得很有趣，但当所有数据都被塞进一列时，你实际上根本没有从使用数据库中获得任何好处。在对数据进行有意义的操作之前，您需要将数据分离为逻辑字段/列/任何您想要调用它们的内容。

小开

一般来说，只要能满足项目的要求，任何东西都是可以防御的。这并不意味着人们会同意或想要捍卫你的决定……

一般来说，以这种方式存储数据是次优的(例如，难以进行有效的查询)，如果修改表单中的项，可能会导致维护问题。也许您可以找到一个中间立场，使用一个整数来表示一组位标志?

小开

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

这敲响了警钟。你应该这样做的唯一原因是你知道如何以“正确的方式”做这件事，但你已经得出结论，有一个切实的理由不这样做。

话虽如此:如果您选择以这种方式存储的数据是您永远不需要查询的数据，那么可能有理由以您所选择的方式存储它。

(有些用户会对我上一段中的说法提出异议，说“你永远不知道将来会增加什么需求”。这些用户要么是被误导了，要么是在陈述宗教信仰。有时候按照摆在你面前的要求工作是有利的。)

小开
最佳答案

除了违反第一范式(因为在单列中存储了重复的值组)之外，逗号分隔的列表还有许多其他更实际的问题:

  • 不能确保每个值都是正确的数据类型:没有办法防止1、2、3、香蕉、5
  • 不能使用外键约束将值链接到查找表;没有办法强制引用完整性。
  • 不能强制唯一性:没有办法防止1、2、3、3、3、5
  • 不能在不获取整个列表的情况下从列表中删除值。
  • 无法存储比字符串列长度更长的列表。
  • 很难在列表中搜索给定值的所有实体;你必须使用低效的表格扫描。可能不得不求助于正则表达式，例如在MySQL中:
    idlist REGEXP '[[:<:]]2[[:>:]]'或MySQL 8.0: idlist REGEXP '\\b2\\b'
  • 难以计数列表中的元素，或执行其他聚合查询。
  • 很难将值连接到它们引用的查找表中。
  • 很难按顺序获取列表。
  • 很难选择保证不出现在值中的分隔符

为了解决这些问题，你必须编写大量的应用程序代码，重新发明RDBMS 已经提供了更有效的. RDBMS的功能。

逗号分隔的列表是错误的，我把它作为我的书的第一章:SQL反模式，第1卷:避免数据库编程陷阱。

有时需要使用反规范化，但作为@OMG小马提到，这些是例外情况。任何非关系“优化”都以牺牲数据的其他用途为代价，使某一类型的查询受益，因此一定要知道哪些查询需要特别处理，从而值得进行非规范化处理。

小开

是的，有那么糟。我的观点是，如果你不喜欢使用关系数据库，那么寻找一个更适合你的替代方案，有很多有趣的“NOSQL”项目，它们具有一些非常高级的功能。

小开

我需要一个多值列，它可以实现为一个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解决了带分隔符列表的一些问题，并且可以根据需要将其转换为带分隔符列表

小开

如果你有一个固定数量的布尔字段，你可以使用INT(1) NOT NULL(或BIT NOT NULL如果存在)或CHAR (0)(可空)。你也可以使用SET(我忘记了确切的语法)。


提交答案