1. 首页
  2. 问题
  3. 从表创建嵌套数组的最佳方法：多个查询/循环VS单个查询/循环样式
一尘不染

从表创建嵌套数组的最佳方法：多个查询/循环VS单个查询/循环样式

sql

假设我有2个表，可以“合并”并以单个嵌套数组表示。

考虑到以下因素，我在徘徊什么是最好的方法：

  • 效率
  • 最佳实践
  • 数据库/服务器端使用权衡
  • 你在现实生活中应该做什么
  • 可以以这种方式“合并”的3个，4个或更多表的大小写相同

问题是关于任何服务器端/关系数据库。

我正在考虑的2种简单方法（如果还有其他方法，请提出建议！请注意， 我在索要一个简单的SERVER-SIDE和RELATIONAL-DB
，所以请不要浪费您的时间解释为什么我不应该使用这种方法DB，使用MVC设计等，等等…）：

  1. 2个循环，5个简单的“ SELECT”查询
  2. 1个循环，1个“ JOIN”查询

我试图给出一个简单而详细的示例，以说明自己并更好地理解您的答案（尽管在这里问题不在于如何编写代码和/或发现可能的错误，所以请不要专注于此。） ）

用于将数据创建和插入表的SQL脚本

CREATE TABLE persons
(
    id int NOT NULL AUTO_INCREMENT,
    fullName varchar(255),
    PRIMARY KEY (id)
);

INSERT INTO persons (fullName) VALUES ('Alice'), ('Bob'), ('Carl'), ('Dan');

CREATE TABLE phoneNumbers
(
    id int NOT NULL AUTO_INCREMENT,
    personId int,
    phoneNumber varchar(255),
    PRIMARY KEY (id)
);

INSERT INTO phoneNumbers (personId, phoneNumber) VALUES ( 1, '123-456'), ( 1, '234-567'), (1, '345-678'), (2, '456-789'), (2, '567-890'), (3, '678-901'), (4, '789-012');

我“合并”后的表的JSON表示形式：

[
  {
    "id": 1,
    "fullName": "Alice",
    "phoneNumbers": [
      "123-456",
      "234-567",
      "345-678"
    ]
  },
  {
    "id": 2,
    "fullName": "Bob",
    "phoneNumbers": [
      "456-789",
      "567-890"
    ]
  },
  {
    "id": 3,
    "fullName": "Carl",
    "phoneNumbers": [
      "678-901"
    ]
  },
  {
    "id": 4,
    "fullName": "Dan",
    "phoneNumbers": [
      "789-012"
    ]
  }
]

伪代码有两种方式：

1。

query: "SELECT id, fullName FROM persons"
personList = new List<Person>()
foreach row x in query result:
    current = new Person(x.fullName)
    "SELECT phoneNumber FROM phoneNumbers WHERE personId = x.id"
    foreach row y in query result:
        current.phoneNumbers.Push(y.phoneNumber)
    personList.Push(current)        
print personList

2。

query: "SELECT persons.id, fullName, phoneNumber FROM persons
            LEFT JOIN phoneNumbers ON persons.id = phoneNumbers.personId"
personList = new List<Person>()
current = null
previouseId = null
foreach row x in query result:
    if ( x.id !=  previouseId )
        if ( current != null )
            personList.Push(current)
            current = null
        current = new Person(x.fullName)
    current.phoneNumbers.Push(x.phoneNumber)
print personList

PHP / MYSQL中的代码实现：

1。

/* get all persons */
$result = mysql_query("SELECT id, fullName FROM persons"); 
$personsArray = array(); //Create an array
//loop all persons
while ($row = mysql_fetch_assoc($result))
{
    //add new person
    $current = array();
    $current['id'] = $row['id'];
    $current['fullName'] = $row['fullName'];

    /* add all person phone-numbers */
    $id = $current['id'];
    $sub_result = mysql_query("SELECT phoneNumber FROM phoneNumbers WHERE personId = {$id}");
    $phoneNumbers = array();
    while ($sub_row = mysql_fetch_assoc($sub_result))
    {
        $phoneNumbers[] = $sub_row['phoneNumber']);
    }
    //add phoneNumbers array to person
    $current['phoneNumbers'] = $phoneNumbers;

    //add person to final result array
    $personsArray[] = $current;
}

