思路:
单表记录过大查询效率低下,可以考虑将表分为月度,年度的表来存数据。
建表脚本: (以下只是示例,按实际需求调整)
<?php
namespace App\Console\Commands\Table;
use Illuminate\Console\Command;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class TableCopy extends Command
{
protected $signature = 'cron:table-copy {table?}';
protected $table_date_format = 'Y';
public function handle()
{
$table_base = $this->argument('table') ?: 'promotion_water';
try {
switch($table_base) {
case 'promotion_water':
$table_name = $table_base.'_'.date($this->table_date_format);
$rt = $this->check_promotion_running_water_table($table_name);
// $rt && $this->info('created table success! table:'.$table_name);
$rt && logger('created table success! table:'.$table_name);
break ;
default:
break ;
}
} catch (\Exception $e) {
logger('TableCreate.error',['table_base'=> $table_base,$e->getMessage()]);
}
}
public function check_promotion_running_water_table($table_name)
{
if(!Schema::hasTable($table_name)) {
Schema::create($table_name, function (Blueprint $table) {
$table->increments('id');
$table->string('serial_number')->index()->comment('流水号');
$table->string('seller_id')->index()->comment('渠道号');
$table->string('userno')->index()->comment('推广者ID');
$table->string('cardId')->index()->comment('卡ID');
$table->string('proCode')->index()->comment('来源码');
$table->string('link')->index()->comment('落地页链接');
$table->string('from')->index()->comment('来源');
$table->string('ip')->index()->comment('IP');
$table->datetime('time')->index()->comment('');
$table->timestamps();
});
if(Schema::hasTable($table_name)) {
return true;
}
}
return false;
}
}
定时任务配置
//app/Console/Kernel.php
$schedule->command('cron:table-copy')->dailyAt('00:00')->runInBackground();