当前位置: 首页 > 面试题库 >

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

阎德宇
2023-03-14
问题内容

假设我有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);

问题答案:

初步的

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

但必须指出,这本身就构成了局限性:您被固定为这样的观念,即这是正确的解决方案,并且只需进行一些小的更正或指导,便会奏效。那是不对的。因此,我必须请您放弃该概念,向后退一步,并查看(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.

  • Query & Print

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



 类似资料:
  • 我可以运行查询以获取文档的确切数量吗? 我有收藏: 执行查询: 回应1份文件,但我需要2份 看到这种反应的最佳方式是什么?

  • 在我的Laravel5.4项目中,我试图从模型文章和状态之间的关系中获取一些数据。关系类型为OneToMany,其中Article具有一个状态,Status具有多个项目。 为了获取想要的数据,我迭代通过模型文章项目的集合,这些项目是eagerLoade连同模型/关系状态一起借助方法。第一次迭代构建了一个正确的查询,但是在每次迭代中,它都会追加由方法

  • 我如何比较用户输入的这些数组呢?

  • 问题内容: 我是node(express)和pg-promise的新手,还无法弄清楚如何将每个嵌套查询(loop)的结果添加到主json数组结果查询中。 我有两个表:帖子和评论。 因此,我想将每个评论的结果添加到每个帖子中并返回帖子。我有这个,但是不起作用: 答案: 选项1(最佳选择) : 通过JSON到psql进行单个查询( JSON查询 ) 参见@ vitaly-t的回答 要么 使用ajax异

  • 无法找到这个问题的答案。我有一个mysqli循环语句。在这个循环中,我想运行另一个查询。我不能同时编写这两个sql。可能吗?我想,因为我使用stmt并设置它来准备语句。所以我添加了另一个变量stmt2。单独运行它们是可行的,但像我写的那样运行它会给我“mysqli致命错误:对非对象调用成员函数bind_param()” 伪代码: 答案是: 我真傻,我不知道我必须有两个mysqli连接。所以解决方案