如何在Laravel中实现跨数据库表posts到buss_posts的数据迁移

Hey there! Let's break down how to handle this cross-database data migration in your Laravel controller step by step—super straightforward once you know the setup.

First, you need to tell Laravel about your second database. Open up config/database.php and add a new entry to the connections array (copy your existing MySQL config and tweak the details):

'connections' => [
    // ... 你的主数据库配置（比如默认的mysql）

    'buss_db' => [
        'driver' => 'mysql',
        'host' => env('BUSS_DB_HOST', '127.0.0.1'),
        'port' => env('BUSS_DB_PORT', '3306'),
        'database' => env('BUSS_DB_DATABASE', 'your_second_db_name'),
        'username' => env('BUSS_DB_USERNAME', 'db_user'),
        'password' => env('BUSS_DB_PASSWORD', 'db_password'),
        'unix_socket' => env('DB_SOCKET', ''),
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
        'prefix' => '',
        'prefix_indexes' => true,
        'strict' => true,
        'engine' => null,
    ],
],

Then update your .env file to add the new database credentials:

BUSS_DB_HOST=127.0.0.1
BUSS_DB_PORT=3306
BUSS_DB_DATABASE=your_second_db_name
BUSS_DB_USERNAME=your_db_user
BUSS_DB_PASSWORD=your_db_password

Using models makes the code cleaner and more maintainable. Create two models:

1. For your original posts table (uses the default database connection):

// app/Models/Post.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    protected $table = 'posts';
    // 允许批量赋值的字段
    protected $fillable = ['post_name', 'post_content', 'created_at', 'updated_at'];
}

2. For the buss_posts table (specifies the second database connection):

// app/Models/BussPost.php
namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class BussPost extends Model
{
    // 指定使用我们刚才配置的第二个连接
    protected $connection = 'buss_db';
    protected $table = 'buss_posts';
    protected $fillable = ['post_name', 'post_content', 'created_at', 'updated_at'];
}

Now let's write the migration logic in your BlogController. We'll use chunk() to process data in batches (prevents memory issues with large datasets):

class BlogController extends Controller {
    public function run() {
        // 批量读取主数据库的posts表数据，每次处理100条
        \App\Models\Post::chunk(100, function ($posts) {
            // 转换为适合批量插入的数组
            $migrateData = $posts->map(function ($post) {
                return [
                    'post_name' => $post->post_name,
                    'post_content' => $post->post_content,
                    'created_at' => $post->created_at, // 保留原创建时间
                    'updated_at' => $post->updated_at, // 保留原更新时间
                ];
            })->toArray();

            // 批量插入到第二个数据库的buss_posts表
            \App\Models\BussPost::insert($migrateData);
        });

        // 如果不需要保留原时间戳，可以简化成：
        // \App\Models\Post::chunk(100, function ($posts) {
        //     \App\Models\BussPost::insert($posts->only(['post_name', 'post_content'])->toArray());
        // });

        return response()->json(['status' => 'success', 'message' => '数据迁移完成！']);
    }
}

不用模型？直接用DB门面也可以 #

If you prefer to skip models, you can switch connections directly with the DB facade:

public function run() {
    DB::table('posts')->chunk(100, function ($posts) {
        $data = [];
        foreach ($posts as $post) {
            $data[] = [
                'post_name' => $post->post_name,
                'post_content' => $post->post_content,
                'created_at' => $post->created_at,
                'updated_at' => $post->updated_at,
            ];
        }
        // 切换到第二个数据库执行插入
        DB::connection('buss_db')->table('buss_posts')->insert($data);
    });

    return response()->json(['status' => 'success', 'message' => '数据迁移完成！']);
}

• 权限检查: Make sure the database users for both connections have read/write access to their respective databases.
• 大数据集: If you're migrating thousands of records, consider creating an Artisan command instead of using a controller—HTTP requests can time out easily.
• 重复数据: If you might run this migration multiple times, add a unique constraint (e.g., on post_name) or use updateOrInsert() instead of insert() to avoid duplicates.
• 先测试: Run a test with a small subset of data (e.g., Post::take(10)->chunk(...)) to verify everything works before migrating all records.

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