mysql 定时任务 timezone_MySQL和PHP使用的timezone不一致导致的困惑

出馬仙老于頭

于 2021-02-18 01:35:43 发布

阅读量186

点赞数

文章标签： mysql 定时任务 timezone

本文链接：https://blog.csdn.net/weixin_36433147/article/details/114333626

版权

情景描述：

使用laravel开发一个航空订票系统程序，其中使用seeder来生成测试数据。

航班数据的生成规则：

1. 航班号是6位字符，前两位是固定字母比如AX，后4为是随机数字

2. 航班的出发和到达城市不能一样

3. 相同的航班号出发城市和到达城市需保持一致，并且起飞时间是相同的，但是日期不能相同

4. 航班起飞时间在生成数据所在时刻的前一天和后五天之内

预备工作：

php artisan make:migration create_flights_table

php artisan make:model Flight

php artisan make:factory FlightFactory --model=Flight

php artisan make:seeder FlightsTableSeeder

表结构：

Schema::create('flights', function (Blueprint $table) {

$table->increments('id');

$table->char('flight_number', 6)->default('');

$table->unsignedInteger('departure_city')->default(0)->comment('departure city id');

$table->unsignedInteger('destination_city')->default(0)->comment('destination city id');

$table->unsignedInteger('departure_time')->default(0)->comment('unix timestamp');

$table->timestamps();

$table->index('departure_city');

$table->index('destination_city');

$table->index('departure_time');

});

Factory(核心代码):

$factory->define(App\Flight::class, function (Faker $faker) {

$cities = [1, 2, 3, 4, 5, 6];

$start_date = 86400 * -1;

$end_date = 86400 * 5;

$current_timestamp = now()->timestamp;

$flight_number = 'AX' . $faker->randomNumber(4, TRUE);

$instance = [

'flight_number' => $flight_number,

'departure_time' => $current_timestamp + (int)$faker->numberBetween($start_date, $end_date),

'departure_city' => $faker->randomElement($cities),

'destination_city' => $faker->randomElement($cities),

];

while ($instance['departure_city'] == $instance['destination_city']) {

$instance['destination_city'] = $faker->randomElement($cities);

}

$existed = Flight::where('flight_number', $flight_number)->first();

if ($existed) {

$instance['departure_city'] = $existed->departure_city;

$instance['destination_city'] = $existed->destination_city;

// 这里时间范围明显不符合规则了,但是为了说明问题，可以忽略

$instance['departure_time'] = $existed->departure_time + $faker->randomElement([-1, 1, 2, 3, 4, 5]) * 86400;

while($instance['departure_time'] == $existed->departure_time) {

$instance['departure_time'] = $existed->departure_time + $faker->randomElement([-1, 1, 2, 3, 4, 5]) * 86400;

}

while(true) {

$departure_date = gmmktime(0, 0, 0, date('n', $instance['departure_time']), date('j', $instance['departure_time']), date('Y', $instance['departure_time']));

$same_date = Flight::where('flight_number', $flight_number)->whereRaw("UNIX_TIMESTAMP(FROM_UNIXTIME(departure_time, '%Y-%m-%d'))=?", [$departure_date])->count();

if ($same_date === 0) {

break;

}

$instance['departure_time'] = $instance['departure_time'] + $faker->randomElement([-1, 1, 2, 3, 4, 5]) * 86400;

}

return $instance;

});

Seeder:

// 需要每条数据都写入数据库才能判断是否有航班号相同且日期相同的记录

for ($i = 0; $i < 3000; $i++) {

factory(App\Flight::class)->create();

}

运行migration:

php artisan migrate:refresh --seed

检测生成的数据是否有重复不符合规则的代码：

select id,f.flight_number,departure_city,destination_city,from_unixtime(departure_time) from flights f join (select flight_number,count(0) cnt,unix_timestamp(from_unixtime(departure_time, '%Y-%m-%d')) departure_date from flights group by flight_number,departure_date) t on f.flight_number=t.flight_number where t.cnt>1 order by f.flight_number desc;

+------+---------------+----------------+------------------+-------------------------------+

+------+---------------+----------------+------------------+-------------------------------+

| 1703 | AX8444 | 2 | 5 | 2018-06-02 14:31:23 |

| 2185 | AX8444 | 2 | 5 | 2018-06-05 14:31:23 |

| 2480 | AX8444 | 2 | 5 | 2018-06-05 14:31:23 |

| 1177 | AX7375 | 4 | 5 | 2018-06-03 05:27:36 |

| 1555 | AX7375 | 4 | 5 | 2018-06-06 05:27:36 |

| 1915 | AX7375 | 4 | 5 | 2018-06-06 05:27:36 |

| 1357 | AX5609 | 1 | 2 | 2018-06-04 22:45:18 |

| 938 | AX5609 | 1 | 2 | 2018-06-02 22:45:18 |

| 2668 | AX5609 | 1 | 2 | 2018-06-04 22:45:18 |

| 2059 | AX5072 | 5 | 6 | 2018-06-02 21:47:45 |