MySQL和PHP使用的timezone不一致导致的困惑

最新推荐文章于 2024-09-18 23:11:57 发布

chuxuzhui0592

最新推荐文章于 2024-09-18 23:11:57 发布

阅读量307

点赞数

文章标签： php 数据库开发工具

原文链接：https://my.oschina.net/huxuanhui/blog/1821313

版权

情景描述：

使用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;

+------+---------------+----------------+------------------+-------------------------------+
| id | flight_number | departure_city | destination_city | from_unixtime(departure_time) |
+------+---------------+----------------+------------------+-------------------------------+
| 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 |
| 2140 | AX5072 | 5 | 6 | 2018-06-04 21:47:45 |
| 2307 | AX5072 | 5 | 6 | 2018-06-04 21:47:45 |
| 929 | AX1217 | 3 | 1 | 2018-06-01 18:23:02 |
| 1079 | AX1217 | 3 | 1 | 2018-06-04 18:23:02 |
| 1778 | AX1217 | 3 | 1 | 2018-06-04 18:23:02 |
+------+---------------+----------------+------------------+-------------------------------+
依然会有日期重复的航班生成。

查看了commander.log中的sql，随即抽取了一个用在whereRaw中的参数，放到mysql中执行

select from_unixtime(1527638400);

+---------------------------+
| from_unixtime(1527638400) |
+---------------------------+
| 2018-05-30 08:00:00 |
+---------------------------+

显然不是 00:00:00.

所以确认是MySQL和PHP使用的timezone不一致导致的。

PHP使用gmmktime来保证时间戳是UTC时间，但是MySQL使用的是

select @@global.time_zone;

+--------------------+
| @@global.time_zone |
+--------------------+
| SYSTEM |
+--------------------+

我的系统timezone使用的是Aisa/Shanghai.

关于timezone问题的确认：

mysql> set time_zone='+00:00';
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1527728303 |
+------------------+
mysql> select time_zone='+08:00';
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
|       1527728326 |
+------------------+

修改MySQL使用UTC时间:

vim /etc/mysql/my.cnf

[mysqld]

default-time-zone=+00:00

转载于:https://my.oschina.net/huxuanhui/blog/1821313