# **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); // 优先保存日志
}
}
}
```
- Laravel5.5总结
- 项目管理
- Manager
- Vip专属链接管理
- Api
- Vip专属链接管理(Api)
- php artisan route:list 路由显示不全
- 数据迁移和填充
- Laravel5.5事件监听机制（注册-监听-任务调度）
- 章节1：未启用任务调度
- 章节2：启用任务调度
- 使用记录
- 数据迁移使用记录
- 安装laravel5.5日志观察器
- Laravel5.5消息队列（rabbitmq）
- 1：laravel自带消息队列
- 2：RabbitMq队列使用
- 第三方支付扩展：yansongda/laravel-pay
- 安装指引
- 控制器内使用
- 分表查询（mysql+mongo）
- 前端Vue按钮导出问题
- 单元测试
- 模型使用
- laravel9数据填充