MySQL查询数据库以显示检查列表的最佳方法

Question

我在数据库中有4个表，可以让我管理一种“检查清单”。对于每个病理学，我有一个大步骤（process）分成多个tasks。所有这些都与摘要表中的特定操作（progress.case_id）相关联。

database.pathology
+--------------+------------+
| id_pathology |    name    |
+--------------+------------+
|            1 | Pathology1 |
|            2 | Pathology2 |
|            3 | Pathology3 |
+--------------+------------+

database.process
+------------+----------+--------------+----------------+
| id_process |   name   | pathology_id | days_allocated |
+------------+----------+--------------+----------------+
| 1          | BigTask1 | 2            | 5              |
| 2          | BigTask2 | 2            | 3              |
| 3          | BigTask3 | 2            | 6              |
| ...        | ...      | ...          | ...            |
+------------+----------+--------------+----------------+

database.task
+---------+-------+------------+
| id_task | name  | process_id |
+---------+-------+------------+
| 1       | Task1 | 1          |
| 2       | Task2 | 1          |
| 3       | Task3 | 1          |
| 4       | Task4 | 2          |
| ...     | ...   | ...        |
+---------+-------+------------+

database.progress
+-------------+---------+---------+---------+------------+---------+
| id_progress | task_id | case_id | user_id |    date    | current |
+-------------+---------+---------+---------+------------+---------+
|           1 |       1 |     120 |       2 | 2015-11-02 |       1 |
|           2 |       2 |     120 |       2 | 2015-11-02 |       0 |
|           3 |       1 |     121 |       3 | 2015-11-02 |       1 |
+-------------+---------+---------+---------+------------+---------+

我需要展示类似这样的内容：

我的问题是：最高效的处理方式是什么？

查询只查询一个表（进度）以显示大多数内容，然后再查询其他表以获取不同流程和天数的名称是否更快？

也许联接函数更有效？

或者您认为我的数据库结构不太合适？

对于每种情况，我们可以有大约50个任务，并将当前字段转换为复选框。 还有一个后台脚本正在运行。 基于剩余天数分析提供的天数，以确定该特定案例是否可能会延迟。

对于每种情况，进度表已经填充了与案例的病理学相关的所有任务。 并且当前字段在开始时始终为“0”。

我已经尝试过多种方法，例如