echo json_encode($personsArray);

2。

/* get all persons and their phone-numbers in a single query */
$sql = "SELECT persons.id, fullName, phoneNumber FROM persons
            LEFT JOIN phoneNumbers ON persons.id = phoneNumbers.personId";
$result = mysql_query($sql);

$personsArray = array();
/* init temp vars to save current person's data */
$current = null;
$previouseId = null;
$phoneNumbers = array();
while ($row = mysql_fetch_assoc($result))
{
    /*
       if the current id is different from the previous id:
       you've got to a new person.
       save the previous person (if such exists),
       and create a new one
    */
    if ($row['id'] != $previouseId )
    {
        // in the first iteration,
        // current (previous person) is null,
        // don't add it
        if ( !is_null($current) )
        {
            $current['phoneNumbers'] = $phoneNumbers;
            $personsArray[] = $current;
            $current = null;
            $previouseId = null;
            $phoneNumbers = array();
        }

        // create a new person
        $current = array();
        $current['id'] = $row['id'];
        $current['fullName'] = $row['fullName'];
        // set current as previous id
        $previouseId = $current['id'];
    }

    // you always add the phone-number 
    // to the current phone-number list
    $phoneNumbers[] = $row['phoneNumber'];
    }
}

// don't forget to add the last person (saved in "current")
if (!is_null($current))
    $personsArray[] = $current);

echo json_encode($personsArray);

阅读 124

收藏
2021-03-10

共1个答案

一尘不染

初步的

首先，感谢您付出大量的精力来解释问题和进行格式化。很高兴看到一个清楚自己在做什么和在问什么的人。

但必须指出，这本身就构成了局限性：您被固定为这样的观念，即这是正确的解决方案，并且只需进行一些小的更正或指导，便会奏效。那是不对的。因此，我必须请您放弃该概念，向后退一步，并查看（a）整个问题和（b）没有该概念的答案。

该答案的上下文是：

  • 您已经给出的所有明确考虑因素，这些都非常重要，我将不再重复

  • 其中两个最重要的是，什么是 最佳做法 ， 我在现实生活中会做什么

该答案源于“标准”，即 最佳实践 的较高等级或参考框架。这就是商业客户/服务器界正在做或应该做的事情。

这个问题，整个问题空间，正在成为一个普遍的问题。我将在这里进行全面考虑，从而也回答另一个SO问题。因此，它可能包含您需要的更多细节。如果是这样，请原谅。

考虑

  1. 该数据库是许多用户共享的基于服务器的资源。在在线系统中，数据库不断变化。它包含每个事实的一个版本的真相（不同于一个地方的一个事实，这是一个单独的规范化问题）。

    • 一些数据库系统没有服务器体系结构，因此这种软件中的 服务器 概念是错误和误导的，这一事实是相互独立但值得注意的一点。

    • 据我了解，出于“性能原因”需要JSON和类似JSON的结构，正是因为“服务器”不能，不能充当服务器。这个概念是在每个（每个）客户端上缓存数据，这样您就不会一直从“服务器”中获取数据。

    • 这会打开一罐蠕虫。如果您没有正确设计和实现此功能，蠕虫将使应用程序超负荷运行。

    • 这样的实现完全违反了客户端/服务器体系结构，后者允许双方都使用简单的代码，并且可以适当地部署软件和数据组件，从而实现时间短且效率高。

    • 此外，这样的实施需要大量的实施工作，并且它是复杂的，由许多部分组成。这些部分中的每一个都必须进行适当的设计。

    • 网络和该主题领域中编写的许多书籍，提供了一种令人困惑的方法组合，这些方法是基于假定的简单性而推向市场的。舒适; 任何人都可以做的事；免费软件可以做任何事情；等等。任何这些提议都没有科学依据。

