MySQL读写一致性问题

胡晅晖 发布于 2018/05/30 17:11
阅读 881
收藏 0

情景描述:

使用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           |
+------+---------------+----------------+------------------+-------------------------------+
不明白为什么依然会有日期重复的航班生成。

猜想:

1. 是否在count()那个查询的时候数据还没写入,所以未读取到

    关于这个猜想我开启了general_log查看了sql的执行顺序,貌似没有看出问题

2. 是否是count()查询结果被缓存导致的 【但是根据id顺序和出现重复数据的顺序,貌似可以排除】

以下是问题补充:

@胡晅晖:$ mysql -V mysql Ver 14.14 Distrib 5.7.21-21, for Linux (x86_64) using 7.0 (2018/05/30 17:26)
加载中
0
daydaygo_chen
daydaygo_chen

既然是测试数据, 可以预先生成, 然后使用 `array_rand($arr, 2);` 这种方式来取, 不用一个一个生成, 再判断是否符合条件

胡晅晖
胡晅晖
主要是想知道写了控制的逻辑,为什么数据还有不符合规则的。 貌似我知道问题的答案了,应为mysql使用的timezone和php使用的不一致,也就是whereRaw那里的那个检测是无效的。
0
胡晅晖
胡晅晖

确定了答案,修改了博客,结果可以去博客看看。

https://my.oschina.net/huxuanhui/blog/1821313

谢谢楼上的评论。

OSCHINA
登录后可查看更多优质内容
返回顶部
顶部