主页 / dba / 问题 / 126030
Accepted
Human_AfterAll
Asked: 2016-01-13 11:03:16 +0800 CST

将多个值存储在一行的一个字段中而不是单独的行中的可能好处

  • 772

在我们上次的每周例会上，一位没有数据库管理背景经验的人提出了这个问题：

“是否有一种情况可以证明以行（字符串）而不是多行存储数据是合理的？”

让我们假设一个表countryStates，我们想在哪里存储一个国家的状态；我将在此示例中使用 USA，并且为了懒惰而不会列出所有州。

在那里我们将有两列；一个叫Country，另一个叫States。正如这里所讨论的，并由@srutzky 的回答提出，这PK将是ISO 3166-1 alpha-3定义的代码。

我们的表格看起来像这样：

+---------+-----------------------+-------------------------------------------------------+
| Country | States                | StateName                                             |
+---------+-----------------------+-------------------------------------------------------+
| USA     | AL, CA, FL,OH, NY, WY | Alabama, California, Florida, Ohio, New York, Wyoming |
+---------+-----------------------+-------------------------------------------------------+

当向一位开发者朋友问同样的问题时，他说从数据流量大小的角度来看，这可能很有用，但如果我们需要操纵这些数据，就没有用了。在这种情况下，应用程序代码必须有一个智能，可以将这个字符串转换成一个列表（假设有权访问这个表的软件需要创建一个组合框）。

我们得出的结论是这个模型不是很有用，但我怀疑是否有办法让它变得有用。

我想问的是你们中是否有人已经看到、听到或以真正有效的方式做过类似的事情。

sql-server database-design

6 个回答

  1. Best Answer

    首先，当前的问题标题是指“将数据存储为字符串而不是列”，这有点令人困惑。当谈到将数据存储为字符串而不是其他内容时，通常是指将所有内容序列化为字符串格式而不是适当/强数据类型（例如INTor DATETIME）。但是，如果询问将数据存储为单个字段中的多个值而不是单独的行，那就有点不同了。公平地说，虽然使用字符串最容易连接值，但也可以使用INT和BINARY类型来完成，或者通过位掩码或类似地保留某些位置以具有不同的含义。由于第二种解释是实际询问的内容，因此根据问题的文本，让我们解决这个问题。

    一句话：不。如果您要存储实际数据点，那么它只会带来痛苦（在代码和性能方面），因为它是不必要的复杂化。如果它是一个仅作为单个单元存储、作为单个单元更新并且从不在数据库中分解的值，那么这可能是可以的，因为它大致类似于存储图像或 PDF。否则，任何解析数据的尝试都将使用任何索引（例如使用LIKE '%something%'、或CHARINDEX、或PATINDEX、或SUBSTRING等）无效。

    如果您需要在单行的单个字段中存储单独的值，那么有更合适的方法来执行此操作：XML 或 JSON。这些是可解析的格式（XML / JSON），甚至可以对 XML 进行索引。但理想情况下，这些数据将存储在正确类型的字段中，以便真正有用。

    请不要忘记，RDBMS 的目的是存储数据，以便在符合ACID的约束范围内尽可能高效地检索和操作数据。由于需要首先解析值，因此检索连接的值已经够糟糕的了，而且这是不可索引的。但是操作通常意味着替换整个 blob 只是为了更新它的一部分（假设不存在与函数一起使用的模式）。XML 数据类型至少允许XML DML进行简单更新，尽管这些仍然不如正确建模数据的简单更新快。REPLACE

    此外，考虑到如上面问题中所示的场景，通过将所有 StateCode 连接在一起，您将无法使用外键（在任一方向）这些值。

    如果业务需求随时间发生变化，您需要跟踪这些项目的其他属性怎么办？就“州”而言，首都、人口、排序顺序或其他什么？正确存储为行，您可以为其他属性添加更多列。当然，您可以拥有多个级别的可解析数据，|StateCode,Capital,Population |StateCode,Capital,Populate|...但希望任何人都能看到问题呈指数级增长失控。当然，XML 和 JSON 格式很容易处理这个特殊问题，这就是上面提到的它们的价值。但是您仍然需要一个很好的理由来使用其中任何一个作为初始建模方法，因为它们都不会像在单独的行中使用离散字段那样有效。

    • 13

  2. 实际上，我出于非常有限的目的使用了类似的东西。我们为输出文件创建了一个标题表。它们是专门构建的，主要是列标题，但不完全是。所以数据看起来像

    OutputType   OutputHeader
