提问人：Mad Scientist 提问时间：9/7/2010 最后编辑：Mad Scientist 更新时间：6/14/2022 访问量：100248
在数据库列中存储分隔列表真的有那么糟糕吗？
Is storing a delimited list in a database column really that bad?
问：
想象一个带有一组复选框的 Web 表单（可以选择其中任何一个或所有复选框）。我选择将它们保存在以逗号分隔的值列表中，这些值存储在数据库表的一列中。
现在，我知道正确的解决方案是创建第二个表并正确规范化数据库。实施这个简单的解决方案的速度更快，而且我希望能够快速地对该应用程序进行概念验证，而不必花太多时间在上面。
我认为在我的情况下，节省的时间和更简单的代码是值得的，这是一个合理的设计选择，还是我应该从一开始就将其规范化？
还有一些上下文，这是一个小型内部应用程序，它基本上取代了存储在共享文件夹中的 Excel 文件。我之所以问，也是因为我正在考虑清理程序并使其更易于维护。其中有些事情我并不完全满意，其中之一就是这个问题的主题。
答：
11赞
duffymo
9/7/2010
#1
是的，我会说这真的很糟糕。这是一个站得住脚的选择，但这并不能使它正确或好。
它打破了第一范态形式。
第二个批评是，将原始输入结果直接放入数据库，而没有任何验证或绑定，会使您容易受到SQL注入攻击。
你所说的懒惰和缺乏SQL知识是新手所构成的东西。我建议花时间正确地做这件事，并将其视为学习的机会。
或者保持原样，吸取SQL注入攻击的痛苦教训。
评论
22赞
Hammerite
9/7/2010
在这个问题中，我没有看到任何迹象表明他容易受到SQL注入的影响。SQL注入和数据库规范化是正交的话题，你对注入的题外话与问题无关。
0赞
Mad Scientist
9/7/2010
输入被转义了，任何有权访问此应用程序的人都已经有了更简单的方法来造成严重破坏。我正在使用Drupal db_query访问数据库，单独提供参数。
0赞
Paul Tomblin
9/7/2010
@Hammerite，即使这种特殊的懒惰和不愿意学习不会导致SQL注入，其他同样态度的例子也会。
0赞
duffymo
9/7/2010
@Hammerite，也没有什么可以排除这种可能性。我认为值得提出，以防 OP 的无知也扩展到 SQL 注入。我同意规范化和SQL注入可以是正交的，但是在没有其他信息的情况下，在我看来应该提到它。这几乎无关紧要。
5赞
Hammerite
9/7/2010
@Paul：也许同样的态度会导致他被公共汽车撞到，因为他在过马路之前没有向两边看，但你没有警告过他。编辑：我以为你是这个答案的海报，我的错误。
4赞
Raj
9/7/2010
#2
好吧，我已经在 SQL Server 的 NTEXT 列中使用键/值对制表符分隔列表超过 4 年了，它有效。你确实失去了进行查询的灵活性，但另一方面，如果你有一个持久化/去持久化键值对的库，那么这并不是一个坏主意。
评论
16赞
Paul Tomblin
9/7/2010
不，这是一个可怕的主意。你已经设法逃脱了，但你几分钟的开发时间成本已经使你的查询性能、灵活性和代码的可维护性变得糟糕。
5赞
Raj
9/7/2010
保罗，我同意。但正如我所说，我使用 if 用于特定目的，即用于数据输入操作，其中有多种表单。现在我已经学会了 NHibernate，我正在修改设计，但当时我需要灵活性来设计 ASP.NET 表单并使用文本框 ID 作为键/值对中的键。
35赞
Mark Brackett
7/10/2013
告诉维护该应用程序 4 年的人关于维护问题有点冒昧。在软件开发中很少有“可怕”的想法 - 大多数它们只是适用性非常有限的想法。警告人们这些局限性是合理的，但责备那些做过并经历过它的人，我觉得这是一种比你更神圣的态度，我可以没有。
45赞
OMG Ponies
9/7/2010
#3
关于SO问的问题有很多：
- 如何从逗号分隔列表中获取特定值的计数
- 如何从逗号分隔的列表中获取仅具有相同 2/3/etc 特定值的记录
逗号分隔列表的另一个问题是确保值一致 - 存储文本意味着拼写错误的可能性......
这些都是非规范化数据的症状，并突出显示了为什么应始终为规范化数据建模。非规范化可以是一种查询优化，在实际出现需求时应用。
0赞
Jerry Coffin
9/7/2010
#4
我可能会采取中间立场：将 CSV 中的每个字段放入数据库中的单独列中，但不太担心规范化（至少现在是这样）。在某些时候，规范化可能会变得有趣，但是由于所有数据都被推入一列，因此使用数据库几乎没有任何好处。您需要将数据分成逻辑字段/列/任何您想要调用它们的内容，然后才能对其进行有意义的操作。
19赞
bobbymcr
9/7/2010
#5
一般来说，如果符合项目的要求，任何事情都可以辩护。这并不意味着人们会同意或想要捍卫你的决定......
通常，以这种方式存储数据是次优的（例如，更难进行有效的查询），并且如果修改表单中的项目，可能会导致维护问题。也许你可以找到一个中间立场，用一个整数来表示一组位标志？
评论
0赞
Vincent
4/28/2023
在性能方面，这将比中间表更好地扩展。您遍历 N 而不是 NxM（如果 M 是每个项目的平均类别数）
49赞
Hammerite
9/7/2010
#6
“其中一个原因是懒惰”。
这敲响了警钟。你应该做这样的事情的唯一原因是你知道如何“以正确的方式”去做，但你得出的结论是，有一个切实的理由不这样做。
话虽如此：如果您选择以这种方式存储的数据是您永远不需要查询的数据，那么可能会以您选择的方式存储它。
（一些用户会对我上一段中的说法提出异议，说“你永远不知道将来会添加什么要求”。这些用户要么被误导，要么陈述宗教信仰。有时，按照摆在你面前的要求工作是有利的。
评论
3赞
foresightyj
1/30/2015
我总是听到一些人说“我的设计比你的更灵活”，当我面对他们诸如不设置外键约束或将列表存储在单个字段中之类的事情时。对我来说，灵活性（在这种情况下）==没有纪律==懒惰。
652赞
Bill Karwin
9/7/2010
#7
除了由于在单个列中存储了一组重复的值而违反了第一范式之外，逗号分隔列表还有很多其他更实际的问题：
- 无法确保每个值都是正确的数据类型：无法阻止 1,2,3，banana，5
- 不能使用外键约束将值链接到查找表;无法强制执行引用完整性。
- 无法强制执行唯一性：无法阻止 1,2,3,3,3,5
- 如果不提取整个列表，则无法从列表中删除值。
- 存储列表的时间不能超过字符串列中适合的长度。
- 很难在列表中搜索具有给定值的所有实体;您必须使用低效的表扫描。可能必须求助于正则表达式，例如在 MySQL： 或 MySQL 8.0 中：
idlist REGEXP '[[:<:]]2[[:>:]]'idlist REGEXP '\\b2\\b' - 很难计算列表中的元素，或执行其他聚合查询。
- 很难将值联接到它们引用的查找表中。
- 很难按排序顺序获取列表。
- 很难选择保证不会出现在值中的分隔符
为了解决这些问题，您必须编写大量的应用程序代码，以更高效的方式重新发明RDBMS已经提供的功能。
逗号分隔的列表是错误的，因此我将此作为我书的第一章：SQL Antipatterns，第 1 卷：避免数据库编程的陷阱。
有时您需要使用非规范化，但正如 @OMG Ponies 所提到的，这些都是例外情况。任何非关系“优化”都会使一种类型的查询受益，但会牺牲数据的其他用途，因此请确保您知道哪些查询需要特殊处理，以至于它们值得非规范化。
评论
11赞
Frank Heikens
11/25/2011
ARRAY（任何数据类型）都可以修复异常，只需检查 PostgreSQL： postgresql.org/docs/current/static/arrays.html （@Bill：好书，任何开发人员或 dba 的必读书）
2赞
Bill Karwin
9/25/2014
@CraigRinger，是的，这是一种非规范化。如果使用得当，对于您尝试优化的特定查询，非规范化可能是正确的做法，但必须完全理解它会损害其他查询。如果这些其他查询对你的应用程序不重要，那么痛苦就不那么重要了。
3赞
jmcclure
9/23/2015
我知道不推荐它，但玩恶魔倡导者：如果有一个处理唯一性和数据类型的 ui（否则会出错或行为不端），其中大部分都可以被删除，ui 无论如何都会删除并创建它，有一个驱动程序表，值来自其中使它们唯一，可以使用像“%P%”这样的字段， 值为 P、R、S、T，计数无关紧要，排序也无关紧要。根据 ui，可以拆分值，例如，在最不常见的情况下从驱动程序表中选中列表中的复选框，而无需转到另一个表来获取它们。
6赞
Bill Karwin
3/1/2018
@PrabhuNandanKumar，我会将 174 行存储在引用您的第一个表的第二个表中。不要存储具有类似数据的 174 列。
5赞
Bill Karwin
12/8/2020
从另一个角度来看：挑战不在于设计一个数据库，允许一个表现良好的客户端插入正确形成的数据。挑战在于确保任何客户端插入的所有数据始终正确形成。
6赞
Robin
9/7/2010
#8
是的，就是这么糟糕。我的观点是，如果你不喜欢使用关系数据库，那么就寻找一个更适合你的替代方案，有很多有趣的“NOSQL”项目，有一些非常高级的功能。
8赞
James A Mohler
7/13/2013
#9
我需要一个多值列，它可以作为 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 可以解决分隔列表的一些问题，并且可以根据需要转换为分隔列表
-1赞
Solomon Ucko
12/1/2018
#10
如果您有固定数量的布尔字段，则可以对每个字段使用（或者如果存在）或（可为空）。你也可以使用 a（我忘记了确切的语法）。INT(1) NOT NULLBIT NOT NULLCHAR (0)SET
评论
5赞
Rick James
5/24/2020
INT(1)占用 4 个字节;是没有意义的。(1)
1赞
jarlh
9/26/2021
INT（1） 占用多少字节是特定于产品的，也是 INT（1） 的含义。可以是一个数字、一个字节、一个单词，或者其他什么？
评论