非建筑和不合标准

如所证明的，您已经了解到某些数据库设计方法是不正确的。您遇到了 一个 问题， 一个
实例表明该建议是错误的。一旦解决了这个问题，就会暴露出目前尚不明显的下一个问题。这些概念是一系列永无止境的问题。

我不会列举有时会提倡的所有错误观念。我相信，随着您对我的回答的进行，您会注意到一个接一个的市场观念是错误的。

两个底线是：

  1. 这些概念违反了体系结构和设计标准，即客户端/服务器体系结构； 开放式建筑 ; 工程原理；对于这个特定问题，较小的是数据库设计原则。

  2. 这就导致像您这样的人，他们试图做一个诚实的工作，被欺骗去实现简单的概念，而这些概念又变成了大规模的实现。永远无法完全实现的实现，因此它们需要进行大量的持续维护，并且最终将被批发替换。

建筑学

违反的中心原则是，永远不要复制任何东西。一旦您拥有了要复制数据的位置（由于缓存或复制或两个单独的单片应用程序等），您就创建了一个副本，该副本在在线情况下
将 不同步。因此，原则是避免这样做。

  • 当然，对于认真的第三方软件（例如主动报告工具），从设计上讲，它们可能会在客户端中很好地缓存基于服务器的数据。但是请注意，在考虑到上述情况的前提下，他们已经投入了数百个人年来正确实施它。您的软件不是这样的。

除了提供必须理解的原理或每个错误的弊端和代价的讲座以外，该答案的其余部分还提供了 您 所请求的，使用正确的体系结构方法（在 最佳实践 之上的步骤）
在现实生活中将要执行的 操作 。

建筑1

不要混淆

  • 必须规范化的 数据

和

  • 的 结果集 ，其中，根据定义，是被扁平化（“去归一化”是不太正确的）中的数据的视图。

假设数据已归一化，则该数据将 不 包含重复值；重复组。结果集 将 包含重复值；重复组。那是行人。

  • 请注意，嵌套集（或嵌套关系）的概念在我看来并不是很好的建议，正是基于这种混淆。

  • 对于自问世45年 RM ，他们已经无法区分基本关系（进行的归一化 不适用 ）源自关系（为的归一化 不适用 ）。

  • 这些支持者中的两个目前正在质疑“第一范式”的定义。1NF是其他NF的基础，如果接受新定义，则所有NF都将失去价值。结果是，规范化本身（用数学术语稀疏定义，但专业人员清楚地理解为一门科学）将受到严重破坏，甚至没有被破坏。

建筑2

有一个古老的科学或工程学原理，必须将内容（数据）与控制（程序元素）分开。这是因为两者的分析，设计和实现是完全不同的。该原则在软件科学中同样重要，因为它具有特定的表达方式。

为了保持简短（哈哈），而不是话语，我将假定您理解：

  • 在数据和程序元素之间存在科学上要求的界限。将它们混合在一起会导致容易出错且难以维护的复杂对象。

    • 在OO / ORM世界中，这一原则的混乱已达到流行病的程度，其后果已广为流传。

    • 只有专业人士才能避免这种情况。对于其余的大多数人，他们接受新的定义为“正常”，并且毕生致力于解决我们根本没有的问题。

  • 根据EF Codd博士的“ 关系模型”， 以 表格形式 存储和呈现数据的架构优势（即巨大的价值）。有针对数据规范化的特定规则。 __

  • 重要的是，您可以确定撰写和销售书籍的人员何时建议非关系或反关系方法。

建筑3

如果在客户端上缓存数据：

  1. 缓存绝对最小值。

