php - 使用 php 将嵌套的 JSON 导入具有关系的 MySQL

Question

我正在寻找一种简单的解决方法来将现有的嵌套 JSON 数据导入到多个 MySQL 表中。 JSON 没有双向关系，所以我认为它们应该自动生成。

这是一个数据示例:

[
    {
        "targetgroup": "Staff",
        "plan": "this field just exists and should be ignored in database",
        "budgetlevel": "Government",
        "spots": 5,
        "edutype": "Bachelor",
        "qualilevel": "Specialist",
        "speciality": "Mathematician",
        "qualification": "Finished",
        "faculty": "Applied mathematics",
        "institute": "this field is sometimes empty in input data",
        "eduform": "Full-time",
        "profiles": [
            "Jr. Arithmetic manager"
        ],
        "entrancetests": [
            {
                "subject": "math",
                "typeoftest": "GOV",
                "minscore": "37",
                "ratingtype": "out of 100"
            },
            {
                "subject": "language",
                "typeoftest": "GOV",
                "minscore": "27",
                "ratingtype": "out of 100"
            },
            {
                "subject": "physics",
                "typeoftest": "GOV",
                "minscore": "40",
                "ratingtype": "out of 100"
            }
        ]
    },
  {
        "targetgroup": "Educational workers",
        "plan": "fridge",
        "budgetlevel": "Legacy",
        "spots": 26,
        "edutype": "Bachelor",
        "qualilevel": "Master",
        "speciality": "Data analysis",
        "qualification": "Finished",
        "faculty": "Machine learning mathematics",
        "institute": "",
        "eduform": "Full-time",
        "profiles": [
            "Head counting manager"
        ],
        "entrancetests": [
            {
                "subject": "Discrete mathematics",
                "typeoftest": "GOV",
                "minscore": "32",
                "ratingtype": "Out of 100"
            },
            {
                "subject": "Algorythm theory",
                "typeoftest": "GOV",
                "minscore": "51",
                "ratingtype": "Out of 100"
            },
            {
                "subject": "Advanced exception catching",
                "typeoftest": "GOV",
                "minscore": "56",
                "ratingtype": "Out of 100"
            }
        ]
    }
]

数据库结构:

表“部门:”

id(自增) |目标群体 |预算水平 | Blob | edutype ...等，与JSON字段名称相同

表“配置文件”

id(相对于相应的父 block )|姓名

表“入口测试”:

id(相对于相应的父 block )|主题 |测试类型 |分钟得分 |评分类型

我对如何导入非嵌套 JSON 有一个大致的了解，但我很难弄清楚如何添加关系，如何在循环内定义父 block ？

Answer 1

以下是针对您的具体情况的功能齐全的事务性 pdo 流程。我已经在我的服务器上测试了 3 个与您的结构匹配的 MyISAM 表。现在，您现在可能不喜欢 pdo；如果没有，我希望我的产品会迫使您尝试新事物并使您查询数据库的方式现代化。

$json='[
    {
        "targetgroup": "Staff",
        "plan": "this field just exists and should be ignored in database",
        "budgetlevel": "Government",
        "spots": 5,
        "edutype": "Bachelor",
        "qualilevel": "Specialist",
        "speciality": "Mathematician",
        "qualification": "Finished",
        "faculty": "Applied mathematics",
        "institute": "this field is sometimes empty in input data",
        "eduform": "Full-time",
        "profiles": [
            "Jr. Arithmetic manager"
        ],
        "entrancetests": [
            {
                "subject": "math",
                "typeoftest": "GOV",
                "minscore": "37",
                "ratingtype": "out of 100"
            },
            {
                "subject": "language",
                "typeoftest": "GOV",
                "minscore": "27",
                "ratingtype": "out of 100"
            },
            {
                "subject": "physics",
                "typeoftest": "GOV",
                "minscore": "40",
                "ratingtype": "out of 100"
            }
        ]
    },
  {
        "targetgroup": "Educational workers",
        "plan": "fridge",
        "budgetlevel": "Legacy",
        "spots": 26,
        "edutype": "Bachelor",
        "qualilevel": "Master",
        "speciality": "Data analysis",
        "qualification": "Finished",
        "faculty": "Machine learning mathematics",
        "institute": "",
        "eduform": "Full-time",
        "profiles": [
            "Head counting manager"
        ],
        "entrancetests": [
            {
                "subject": "Discrete mathematics",
                "typeoftest": "GOV",
                "minscore": "32",
                "ratingtype": "Out of 100"
            },
            {
                "subject": "Algorythm theory",
                "typeoftest": "GOV",
                "minscore": "51",
                "ratingtype": "Out of 100"
            },
            {
                "subject": "Advanced exception catching",
                "typeoftest": "GOV",
                "minscore": "56",
                "ratingtype": "Out of 100"
            }
        ]
    }
]';


