如何防止API拉取数据存储时重复 仅更新新增记录忽略已存数据

从ClickMeeting API拉取会议数据存入本地数据库时，每次刷新页面都会重复写入已存在的相同数据，要求实现：仅写入新拉取的记录，已存储的旧记录直接忽略，或仅更新有变动的旧记录。

控制器代码（Controller） #

public function dashboard()
{
    $client = new Client();
    $uri = 'https://api.clickmeeting.com/v1/conferences/active';
    $header = ['headers' => ['X-Api-Key' => '123456']];
    $res = $client->get($uri, $header);
    $conferences = json_decode($res->getBody()->getContents(), true);

    collect($conferences)
        ->each(function ($conference, $key) {
            ClickMeeting::create([
                'conference_id' => $conference['id'],
                'parent_id' => $conference['parent_id'],
                'room_type' => $conference['room_type'],
                'room_pin' => $conference['room_pin'],
                'name' => $conference['name'],
                'name_url' => $conference['name_url'],
                'access_type' => $conference['access_type'],
                'lobby_enabled' => $conference['lobby_enabled'],
                'lobby_description' => $conference['lobby_description'],
                'registration_enabled' => $conference['registration_enabled'],
                'status' => $conference['status'],
                'timezone' => $conference['timezone'],
                'timezone_offset' => $conference['timezone_offset'],
                'paid_enabled' => $conference['paid_enabled'],
                'automated_enabled' => $conference['automated_enabled'],
                'type' => $conference['type'],
                'permanent_room' => $conference['permanent_room'],
                'room_url' => $conference['room_url'],
                'embed_room_url' => $conference['embed_room_url'],
         
            ]);
        });

    return view('admin.clickmeeting.dashboard');
}

迁移表结构（Migration Schema） #

public function up()
{
    Schema::create('clickmeeting', function (Blueprint $table) {
        $table->increments('id');
        $table->timestamps();
        $table->string('conference_id')->unique();
        $table->string('parent_id')->nullable();
        $table->string('room_type');
        $table->string('room_pin');
        $table->string('name');
        $table->string('name_url');
        $table->string('ends_at');
        $table->string('access_type');
        $table->string('lobby_enabled');
        $table->string('lobby_description');
        $table->string('registration_enabled');
        $table->string('status');
        $table->string('timezone');
        $table->string('timezone_offset');
        $table->string('paid_enabled');
        $table->string('automated_enabled');
        $table->string('type');
        $table->string('permanent_room');
        $table->string('room_url');
        $table->string('embed_room_url');
    });
}

重复写入的核心原因是循环中直接调用create()方法插入数据，没有做存在性校验。迁移文件中已经给conference_id字段加了唯一索引，直接用Laravel自带的ORM方法即可实现需求，不需要额外写复杂的判断逻辑。

方案1：单条记录校验（适合数据量小的场景） #

如果每次拉取的数据量不大，可以用updateOrCreate方法，方法第一个参数传唯一匹配条件，第二个参数传要写入的字段值：记录存在时更新对应字段，不存在时插入新记录。

collect($conferences)
    ->each(function ($conference) {
        ClickMeeting::updateOrCreate(
            // 用会议ID判断记录是否已存在
            ['conference_id' => $conference['id']],
            [
                'parent_id' => $conference['parent_id'],
                'room_type' => $conference['room_type'],
                'room_pin' => $conference['room_pin'],
                'name' => $conference['name'],
                'name_url' => $conference['name_url'],
                'access_type' => $conference['access_type'],
                'lobby_enabled' => $conference['lobby_enabled'],
                'lobby_description' => $conference['lobby_description'],
                'registration_enabled' => $conference['registration_enabled'],
                'status' => $conference['status'],
                'timezone' => $conference['timezone'],
                'timezone_offset' => $conference['timezone_offset'],
                'paid_enabled' => $conference['paid_enabled'],
                'automated_enabled' => $conference['automated_enabled'],
                'type' => $conference['type'],
                'permanent_room' => $conference['permanent_room'],
                'room_url' => $conference['room_url'],
                'embed_room_url' => $conference['embed_room_url'],
                // 注意：迁移表中存在ends_at字段，原代码漏传该字段，如果API返回该字段请补上，否则会触发数据库字段缺失错误
                // 'ends_at' => $conference['ends_at'],
            ]
        );
    });

