laravel9數據填充 · Laravel

# **Laravel9.2 數據填充** ##### <blockquote class="danger"><p>數據填充：編寫seeder+工廠填充數據</p></blockquote> **需求背景：目前我有三張表：** 表1：fq_push_tasks1 任務表 ``` CREATE TABLE `fq_push_tasks1` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '推送名稱', `push_number` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '推送編號', `push_task_type` tinyint NOT NULL DEFAULT '1' COMMENT '推送類型（1:定時消息 2：延遲消息）', `country_code` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '推送國家', `receive_type` tinyint NOT NULL DEFAULT '1' COMMENT '接收用戶類型：1：全量用戶 2：標簽用戶', `send_time` timestamp NULL DEFAULT NULL COMMENT '定時消息（必填）：發送時間', `push_trigger_type` tinyint NOT NULL DEFAULT '0' COMMENT '延遲消息（必填）：觸發事件（默認0），1：用戶新增 2：充值 3：關閉充值彈窗 4：支付失敗', `delay_time` varchar(31) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '延遲消息（必填）：延遲時間（單位s）', `cool_time` int NOT NULL DEFAULT '0' COMMENT '延遲消息（必填）：冷卻時間', `push_position` tinyint NOT NULL DEFAULT '0' COMMENT '推送位置 0全部 1僅push 2僅inbox', `push_scene` tinyint NOT NULL DEFAULT '0' COMMENT '推送場景 0 默認推送，1 新書推薦', `app_ids` varchar(63) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '標簽用戶（必填）：APPID', `system` tinyint NOT NULL DEFAULT '0' COMMENT '標簽用戶（必填）：系統 1:安卓 2：IOS', `last_uid_digits` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '標簽用戶（必填）：最后一個Uid尾數', `ab_id` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '關聯的ab實驗id', `link_book_id` varchar(511) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '標簽用戶（必填）：歸因書籍', `read_book_id` varchar(511) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '標簽用戶（必填）：閱讀過的書', `create_to_send_time` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '標簽用戶（必填）：新增距今天數（位數代表運算類型，首為數字代表非范圍類型）', `active_to_send_time` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '標簽用戶（必填）：活躍距今天數（位數代表運算類型，首為數字代表非范圍類型）', `total_recharge_nums` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '標簽用戶（必填）：累計充值次數（位數代表運算類型，首為數字代表非范圍類型）', `total_recharge_money` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '標簽用戶（必填）：累計充值金額（位數代表運算類型，首為數字代表非范圍類型）', `total_active_days` varchar(125) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '標簽用戶（必填）：累計活躍天數（位數代表運算類型，首為數字代表非范圍類型）', `send_time_type` tinyint NOT NULL DEFAULT '1' COMMENT '推送時間類型 1:UTC，2:各國時區', `push_priority` int NOT NULL DEFAULT '0' COMMENT '推送優先級', `status` tinyint NOT NULL DEFAULT '1' COMMENT '發送狀態 1. 待發送 2. 發送中 3. 發送成功 4. 發送取消 5. 發送失敗（默認1：必填）', `admin_id` int unsigned NOT NULL DEFAULT '0' COMMENT '創建的管理員ID', `is_deleted` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否刪除 1 是 0 否', `deleted_at` timestamp NOT NULL COMMENT '刪除時間', `last_stop_at` timestamp NOT NULL COMMENT '最后一次停止時間', `is_warm_up` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否開始預熱 0 否 1 是', `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間', `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間', PRIMARY KEY (`id`), KEY `idx_system` (`system`), KEY `idx_name` (`name`) USING BTREE, KEY `idx_push_number` (`push_number`) USING BTREE, KEY `idx_push_task_type` (`push_task_type`) USING BTREE, KEY `idx_push_trigger_type` (`push_trigger_type`) USING BTREE, KEY `idx_push_position` (`push_position`) USING BTREE, KEY `idx_push_scene` (`push_scene`) USING BTREE, KEY `idx_status` (`status`) USING BTREE, KEY `idx_is_deleted` (`is_deleted`) USING BTREE, KEY `idx_created_at` (`created_at`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=812 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='推送消息任務主表'; ``` 表2：fq_push_task_apps1 任務關聯的app表（1個任務對應多條記錄） ``` CREATE TABLE `fq_push_task_apps1` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `task_id` int NOT NULL DEFAULT '0' COMMENT '推送任務ID', `app_id` int NOT NULL DEFAULT '0' COMMENT 'appId', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_task_id` (`task_id`) USING BTREE, KEY `idx_app_id` (`app_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='推送消息-任務關聯app表'; ``` 表三：fq_push_task_statistics1 推送消息-任務維度統計表（1個任務對應1條記錄） ``` CREATE TABLE `fq_push_task_statistics1` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `task_id` int NOT NULL DEFAULT '0' COMMENT '推送任務ID', `push_type` int NOT NULL DEFAULT '0' COMMENT '發送類型', `is_auto` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '自動推送1是2否', `send_all` int NOT NULL DEFAULT '0' COMMENT '發送人數', `send_success` int NOT NULL DEFAULT '0' COMMENT '發送成功數', `click_num` int NOT NULL DEFAULT '0' COMMENT '點擊數', `recharge_user_num` int NOT NULL DEFAULT '0' COMMENT '充值總人數', `order_num` int NOT NULL DEFAULT '0' COMMENT '訂單總數', `order_price` int NOT NULL DEFAULT '0' COMMENT '充值總金額（普通充值，非訂閱）', `total_recharge` int NOT NULL DEFAULT '0' COMMENT '總充值金額', `sub_coin_num` int NOT NULL DEFAULT '0' COMMENT '訂閱金幣包人數【普通訂閱（普通月卡） + 復充訂閱（復充月卡）】', `sub_coin_price` int NOT NULL DEFAULT '0' COMMENT '訂閱金幣包金額【普通訂閱（普通月卡） + 復充訂閱（復充月卡）】', `sub_time_num` int NOT NULL DEFAULT '0' COMMENT '訂閱年月周日的人數（包周+買斷）', `sub_time_money` int NOT NULL DEFAULT '0' COMMENT '訂閱年月周日的金額（包周+買斷）', `send_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '發送時間', `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_send_time` (`send_time`), KEY `idx_push_type` (`push_type`), KEY `idx_auto` (`is_auto`), KEY `idx_task_id` (`task_id`) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='推送消息-任務維度統計表'; ``` **需求目標：由于要測試查詢效率，因此要填充大量數據，且填充的數據要支持關聯填充** ----------------------------------------- **開始實現** * [ ] 執行流程分為三步 * step1：分別講上述三個表先創建模型，在模型中指定關聯關系 * step2：分別創建fq_push_tasks1（PushTaskFactory）、fq_push_task_statistics1（PushTaskStatisticFactory）的工廠類 fq_push_tasks1工廠類創建命令：```php artisan make:factory PushTaskFactory --model=PushTask``` fq_push_task_statistics1工廠類創建命令：```php artisan make:factory PushTaskStatisticFactory --model=PushTaskStatistic``` 備注：PushTaskFactory：工廠名稱，PushTask：模型名稱，另外模型中必須引用工廠類： <span style="color:red">use HasFactory; </span>，且目錄地址必須跟類的地址保持一致（比方說，PushTask.php模型在Model/Push/PushTask.php）,那么PushTaskFactory的位置就必須在database/factories/Push/PushTaskStatisticFactory.php * step3：創建一個執行填充類：PushTaskSeeder，創建命令：```php artisan db:seed --class=PushTaskSeeder``` * [ ] 模型內容： ***PushTask.php*** ``` <?php namespace App\Models; use App\Models\Push\PushTaskApp; use App\Models\Push\PushTaskStatistic; use DateTime; use App\Models\Push\PushTaskContent; use Illuminate\Database\Eloquent\Builder; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Relations\BelongsTo; use Illuminate\Database\Eloquent\Relations\HasMany; use Illuminate\Database\Eloquent\Relations\HasOne; /** * @method static self search($search) */ class PushTask extends BaseModel { // 設置表名 protected $table = "push_tasks1"; // 推送任務類型 1.定時推送 2.延遲推送 const PUSH_TASK_TIMED = 1; const PUSH_TASK_DELAY = 2; use HasFactory; /** * 關聯消息統計【一對一】 * * @return HasOne */ public function statistic(): HasOne { return $this->hasOne(PushTaskStatistic::class, 'id', 'task_id'); } } ``` ***PushTaskApp.php*** ``` <?php namespace App\Models\Push; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; class PushTaskApp extends Model { // 設置表名 protected $table = "push_task_apps1"; protected $guarded = ['id']; use HasFactory; } ``` ***PushTaskStatistic.php*** ``` <?php namespace App\Models\Push; use App\Models\PushTask; use Illuminate\Database\Eloquent\Factories\HasFactory; use Illuminate\Database\Eloquent\Model; use Illuminate\Database\Eloquent\Relations\BelongsTo; class PushTaskStatistic extends Model { protected $guarded = ['id']; protected $table = 'push_task_statistics1'; use HasFactory; /** * 關聯任務 * * @return BelongsTo */ public function task(): BelongsTo { return $this->belongsTo(PushTask::class); } } ``` * [ ] 工廠類內容： ***PushTaskFactory.php*** ``` <?php namespace Database\Factories; use App\Models\PushTask; use Carbon\Carbon; use Illuminate\Database\Eloquent\Factories\Factory; class PushTaskFactory extends Factory { protected $model = PushTask::class; public function definition() { return [ 'name' => $this->faker->sentence, 'push_number' => $this->faker->unique()->randomNumber(8), 'push_task_type' => $this->faker->randomElement([1, 2]), 'country_code' => $this->faker->countryCode, 'receive_type' => $this->faker->randomElement([1, 2]), 'send_time' => Carbon::parse($this->faker->dateTimeBetween('-1 year', 'now'))->format('Y-m-d H:i:s'), 'push_trigger_type' => $this->faker->randomElement([0, 1, 2, 3, 4]), 'delay_time' => $this->faker->randomNumber(5), 'cool_time' => $this->faker->randomNumber(3), 'push_position' => $this->faker->randomElement([0, 1, 2]), 'push_scene' => $this->faker->randomElement([0, 1]), 'app_ids' => json_encode($this->faker->randomElements([1, 2, 3, 4], 2)), 'system' => $this->faker->randomElement([1, 2]), 'last_uid_digits' => $this->faker->randomNumber(5), 'ab_id' => $this->faker->randomNumber(8), 'link_book_id' => $this->faker->randomNumber(8), 'read_book_id' => $this->faker->randomNumber(8), 'create_to_send_time' => $this->faker->randomNumber(5), 'active_to_send_time' => $this->faker->randomNumber(5), 'total_recharge_nums' => $this->faker->randomNumber(5), 'total_recharge_money' => $this->faker->randomNumber(5), 'total_active_days' => $this->faker->randomNumber(5), 'send_time_type' => $this->faker->randomElement([1, 2]), 'push_priority' => $this->faker->randomNumber(3), 'status' => $this->faker->randomElement([1, 2, 3, 4, 5]), 'admin_id' => $this->faker->randomNumber(5), 'is_deleted' => $this->faker->randomElement([0, 1]), 'deleted_at' => Carbon::parse($this->faker->optional()->dateTimeBetween('-1 year', 'now'))->format('Y-m-d H:i:s'), 'last_stop_at' => Carbon::parse($this->faker->optional()->dateTimeBetween('-1 year', 'now'))->format('Y-m-d H:i:s'), 'is_warm_up' => $this->faker->randomElement([0, 1]), 'created_at' => Carbon::parse($this->faker->dateTimeBetween('-1 year', 'now'))->format('Y-m-d H:i:s'), 'updated_at' => Carbon::parse($this->faker->dateTimeBetween('-1 year', 'now'))->format('Y-m-d H:i:s'), ]; } } ***PushTaskStatisticFactory.php*** <?php namespace Database\Factories\Push; use App\Models\Push\PushTaskStatistic; use App\Models\PushTask; use Carbon\Carbon; use Illuminate\Database\Eloquent\Factories\Factory; class PushTaskStatisticFactory extends Factory { protected $model = PushTaskStatistic::class; public function definition() { return [ 'push_type' => $this->faker->randomNumber(2), 'is_auto' => $this->faker->randomElement([0, 1]), 'send_all' => $this->faker->randomNumber(5), 'send_success' => $this->faker->randomNumber(5), 'click_num' => $this->faker->randomNumber(5), 'recharge_user_num' => $this->faker->randomNumber(5), 'order_num' => $this->faker->randomNumber(5), 'order_price' => $this->faker->randomNumber(5), 'total_recharge' => $this->faker->randomNumber(5), 'sub_coin_num' => $this->faker->randomNumber(5), 'sub_coin_price' => $this->faker->randomNumber(5), 'sub_time_num' => $this->faker->randomNumber(5), 'sub_time_money' => $this->faker->randomNumber(5), 'send_time' => Carbon::parse($this->faker->dateTimeBetween('-1 year', 'now'))->format('Y-m-d H:i:s'), 'created_at' => Carbon::parse($this->faker->dateTimeBetween('-1 year', 'now'))->format('Y-m-d H:i:s'), 'updated_at' => Carbon::parse($this->faker->dateTimeBetween('-1 year', 'now'))->format('Y-m-d H:i:s') ]; } } 填充類內容： PushTaskSeeder.php ``` <?php namespace Database\Seeders; use Illuminate\Database\Seeder; use App\Models\PushTask; use App\Models\Push\PushTaskApp; use App\Models\Push\PushTaskStatistic; use Illuminate\Support\Facades\DB; use Illuminate\Support\Facades\Log; class PushTaskSeeder extends Seeder { public function run() { ini_set('memory_limit', '512M'); try { DB::beginTransaction(); // 生成1W條 PushTask 數據 $tasks = PushTask::factory()->count(1000)->create(); $tasks->each(function ($info) { // 創建關聯的app數據 $pushTaskAppData = []; $appList = json_decode($info['app_ids'], 1); foreach ($appList as $appId) { $pushTaskAppData[] = ['task_id' => $info->id, 'app_id' => $appId]; } // 批量插入關聯的app數據 if ($pushTaskAppData) { PushTaskApp::query()->insert($pushTaskAppData); } // 批量插入關聯的PushTaskStatistic數據 PushTaskStatistic::factory()->count(1000)->create(['task_id' => $info->id]); }); DB::commit(); } catch (\Exception $e) { DB::rollBack(); $failMessage = sprintf('Failed to seed push tasks,Msg:%s,File:%s(%s)', $e->getMessage(), $e->getFile(), $e->getLine()); echo $failMessage . $e->getTraceAsString(); Log::error($failMessage); // 優先保存日志 } } } ``` 上述文件填寫完成后，即可執行填充數據，執行方式有兩種：方式1：局部執行命令：```php artisan db:seed --class=PushTaskSeeder``` 方式2：全局執行：需要在DatabaseSeeder.php填充類中將上面創建的PushTaskSeeder.php加入到全局填類中，然后執行命令：```php artisan db:seed``` 即可 DatabaseSeeder.php的文件內容： ``` <?php namespace Database\Seeders; use Illuminate\Database\Seeder; class DatabaseSeeder extends Seeder { /** * Seed the application's database. * * @return void */ public function run() { $this->call([ PushTaskSeeder::class ]); } } ```