这意味着仅缓存在线环境中不变的数据。这意味着仅引用表和查找表，即填充较高级别分类器的表，下拉列表等。

  1. 货币

对于您缓存的每个表，您必须有一种方法（a）与服务器上存在的“真理”的一个版本相比，确定缓存的数据已过时，并且（b）从服务器刷新它， （c）逐表进行。

通常，这涉及每隔五分钟执行一次的后台进程，该进程针对客户端上的每个缓存表与服务器上的DateTime查询MAX更新的DateTime，如果更改，则刷新该表
及其 所有子表，那些依赖于已更改表的表。

当然，这要求您UpdatedDateTime在每个表上都有一列。这不是负担，因为无论如何您都需要进行OLTP
ACID事务（如果您有真实的数据库，而不是一堆不合标准的文件）。

这实际上意味着（永远不会重复）编码负担是过高的。

建筑4

在非商业性的非商业环境中，我了解到有人建议对“一切”进行反向缓存。

  • 这是像PostgreSQL这样的程序可以在多用户系统中使用的唯一方法。

  • 您总能得到所要付出的：花生，猴子。您支付零，您得到零。

如果要在客户端上缓存数据，则体系结构3的必然结果是不要缓存频繁更改的表。这些是交易记录和历史记录表。在客户端上缓存此类表或所有表的概念完全破产。

在真正的客户端/服务器部署中，由于使用适用的标准，对于每个数据窗口，应用程序应基于上下文或过滤器值等，仅查询在特定时间，特定时间所需的行。该应用程序永远不要加载整个表。

如果使用同一窗口的同一用户在第一次检查后15分钟检查了其内容，则数据将过期15分钟。

  • 对于免费软件/共享软件/蒸气软件平台，这些平台通过不存在服务器体系结构来定义自己，因此，结果是，不存在这种性能，因此，您肯定需要缓存超过客户端最小表的数量。

  • 如果这样做，则必须考虑到上述所有因素，并正确实施，否则您的应用程序将被破坏，其后果将促使用户寻求您的终止。如果有多个用户，他们将有相同的原因，并很快组成一支军队。

建筑5

现在我们来 了解如何 在客户端上缓存那些经过精心选择的表。

请注意，数据库会增长，它们会被扩展。

  • 如果系统发生故障（故障），它将以很小的增量增长，并且需要大量的精力。

  • 如果该系统取得很小的成功，它将成倍增长。

  • 如果系统（分别包含数据库和应用程序）设计和实施得当，则更改将很容易，而错误将很少。

因此，必须正确设计应用程序中的所有组件，以符合适用的标准，并且数据库必须完全规范化。反过来，这可以最小化数据库中的更改对应用程序的影响，反之亦然。

  • 该应用程序将由易于维护和更改的简单而不是复杂的对象组成。

  • 对于确实缓存在客户端上的数据，将使用某种形式的数组：OO平台中一个类的多个实例；4GL中的DataWindows（TM，google）。PHP中的简单数组。

（ 顺便说一句。请注意，像您这样的情况下的人们在一年之内所生产的产品，使用商业SQL平台，商业4GL并符合体系结构和标准的专业提供商。 ）

建筑6

因此，假设您理解了以上所有内容，并欣赏了它的价值，尤其是体系结构1和2。

  • 如果您不这样做，请在这里停止并提出问题，请勿继续进行以下操作。

现在我们已经建立了完整的上下文，我们可以解决您的问题的症结所在。

  • 在应用程序中的那些数组中，为什么要在地球上存储展平的数据视图？

    • 从而困扰并困扰着这些问题
    • 而不是存储标准化表的副本？

回答

  1. 切勿重复任何可衍生的内容。那是一个架构原则，不仅限于数据库中的规范化。

  2. 永远不要合并任何东西。

如果这样做，您将 创建 ：

* 客户端上的数据重复及其大量。客户不仅会发胖而且很慢，而且还会与重复数据的镇流器一起被固定在地板上。