如果需求是已存在的旧记录完全不更新，只插入全新记录，把逻辑改成先判断存在性再插入即可：

collect($conferences)
    ->each(function ($conference) {
        if (!ClickMeeting::where('conference_id', $conference['id'])->exists()) {
            ClickMeeting::create([
                'conference_id' => $conference['id'],
                'parent_id' => $conference['parent_id'],
                // 其余字段和原create逻辑一致，记得补全ends_at字段
                'room_type' => $conference['room_type'],
                'room_pin' => $conference['room_pin'],
                'name' => $conference['name'],
                'name_url' => $conference['name_url'],
                'access_type' => $conference['access_type'],
                'lobby_enabled' => $conference['lobby_enabled'],
                'lobby_description' => $conference['lobby_description'],
                'registration_enabled' => $conference['registration_enabled'],
                'status' => $conference['status'],
                'timezone' => $conference['timezone'],
                'timezone_offset' => $conference['timezone_offset'],
                'paid_enabled' => $conference['paid_enabled'],
                'automated_enabled' => $conference['automated_enabled'],
                'type' => $conference['type'],
                'permanent_room' => $conference['permanent_room'],
                'room_url' => $conference['room_url'],
                'embed_room_url' => $conference['embed_room_url'],
            ]);
        }
    });

方案2：批量处理（适合数据量大的场景，性能更优） #

单条循环会产生多次数据库查询，数据量大时性能较差，可以用upsert方法一次性批量处理所有数据，大幅减少数据库IO：

// 先整理好所有待写入的数据集
$insertData = collect($conferences)->map(function ($conference) {
    return [
        'conference_id' => $conference['id'],
        'parent_id' => $conference['parent_id'],
        'room_type' => $conference['room_type'],
        'room_pin' => $conference['room_pin'],
        'name' => $conference['name'],
        'name_url' => $conference['name_url'],
        'access_type' => $conference['access_type'],
        'lobby_enabled' => $conference['lobby_enabled'],
        'lobby_description' => $conference['lobby_description'],
        'registration_enabled' => $conference['registration_enabled'],
        'status' => $conference['status'],
        'timezone' => $conference['timezone'],
        'timezone_offset' => $conference['timezone_offset'],
        'paid_enabled' => $conference['paid_enabled'],
        'automated_enabled' => $conference['automated_enabled'],
        'type' => $conference['type'],
        'permanent_room' => $conference['permanent_room'],
        'room_url' => $conference['room_url'],
        'embed_room_url' => $conference['embed_room_url'],
        // 同样记得补全ends_at字段
        // 'ends_at' => $conference['ends_at'],
        'created_at' => now(),
        'updated_at' => now(),
    ];
})->toArray();

// 批量upsert参数说明：
// 1. 待写入的数据集
// 2. 唯一判断字段，用于匹配已存在的记录
// 3. 记录存在时需要更新的字段列表，如果不需要更新旧记录传空数组即可
ClickMeeting::upsert($insertData, ['conference_id'], [
    'parent_id', 'room_type', 'room_pin', 'name', 'name_url', 'access_type',
    'lobby_enabled', 'lobby_description', 'registration_enabled', 'status',
    'timezone', 'timezone_offset', 'paid_enabled', 'automated_enabled', 'type',
    'permanent_room', 'room_url', 'embed_room_url', 'updated_at'
]);

优化建议 #

不建议把外部API调用、数据同步逻辑放在页面访问的控制器方法中：用户每次刷新页面都会触发一次API请求，既拖慢页面加载速度，也容易因为API超时、限流导致页面报错。可以把这部分逻辑放到Laravel的任务调度或者队列中，按固定频率（比如每10分钟）同步一次数据即可。

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