PHP遍历JSON子数组并插入SQL表的技术求助

一、先理清楚数据结构 #

你给出的是RandomUser的API返回结构，核心是results数组里的每个用户对象，包含多层嵌套子数组。不用暴力建大表，按数据逻辑拆分表更合理。

二、数据库表设计（结构化存储） #

先建三张关联表，符合数据库范式，方便后续维护：

-- 用户主表：存基础信息
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    gender VARCHAR(10) NOT NULL,
    title VARCHAR(10),
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    dob_date DATETIME,
    dob_age INT,
    registered_date DATETIME,
    registered_age INT,
    phone VARCHAR(20),
    cell VARCHAR(20)
);

-- 用户地址表：关联用户ID，存地址相关的嵌套数据
CREATE TABLE user_locations (
    id INT IDENTITY(1,1) PRIMARY KEY,
    user_id INT NOT NULL FOREIGN KEY REFERENCES users(id),
    street_number INT,
    street_name VARCHAR(100),
    city VARCHAR(50),
    state VARCHAR(50),
    country VARCHAR(50),
    postcode VARCHAR(20),
    latitude DECIMAL(10,4),
    longitude DECIMAL(10,4),
    timezone_offset VARCHAR(10),
    timezone_description VARCHAR(100)
);

-- 用户图片表：关联用户ID，存头像链接
CREATE TABLE user_pictures (
    id INT IDENTITY(1,1) PRIMARY KEY,
    user_id INT NOT NULL FOREIGN KEY REFERENCES users(id),
    large_url VARCHAR(255),
    medium_url VARCHAR(255),
    thumbnail_url VARCHAR(255)
);

三、PHP代码实现 #

用SQLSRV扩展连接SQL Server，遍历JSON数组拆解数据，用预处理语句插入（防注入）：

// 1. 连接SQL Server
$serverName = "你的服务器名";
$connectionOptions = [
    "Database" => "你的数据库名",
    "Uid" => "用户名",
    "PWD" => "密码"
];
$conn = sqlsrv_connect($serverName, $connectionOptions);
if (!$conn) {
    die(print_r(sqlsrv_errors(), true));
}

// 2. 解析JSON文件（假设JSON存在本地文件，也可以是API响应）
$jsonContent = file_get_contents('users.json');
$data = json_decode($jsonContent, true);

// 3. 遍历每个用户数据
foreach ($data['results'] as $user) {
    // 拆解基础信息
    $gender = $user['gender'];
    $title = $user['name']['title'];
    $firstName = $user['name']['first'];
    $lastName = $user['name']['last'];
    $email = $user['email'];
    $dobDate = $user['dob']['date'];
    $dobAge = $user['dob']['age'];
    $registeredDate = $user['registered']['date'];
    $registeredAge = $user['registered']['age'];
    $phone = $user['phone'];
    $cell = $user['cell'];

    // 拆解地址信息
    $streetNumber = $user['location']['street']['number'];
    $streetName = $user['location']['street']['name'];
    $city = $user['location']['city'];
    $state = $user['location']['state'];
    $country = $user['location']['country'];
    $postcode = $user['location']['postcode'];
    $latitude = $user['location']['coordinates']['latitude'];
    $longitude = $user['location']['coordinates']['longitude'];
    $timezoneOffset = $user['location']['timezone']['offset'];
    $timezoneDesc = $user['location']['timezone']['description'];

    // 拆解图片信息
    $picLarge = $user['picture']['large'];
    $picMedium = $user['picture']['medium'];
    $picThumb = $user['picture']['thumbnail'];

    // 4. 插入主用户表，用预处理语句防注入
    $userSql = "INSERT INTO users (gender, title, first_name, last_name, email, dob_date, dob_age, registered_date, registered_age, phone, cell)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    $userParams = [$gender, $title, $firstName, $lastName, $email, $dobDate, $dobAge, $registeredDate, $registeredAge, $phone, $cell];
    $stmt = sqlsrv_query($conn, $userSql, $userParams);
    if (!$stmt) {
        die(print_r(sqlsrv_errors(), true));
    }

    // 获取刚插入的用户ID，用于关联子表
    $userId = sqlsrv_get_last_insert_id($stmt);

    // 5. 插入地址表
    $locationSql = "INSERT INTO user_locations (user_id, street_number, street_name, city, state, country, postcode, latitude, longitude, timezone_offset, timezone_description)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    $locationParams = [$userId, $streetNumber, $streetName, $city, $state, $country, $postcode, $latitude, $longitude, $timezoneOffset, $timezoneDesc];
    $stmt = sqlsrv_query($conn, $locationSql, $locationParams);
    if (!$stmt) {
        die(print_r(sqlsrv_errors(), true));
    }

    // 6. 插入图片表
    $pictureSql = "INSERT INTO user_pictures (user_id, large_url, medium_url, thumbnail_url)
                   VALUES (?, ?, ?, ?)";
    $pictureParams = [$userId, $picLarge, $picMedium, $picThumb];
    $stmt = sqlsrv_query($conn, $pictureSql, $pictureParams);
    if (!$stmt) {
        die(print_r(sqlsrv_errors(), true));
    }
}

// 关闭连接
sqlsrv_close($conn);

四、简化方案（不想分表） #

如果觉得分表麻烦，SQL Server 2016及以上支持JSON类型，可以把嵌套数组直接转成JSON字符串存在主表字段里：

// 把location、picture等嵌套数组转成JSON
$locationJson = json_encode($user['location']);
$pictureJson = json_encode($user['picture']);

// 主表新增location_json、picture_json字段（类型为JSON或NVARCHAR(MAX)）
$userSql = "INSERT INTO users (gender, title, first_name, last_name, email, dob_date, dob_age, registered_date, registered_age, phone, cell, location_json, picture_json)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
// 对应的参数加上$locationJson和$pictureJson即可

内容的提问来源于stack exchange，提问作者imtryingman