* 额外的代码，这是完全不必要的

* 该代码的复杂性

* 易碎的代码，将不得不不断进行更改。

这就是您正在遭受的确切问题，这是该方法的结果，您直觉上是错误的，因此必须有更好的方法。您知道这是一个普遍且普遍的问题。

还请注意，该方法（该代码）构成了您的心理支柱。查看格式化和精美呈现的方式：这对您很重要。我不愿意将所有这些情况告知您。

* 由于您的认真和直率的态度以及您没有发明这种方法的知识，很容易克服哪种勉强
  1. 在每个代码段中，在演示时以及在需要时：

一种。在商业客户/服务器上下文中，
执行将简单，规范化，未重复的表 联接
在一起的查询，并仅检索合格的行。从而获得当前数据值。用户永远不会看到过时的数据。在这里，经常使用视图（归一化数据的展平视图）。

b。在非商业子服务器环境中，
创建一个临时结果集数组，并 连接 简单的，未重复的数组（已缓存的表的副本），并仅使用源数组中的合格行填充该数组。其货币由后台进程维护。

* 使用键来形成数组之间的联接，与使用键来在数据库的关系表中形成联接的方式完全相同。

* 当用户关闭窗口时销毁这些组件。

* 一个聪明的版本将消除结果集数组，并通过键将源数组联接起来，并将结果限制为合格的行。

除了结构上不正确之外，根本不需要嵌套数组或嵌套集或JSON或类似JSON的结构。这是混淆架构1原理的结果。

  • 如果确实选择使用此类结构，则 仅 将它们用于临时结果集数组。

最后，我相信该论述证明了 n个 表不是问题。更重要的是，在数据层次结构深处的 m个 级别（“嵌套”）不是问题。

答案2

现在，我已经给出了完整的上下文（而不是之前的上下文），这消除了您的问题中的含义，并使其成为一个通用的内核。

问题是关于任何服务器端/关系数据库。 [哪个更好]：

2个循环，5个简单的“ SELECT”查询

1个循环，1个“ JOIN”查询

您所提供的详细示例在上面没有正确描述。准确的描述是：

  • 您的Option 1 2个循环，每个循环用于加载每个数组1个单表SELECT查询（每个循环执行了nxm次，最外面的循环仅是一次执行）

  • 您的选择2 1执行一次联接的SELECT查询，然后执行2个循环，每个循环用于加载每个数组

对于商业SQL平台，两者都不适用，因为它不适用。

  • 商业SQL服务器是一个集处理引擎。将一个查询与任何需要的连接一起使用，这将返回一个结果集。永远不要使用循环来遍历所有行，这样会将集处理引擎简化为1970年前的ISAM系统。在服务器中使用视图，因为它提供了最高的性能，并且代码在一个地方。

但是，对于非商业，非服务器平台，其中：

  • 您的“服务器” 不是 集合处理引擎，即。它返回单行，因此您必须手动获取每一行并填充数组， 或者

  • 您的“服务器”并 不能 提供客户端/服务器的结合，即。它没有在客户端上提供将传入结果集绑定到接收数组的功能，因此您必须逐行浏览返回的结果集，并手动填充数组，

按照您的示例 ，答案是，您的选择2很大。

请仔细考虑，并发表评论或提出问题。

对评论的回应

假设我需要将此json（或其他html页面）打印到某个STOUT（示例：http响应：GET /
allUsersPhoneNumbers。这只是一个示例，阐明了我希望得到的内容），应返回此json。
我有一个php函数，得到了这2个结果集（1）。现在它应该打印此json-
我该怎么做？该报告可能是一个员工全年的月薪，等等。一种或其他方式，我需要收集此信息并以“ JOIN”表示形式进行表示

