Laravel技术咨询：迁移本地phpMyAdmin创建的表及批量数据至服务器

Hey there! I’ve been in your exact situation before—tinkering with a Laravel project where I had to retroactively add migrations for tables I built manually. Let’s break down your two questions clearly:

1. How to Migrate Tables Created in phpMyAdmin? #

You’ll need to translate your manual table structures into Laravel migration files so the migrate command can recreate them on the server. Here’s the step-by-step:

• Generate empty migration files
  Run these Artisan commands in your project root to create base migration files for your tables:

  php artisan make:migration create_countries_table
  php artisan make:migration create_regions_table
  

• Replicate your table structure in the migration files
  Open the new migration files in database/migrations/. Look at your phpMyAdmin table details (field names, data types, lengths, primary keys, foreign keys, indexes) and translate them to Laravel’s Schema builder syntax.
  For example, a countries table might look like this:

  public function up()
  {
      Schema::create('countries', function (Blueprint $table) {
          $table->id();
          $table->string('name', 100)->unique();
          $table->char('iso_code', 2)->unique();
          $table->timestamps();
      });
  }
  

  And for regions (with a foreign key to countries):

  public function up()
  {
      Schema::create('regions', function (Blueprint $table) {
          $table->id();
          $table->foreignId('country_id')->constrained()->onDelete('cascade');
          $table->string('name', 150);
          $table->string('code', 10)->nullable();
          $table->timestamps();
          
          // Add any indexes you have in phpMyAdmin
          $table->index(['country_id', 'name']);
      });
  }
  

• Validate the migration locally
  Before deploying, run php artisan migrate:fresh (warning: this will wipe all local data—backup first!) to confirm the migration recreates your tables exactly as they were in phpMyAdmin. Fix any discrepancies until the structures match.

2. How to Sync Large Amounts of Data from countries and regions? #

For bulk data sync, Laravel seeders are the way to go. Here’s how to handle large datasets efficiently:

• Create seeder files
  Generate seeder classes for each table:

  php artisan make:seeder CountrySeeder
  php artisan make:seeder RegionSeeder
  

• Export your local data
  In phpMyAdmin, export your countries and regions tables as a JSON or CSV file. Save these files in your project (e.g., storage/app/seeds/).

• Write the seeder logic
  Open the seeder files and add code to read your exported data and insert it into the database. For large datasets, use chunked inserts to avoid memory issues:

  Example for CountrySeeder:

  public function run()
  {
      // Read JSON data (adjust path to your file)
      $jsonData = Storage::get('seeds/countries.json');
      $countries = json_decode($jsonData, true);
  
      // Insert in chunks of 1000 records at a time
      foreach (array_chunk($countries, 1000) as $chunk) {
          DB::table('countries')->insert($chunk);
      }
  }
  

  For RegionSeeder, follow the same pattern—just make sure the country_id values match the seeded countries.

• Run the seeders
  On your server (after running migrations), execute:

  php artisan db:seed --class=CountrySeeder
  php artisan db:seed --class=RegionSeeder
  

  Or if you’ve added these seeders to DatabaseSeeder.php’s run() method, you can run all seeders with php artisan db:seed.

Pro Tips: #

• For extra large datasets (100k+ rows), consider using Laravel’s insertUsing() or raw SQL inserts for even better performance.
• Always test the migration and seeding process on a staging server first before deploying to production.
• If you’re worried about duplicate data, add ->ignore() to your insert calls (Laravel 8+) or use updateOrInsert() if you need to sync changes later.

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