php - 嵌入式数据集的 mySQL INSERT 和 SELECT 查询

Question

我正在尝试使用此 JSON 数据编写一个高效的 mySQL INSERT 查询(为了清楚起见，已转换为 JSON):

[
    {
        "job": "Avocado Books Binding",
        "tasks": [
            "Finish collating the signatures",
            "Fix perfect binders",
            "Fold signatures"
        ]
    },
    {
        "job": "Prepress Workflow Development",
        "tasks": [
            "Research new proofing machines",
            "Find free proofing software"
        ]
    }
]

生成的表结构将是(包含此数据)

Table: Jobs
job_id   job_title
1        Avocado Books Binding
2        Prepress Workflow Development

Table: Tasks
task_id   task_description                    job_id
1         Finish collating the signatures     1
2         Fix perfect binders                 1
3         Fold signatures                     1
4         Research new proofing machines      2
5         Find free proofing software         2

作业和任务通过 job_id 关联。

我还尝试编写一个 SELECT 查询，以高效的方式获取此数据。

非常感谢任何帮助、引用或建议。

更新:

PHP 数组的结构

Array
(
    [0] => Array
        (
            [job] => Avocado Books Binding
            [tasks] => Array
                (
                    [0] => Finish collating the signatures
                    [1] => Fix perfect binders
                    [2] => Fold signatures
                )

        )

    [1] => Array
        (
            [job] => Avocado Books Binding
            [tasks] => Array
                (
                    [0] => Finish collating the signatures
                    [1] => Fix perfect binders
                    [2] => Fold signatures
                )

        )

)

Answer 1

如果禁用 PDO 的准备语句模拟，则重复执行此类(服务器端准备好的)语句将比多个未准备语句高效得多:

$dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

$insert_job = $dbh->prepare('
  INSERT INTO Jobs (job_title) VALUES (?)
');

$insert_task = $dbh->prepare('
  INSERT INTO Tasks (task_description, job_id) VALUES (:desc, :id)
');

$insert_task->bindParam(':desc', $task  , PDO::PARAM_STR);
$insert_task->bindParam(':id',   $job_id, PDO::PARAM_INT);

foreach (json_decode($json, TRUE) as $job) {
  $insert_job->execute([$job['job']]);
  $job_id = $insert_job->lastInsertId();
  foreach ($job['tasks'] as $task) $insert_task->execute();
}