$result = $db->prepare("SELECT DISTINCT process_id,process.name FROM task, progress,process WHERE progress.task_id = task.id_task AND task.process_id = process.id_process AND progress.case_id = ?");     
$result->execute(array($id));
foreach($result as $row)
{
  echo "<b>".$row[1]."</b><br>";
  $result = $db->prepare("SELECT name,id_task FROM task WHERE process_id = ?");     
  $result->execute(array($row[0]));
  foreach($result as $row)
  {
      echo $row[0];
      $result = $db->prepare("SELECT user_id, date, current FROM progress WHERE progress.task_id = ? AND case_id = ?");     
      $result->execute(array($row[1], $id));
      foreach($result as $row)
      {
        if($row[2] == 0)
        {echo "<input type='checkbox' />";}
        else
        {
          echo "<input type='checkbox' checked/>";
          echo "user : ".$row[0]." date : ".$row[1]."<br>";
        }            
      }          
  }

但我很确定我没有做对。我应该更改我的数据库基础设施吗？我应该使用特定的MySQL技巧吗？或者只是使用更有效率的PHP处理方式？

- Atnaize

---

内部查询使用外部查询的数据时，几乎总是可以更好地编写为单个“join”查询。 - Marc B

从@MarcB的评论中补充一下。你应该将嵌套查询识别为软件开发反模式。当你看到这种反模式时，你应该总是寻找另一种解决问题的方式。这并不是说总会有更好的方法，但正如注意到的那样，你几乎总能找到更好的方法。 - Mike Brant

在MySQL中，请考虑使用SET数据类型。 - Rick James

Answer 1

就效率而言，数据库查询是您可以执行的最慢操作之一。减少查询次数可以大大提高应用程序的速度。

但更重要的是，您的应用程序需要按设计工作，这意味着开发人员需要理解正在发生什么，数据不应该挂起等待被覆盖，3年后负责维护的初级开发人员不会想掉头发。

快速比缓慢好。

缓慢比损坏好。

针对您的特定问题，如果可能，请勿在循环内部进行查询。特别是当该循环由从同一数据库获取的数据控制时。这是一个代码异味，需要正确使用JOIN。

Google图像搜索SQL Join Diagrams显示了许多维恩图，展示了每个JOIN返回的不同类型的数据。如有疑问，通常需要使用LEFT JOIN。

因此，让我们确定您的关系：

• 病理学

  • 在你的结果中未使用。
  • 找到一种方法将其纳入你的查询中，因为“Pathology2”出现在你的模拟中。

• 流程

  • 在一对多的关系中引用病理学。每个流程可以有一个病理学，但每个病理学可以有0个或多个流程。

• 任务

  • 在一对多的关系中引用任务。任务包含流程的子项。

• 进度

  • 引用任务，以及未显示的案例和用户。当引用特定案例和用户的任务时，进度似乎是任务的详细信息。
  • 我假设有一个业务约束，即task_id、case_id和user_id必须是唯一的...也就是说，用户1只能为任务1和案例100有1个进度条目。
  • 除了保存任务的详细信息外，还作为Task、Case和User之间的桥梁，给这三个表提供了多对多的关系。由于任务是流程的直接子项，而流程是病理学的直接子项，所以它给病理学提供了多对多的关系。

• 案例

  • 推断存在此表。
  • 由任务引用。

• 用户

  • 推断存在此表。
  • 由任务引用。

基于这个表结构，我们的主要分组将是Case（案例）、Pathology（病理学）和User（用户）。

也就是说，如果您是一个已登录的用户，并且想要按案例查看自己的进展情况，您会想要看到以下内容：

Case 110:
    Pathology1:
        BigTask1:
            Task1: X
            Task2: []
        BigTask2:
            Task3: X
    Pathology2:
        BigTask3:
            Task4: []
Case 120:
    Pathology1:
        BigTask1: 
            Task1: []

我们需要用户ID == 1; 我们的第一次排序将基于Case 我们的第二次排序将基于Pathology 我们的第三次排序将基于Process 最后一次排序将基于Task... 因此，获取上述结果的数据将是：

+------+------------+----------+-------+----------+
| case | pathology  | process  | task  | progress |
+------+------------+----------+-------+----------+
| 110  | Pathology1 | BigTask1 | Task1 | 1        |
| 110  | Pathology1 | BigTask1 | Task2 | 0        |
| 110  | Pathology1 | BigTask2 | Task3 | 1        |
| 110  | Pathology2 | BigTask3 | Task4 | 0        |
| 120  | Pathology1 | BigTask1 | Task1 | 0        |
+------+------------+----------+-------+----------+

我们的“ORDER BY”子句是从后往前排列的... ORDER BY task, process, pathology, case... 我们可以在PHP中对其进行排序，但是数据库比我们更擅长这样做。如果索引设置正确，数据库甚至可能不需要对其进行排序，它将按顺序获取数据。

获取特定用户上述数据的查询如下：

SELECT
    prog.case_id AS case,
    path.name AS pathology,
    proc.name AS process,
    task.name AS task,
    prog.current AS progress
FROM
    pathology path
LEFT JOIN process proc ON path.id_pathology = proc.pathology_id
LEFT JOIN task ON task.process_id = proc.id_process
LEFT JOIN progress prog ON task.id_task = prog.task_id
WHERE prog.user_id = :userid
ORDER BY task, process, pathology, case

你的PHP代码可能是这样的

<?php

$sql = <<<EOSQL
SELECT
    prog.case_id AS case,
    path.name AS pathology,
    proc.name AS process,
    task.name AS task,
    prog.current AS progress
FROM
    pathology path
LEFT JOIN process proc ON path.id_pathology = proc.pathology_id
LEFT JOIN task ON task.process_id = proc.id_process
LEFT JOIN progress prog ON task.id_task = prog.task_id
WHERE prog.user_id = :userid
ORDER BY task, process, pathology, case
EOSQL;

$result = $db->prepare($sql);     
$result->execute(array(':userid' => $id));
$rows = $result->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $row) {
    var_dump($row);
    // array(5) {
    //     ["case"]=>
    //     int(110)
    //     ["pathology"]=>
    //     string(10) "Pathology1"
    //     ["process"]=>
    //     string(8) "BigTask1"
    //     ["task"]=>
    //     string(5) "Task1"
    //     ["progress"]=>
    //     int(1)
    // }
}

- Ghedipunk

---

如果您想直接显示结果集而不必将结果放入某种中间数据结构以允许在顶层按情况进行分组，则似乎应该将ORDER BY列顺序与您所展示的相反。 - Mike Brant

@MikeBrant 或许吧，我没有直接运行过这个程序，而且我已经有一段时间没有写 ORDER BY 子句了。我的理解是数据库首先按任务排序，然后按进程、病理和案例排序，使得同一个案例中的所有内容都在一起。如果我的假设是错误的，那么很容易反转列的顺序。 - Ghedipunk