提问



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


现在,我知道正确的解决方案是创建第二个表并正确地规范化数据库。实现简单的解决方案更快,我想快速获得该应用程序的概念验证,而无需花费太多时间。


我认为节省的时间和更简单的代码在我的情况下是值得的,这是一个可辩护的设计选择,还是我应该从一开始就将它标准化?


更多上下文,这是一个小型内部应用程序,实际上取代了存储在共享文件夹中的Excel文件。我也在问,因为我正在考虑清理程序并使其更易于维护。在那里有一些事情,我并不完全满意,其中一个是这个问题的主题。

最佳参考


除了因为存储在单个列中的重复值组而违反First Normal Form之外,逗号分隔列表还有许多其他更实际的问题:[2]



  • 无法确保每个值都是正确的数据类型:无法阻止 1,2,3,banana,5

  • 无法使用外键约束将值链接到查找表;无法强制执行参照完整性。

  • 无法强制执行唯一性:无法阻止 1,2,3,3,3,5

  • 如果不提取整个列表,则无法从列表中删除值。

  • 不能存储比字符串列更长的列表。

  • 很难在列表中搜索具有给定值的所有实体;你必须使用低效的表扫描。可能不得不求助于正则表达式,例如MySQL:

    idlist REGEXP '[**:<:**]2[**:>:**]' *

  • 难以计算列表中的元素,或执行其他聚合查询。

  • 很难将值加入他们引用的查找表中。

  • 难以按排序顺序获取列表。

  • 将整数存储为字符串所需的空间大约是存储二进制整数的两倍。更不用说逗号字符占用的空间了。



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


以逗号分隔的列表是错误的,我把它作为我书中的第一章:SQL Antipatterns:避免数据库编程的陷阱。[3]


有时您需要使用非规范化,但正如@OMG小马提到的那样,这些都是异常情况。任何非关系型优化都会以牺牲其他数据使用为代价来获益,因此请确保您知道哪些查询需要特别处理,以至于它们需要非规范化。





* MySQL 8.0不再支持这种字边界表达式语法。

其它参考1


关于SO的问题有很多问题:



  • 如何从逗号分隔列表中获取特定值的计数

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


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


这些都是非规范化数据的症状,并突出显示为什么您应该始终为规范化数据建模。非规范化可以是一个查询优化, 在需要实际呈现时应用

其它参考2


一个原因是懒惰。


这响起警钟。你应该做这样的事情的唯一原因是你知道如何以正确的方式做到这一点但你已经得出结论,有一个切实的理由不这样做。


话虽如此:如果您选择以这种方式存储的数据是您永远不需要查询的数据,那么可能存在以您选择的方式存储它的情况。


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

其它参考3


一般来说,如果符合项目要求,任何事物都是可以防御的。这并不意味着人们会同意或想要捍卫你的决定......


通常,以这种方式存储数据是不理想的(例如,难以进行有效的查询),如果修改表单中的项目,可能会导致维护问题。也许你可以找到一个中间地带并使用代表一组位标志的整数代替?

其它参考4


是的,我会说它确实那么糟糕。这是一个可辩护的选择,但这并不能使它正确或好。


它破坏了第一个正常形式。


第二个批评是将原始输入结果直接放入数据库,而没有任何验证或绑定,这使您对SQL注入攻击持开放态度。


你所谓的懒惰和缺乏SQL知识的是新手所做的事情。我建议花时间去做正确的事情并将其视为学习的机会。


或者保持原样并学习SQL注入攻击的痛苦教训。

其它参考5


我需要一个多值列,它可以实现为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项目,其中包含一些非常先进的功能。

其它参考7


好吧,我已经在SQL Server的NTEXT列中使用键/值对选项卡分隔列表已超过4年了,它可以工作。你确实失去了查询的灵活性,但另一方面,如果你有一个库坚持/掠夺关键价值对然后它不是一个坏主意。

其它参考8


我可能会采取中间立场:将CSV中的每个字段放入数据库中的单独列中,但不要担心规范化(至少目前为止)。在某些时候,规范化可能变得有趣,但是将所有数据推入单个列中,您几乎无法从使用数据库中获益。您需要将数据分成逻辑字段/列/任何你想要调用它们之前,你可以有意义地操纵它。