$db=new PDO("mysql:host=yourhost;dbname=yourdbname;charset=utf8","username","password");

try{
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);  // this will deny subsequent queries from being executed if there is an error and permit exception handle at the bottom
    $db->beginTransaction();

    // dep
    $dep_cols=array("targetgroup","budgetlevel","spots",
        "edutype","qualilevel","speciality","qualification",
        "faculty","institute","eduform");  // declare columns
    $dep_keys=array_map(function($v){return ":$v";},$dep_cols);  // build :keys    
    $dep_cols=array_combine($dep_keys,$dep_cols);   // assign :keys
    var_export($dep_cols);
    $dep_query="INSERT INTO `dep` (`".implode('`,`',$dep_cols)."`)"; // list columns as csv
    $dep_query.=" VALUES (".implode(',',array_keys($dep_cols)).");";
    echo "<div>$dep_query</div>";
    $stmt_add_dep=$db->prepare($dep_query);

    // profile
    $profile_cols=array('name');
    $profile_query="INSERT INTO `profile` (`id`,`".implode('`,`',$profile_cols)."`)"; // list columns as csv
    $profile_query.=" VALUES (LAST_INSERT_ID(),".implode(',',array_fill(0,sizeof($profile_cols),"?")).");";
    echo "<div>$profile_query</div>";
    
    // entrancetests
    $entrance_cols=array('subject','typeoftest','minscore','ratingtype');  // declare columns
    $entrance_keys=array_map(function($v){return ":$v";},$entrance_cols);  // build :keys
    $entrance_cols=array_combine($entrance_keys,$entrance_cols);  // assign :keys    
    var_export($entrance_cols);
    $entrance_query="INSERT INTO `entrancetests` (`id`,`".implode('`,`',$entrance_cols)."`)"; // list columns as csv
    $entrance_query.=" VALUES (LAST_INSERT_ID(),".implode(',',array_keys($entrance_cols)).");";
    echo "<div>$entrance_query</div>";
    $stmt_add_entrance=$db->prepare($entrance_query);
        
    foreach(json_decode($json) as $d){
        foreach($dep_cols as $k=>$v){
            $stmt_add_dep->bindValue($k,(property_exists($d,$v)?$d->$v:""));
            echo "<div>$k => {$d->$v}</div>";
        }
        $stmt_add_dep->execute();
        echo "<div>Dep Affected Rows: ",$stmt_add_dep->rowCount(),"</div><br>";
        
        $stmt_add_profile=$db->prepare($profile_query);
        foreach($d->profiles as $k=>$v){
            $stmt_add_profile->bindValue($k+1,$v);
            echo "<div>",$k+1," => $v</div>";
        }
        $stmt_add_profile->execute();
        echo "<div>Profile Affected Rows: ",$stmt_add_profile->rowCount(),"</div><br>";
        
        foreach($d->entrancetests as $o){
            foreach($entrance_cols as $k=>$v){
                $stmt_add_entrance->bindValue($k,(property_exists($o,$v)?$o->$v:""));
                echo "<div>$k => {$o->$v}</div>";
            }
        }
        $stmt_add_entrance->execute();
        echo "<div>Entrance Affected Rows: ",$stmt_add_entrance->rowCount(),"</div><br>";
    }
    
    // $db->commit();  // Only use with InnoDB tables.  MyISAM is auto-commit
    
}
catch(PDOException $e){
    // $db->rollBack();  // Only works if InnoDB table.  If MyISAM table, it doesn't rollback.
    echo "Error message: {$e->getMessage()}. File: {$e->getFile()}. Line: {$e->getLine()}";
    // do not show these error messages to users when you go live
}

照原样，如果查询有错误，此脚本将停止执行后续查询。