Laravel中如何跨服务器执行REPLACE INTO本地库表SELECT外部库表操作

方案1：同MySQL实例可跨库访问时直接执行原生SQL #

如果你的两个数据库部署在同一个MySQL实例，且主库连接账号拥有extDB的读取权限，可以直接执行原生SQL完成操作，性能最高全程在数据库层完成，无PHP层数据传输开销：

DB::statement("REPLACE INTO mainDB.images (id, created_at, updated_at, is_active, rating, title, caption, summary, location, subregion, region, country_id, gps, keywords, author, note, date, camera_body, camera_lens, camera_focal, camera_iso, camera_speed, camera_aperture) 
SELECT Image_ID, Date_Created, Date_Modified, Mark_Active, TotalStars, ImageTitle, Image, ImageNewTitle, Location, SubRegion, Region, Country_ID, LocationGPS, Keywords, Author, Note, Image_Date, Camera_Body, Camera_Lens, Camera_Focale, Camera_ISO, Camera_Speed, Camera_Aperture 
FROM extDB.GW_xImage 
WHERE Date_Modified > NOW()");

方案2：跨服务器部署无法直接跨库时的分步实现 #

如果两个数据库在不同服务器无法直接跨库查询，可以分块拉取外部库数据再批量写入主库，用Laravel 8自带的upsert方法实现和REPLACE等价的效果，同时避免大数据量下内存溢出：

// 定义字段映射，后续调整字段时只需修改此处
$fieldMapping = [
    'Image_ID' => 'id',
    'Date_Created' => 'created_at',
    'Date_Modified' => 'updated_at',
    'Mark_Active' => 'is_active',
    'TotalStars' => 'rating',
    'ImageTitle' => 'title',
    'Image' => 'caption',
    'ImageNewTitle' => 'summary',
    'Location' => 'location',
    'SubRegion' => 'subregion',
    'Region' => 'region',
    'Country_ID' => 'country_id',
    'LocationGPS' => 'gps',
    'Keywords' => 'keywords',
    'Author' => 'author',
    'Note' => 'note',
    'Image_Date' => 'date',
    'Camera_Body' => 'camera_body',
    'Camera_Lens' => 'camera_lens',
    'Camera_Focale' => 'camera_focal',
    'Camera_ISO' => 'camera_iso',
    'Camera_Speed' => 'camera_speed',
    'Camera_Aperture' => 'camera_aperture',
];

$extSelectFields = array_keys($fieldMapping);
$mainTableFields = array_values($fieldMapping);

// 分块查询外部库数据，每次处理200条，可根据实际情况调整块大小
DB::connection('mysql_external')
    ->table('GW_xImage')
    ->select($extSelectFields)
    ->where('Date_Modified', '>', now())
    ->chunkById(200, function ($imageBatch) use ($mainTableFields) {
        $insertRows = $imageBatch->map(function ($image) use ($mainTableFields) {
            return array_combine($mainTableFields, (array)$image);
        })->toArray();

        // 批量写入，id冲突时自动更新对应字段
        DB::table('images')->upsert(
            $insertRows,
            ['id'],
            $mainTableFields
        );
    }, 'Image_ID');

如果需要处理更大的数据量，可以适当调高分块数值，或者改用队列异步处理同步任务。

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