也许我还不够清楚。

  1. 基本上，除非绝对必要，否则请不要使用JSON。这意味着发送到需要它的某个系统，这意味着接收系统，并且该需求是愚蠢的。

  2. 确保您的系统不会对其他人提出这样的要求。

  3. 保持数据规范化。无论是在数据库中，还是在您编写的任何程序元素中。这意味着（在此示例中）每个表或数组使用一个SELECT。这是出于加载目的，因此您可以在程序中的任何位置引用和检查它们。

  4. 当您需要加入时，请了解它是：

    • 结果集；派生关系 一个看法
    • 因此是临时的，仅在该元素执行期间存在

一种。对于表，通过键以通常的方式将它们连接起来。一个查询，联接两个（或多个）表。

b。对于数组，请通过键将程序中的数组连接到数据库中的表，方法与通过键将数据库中的表连接到数据库的方法相同。

  1. 对于您给出的示例（它是对某些请求的响应），首先要了解它是类别[4]，然后实现它。

为什么还要考虑JSON？JSON与这有什么关系？

JSON被误解了，人们对哇的因素很感兴趣。这是寻找问题的解决方案。除非您有该问题，否则它没有任何价值。检查以下两个链接：
Copter-什么是JSON

现在，如果您了解这一点，则主要用于传入的提要。从不外向。此外，在使用之前需要进行解析，解构等操作。

记起：

我需要收集此信息并将其以“ JOIN”表示形式表示

是的。那是行人。加入并 不会 意味着JSONed。

在您的示例中，接收者期望的是展平的视图（例如，电子表格），所有单元格都已填充，是的，对于具有多个PhoneNumber的用户，其用户详细信息将在随后的第二个结果集行中重复。对于任何种类的print,。为了进行调试，我想要一个扁平化的视图。它只是一个：

    SELECT ... FROM Person JOIN PhoneNumber

并返回。或者，如果您满足了来自数组的请求，请加入Person和PhoneNumber数组，这可能需要一个临时的结果集数组，然后将其返回。

请不要告诉我您一次只能获得1个用户，依此类推。

正确的。如果有人告诉您退回程序处理（即，在WHILE循环中逐行执行），而引擎或您的程序已经进行了集处理（即，在一个命令中处理了整个集），则将其标记为应不被听。

我已经说过，您的选择2是正确的，选择1是不正确的。就GET或SELECT而言。

另一方面，对于不具有集合处理功能（即无法在单个命令中打印/设置/检查数组）或不提供客户端数组绑定的“服务器”的编程语言，您确实需要编写循环，数据层次结构的每个深度循环一个循环（在您的示例中，两个循环，一个循环用于Person，一个循环用于每个用户的PhoneNumber）。

  • 您必须执行此操作才能解析传入的JSON对象。
  • 您必须这样做才能从Option 2中返回的结果集中加载每个数组。
  • 您必须执行此操作才能从Option 2中返回的结果集中打印每个数组。

对评论2的回应

我已经说过，我必须返回以嵌套版本表示的结果（比方说，我正在将报告打印到页面上），json只是这种表示形式的一个示例。

我认为您不理解我在此答案中提供的理由和结论。

  • 为了进行打印和显示，切勿 嵌套 。 打印一个展平的视图 ，每个选项2的SELECT返回的行。这是我们打印或显示数据关系31年以来一直在做的事情。它更易于阅读，调试，搜索，查找，折叠，装订，切割。您不能 对 嵌套数组 做 任何事情，只能看着它，并说 有趣的东西 。

代码

警告

I would prefer to take your code and modify it, but actually, looking at your
code, it is not well written or structured, it cannot be reasonably modified.
Second, if I use that, it would be a bad teaching tool. So I will have to give
you fresh, clean code, otherwise you will not learn the correct methods.

This code examples follow my advice, so I am not going to repeat. And this is
way beyond the original question.

Your request, using your Option 2. One SELECT executed once. Followed by one
loop. Which you can “pretty up” if you like.

2021-03-10