如何使用cURL获取分页API全部内容并批量写入MySQL数据库

你的代码仅拉取第一页数据的核心原因是只发起了一次API请求，没有循环遍历1~418的所有页码，按以下步骤修改即可：

前提确认 #

首先确认目标API的分页传参规则，绝大多数分页API使用page参数指定页码，例如第3页的请求地址为http://exampledomain.com/api/properties?page=3，如果你的API使用其他参数名（比如p、pageIndex），对应替换即可。

修改后的完整data.php代码 #

<?php
include_once './config/Database.php';

$baseUrl = "http://exampledomain.com/api/properties";
$headers = array(
    'Content-type: application/json',
    'API_KEY: 2S7rhsaq9X1cnfkMCPHX64YsWYyfe1he',
);
$totalPage = 418;

// 预编译SQL仅执行一次，提升性能
$query = "INSERT INTO details (
            county, country, town, description, displayable_address,
            image, thumbnail, latitude, longitude, bedrooms,
            bathrooms, price, type
        ) VALUES (
            :county, :country, :town, :description, :address,
            :image_full, :image_thumbnail, :latitude, :longitude, :num_bedrooms,
            :num_bathrooms, :price, :type
        )";
$stmt = $conn->prepare($query);

// 绑定参数仅执行一次
$stmt->bindParam(':county', $county);
$stmt->bindParam(':country', $country);
$stmt->bindParam(':town', $town);
$stmt->bindParam(':description', $description);
$stmt->bindParam(':address', $displayable_address);
$stmt->bindParam(':image_full', $image);
$stmt->bindParam(':image_thumbnail', $thumbnail);
$stmt->bindParam(':latitude', $latitude);
$stmt->bindParam(':longitude', $longitude);
$stmt->bindParam(':num_bedrooms', $bedrooms);
$stmt->bindParam(':num_bathrooms', $bathrooms);
$stmt->bindParam(':price', $price);
$stmt->bindParam(':type', $type);

// 开启事务，批量提交提升写入性能
$conn->beginTransaction();

try {
    // 循环遍历所有页码
    for ($page = 1; $page <= $totalPage; $page++) {
        // 拼接当前页请求地址
        $currentUrl = $baseUrl . "?page=" . $page;
        $ch = curl_init($currentUrl);
        curl_setopt($ch, CURLOPT_URL, $currentUrl);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
        $resp = curl_exec($ch);

        if (curl_error($ch)) {
            throw new Exception("第{$page}页请求失败: " . curl_error($ch));
        }

        $decoded = json_decode($resp, true);
        // 校验返回数据是否正常
        if (!isset($decoded['data']) || empty($decoded['data'])) {
            echo "第{$page}页无有效数据，跳过\n";
            curl_close($ch);
            continue;
        }

        // 插入当前页所有数据
        foreach ($decoded["data"] as $decode) {
            $county = $decode['county'];
            $country = $decode['country'];
            $town = $decode['town'];
            $description = $decode['description'];
            $displayable_address = $decode['address'];
            $image = $decode['image_full'];
            $thumbnail = $decode['image_thumbnail'];
            $latitude = $decode['latitude'];
            $longitude = $decode['longitude'];
            $bedrooms = $decode['num_bedrooms'];
            $bathrooms = $decode['num_bathrooms'];
            $price = $decode['price'];
            $type = $decode['type'];
            
            $stmt->execute();
        }

        curl_close($ch);
        echo "第{$page}页数据写入完成\n";
        
        // 防止请求频率过高触发API限流，可根据API规则调整或删除
        sleep(1);
    }

    // 所有页处理完成后提交事务
    $conn->commit();
    echo "全部418页数据写入成功";
} catch (Exception $e) {
    // 出现错误回滚事务，避免脏数据
    $conn->rollBack();
    die("执行失败: " . $e->getMessage());
}

可选优化点 #

• 如果API支持自定义单页返回条数，可以在请求地址中加入per_page=100（数值根据API上限调整），减少总请求次数
• 可以加入失败重试逻辑，避免单次网络波动导致整个任务中断
• 如果数据量极大，可以每处理10页提交一次事务，避免事务占用过多内存

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