PersonalData Name|Address|City|State|Zip
JobInfo      Name|JobName|JobTitle

    从本质上讲，它看起来像是一个分隔列表。在某种程度上它是。但就我们的目的而言，它是一个长字符串。

    这就是这里的诀窍。如果您从不打算解析列表，那么值得保存列表。但是，如果您将甚至可能需要解析列表，那么值得花费额外的空间和时间将其拆分并保存在单独的行中。

    • 9

  3. 我曾经在一张相当小的桌子上使用过它，例如：

    CREATE TABLE t1 (
  ID number,
  some_feature   varchar2(100),
  valid_channels  varchar2(100));

CREATE TABLE channel_def (
  channel varchar2(100));

    然后将值存储CRM,SMS,SELF-CARE到valid_channel.

    整个表有大约 10 条记录。 valid_channel包含实际上应该在描述多对多关系的链接表中的值。桌子t1不会被大量使用，所以我们决定走这条路。不过，这一决定涉及一些政治因素（见下文）。

    但总的来说我避免它，它不是 3NF。

    我现在工作的地方有几十个这样的专栏。他们的理由是它使他们的查询更容易：而不是使用链接表连接三个表，他们可以直接使用LIKE. 例如

    SELECT * 
  FROM t1 
 INNER JOIN channel_def cd
    ON ','||t1.valid_channels||',' LIKE '%,'||cd.channel||',%';

    在 Oracle 上的 Horrible + 它由于启动而禁用索引的使用'%,'。

    • 1

  4. 这是在 SE 上完成的。正如 Marc Gravell所写：

    ...经过一番思考和考虑，我们决定使用竖线（条）分隔的自然表示，带有前导/尾随竖线，因此“.net c#”变成了简单的“|.net|c#|”。这有优点：

    • 解析非常简单
    • 可以通过简单的替换来完成批量更新和删除标签（包括管道，以避免替换中间标签匹配）
    • ...

    这种“新格式”是“旧格式”的下一步，后者略有不同，被选择使用 SQL Server 全文搜索功能，因此如果您从头开始执行此操作，则其中一些好处并不相关。

    由于工作量和性能的原因，他们可能没有完全规范化事情。

    • 1

  5. 好吧，使用字符串和其他数据类型的一个可能的主要好处是，当可能需要纯粹的性能时，使用 SQLCLR 将它们从 SQL Server 发送到 C#、C、C++（等）。您甚至可以创建一个视图或存储过程来以非关系方式表示关系数据——正如您在上面的示例中为此目的所做的那样。

    看这个例子：

    http://aboutsqlserver.com/2013/07/22/clr-vs-t-sql-performance-considerations/

    根据 Wikipedia：SQL CLR 或 SQLCLR（SQL 公共语言运行时）是用于在 SQL Server 中托管 Microsoft .NET 公共语言运行时引擎的技术。SQLCLR 允许托管代码由 Microsoft SQL Server 环境托管并在其中运行。

    • 0

  6. 在我看来，答案是否定的。我没有使用过这种方法并且会避免它——我想不出为什么我会走这条路。您倾向于使用数组的 JSON/NoSQL 世界。

    我们在之前的角色中有类似的设计选择，架构师团队希望有一个“数据”字段，该字段被分隔然后转换为二进制。由于几个原因，我们最终没有走那条路。

    如果您必须加入这种类型的数据，那将是一种丑陋的体验。更新字符串的单个元素也会令人不快。

    • 0

相关问题