MySQLのテーブルの更新履歴を履歴テーブルに残す方法

#環境
MySQL 5.7
PHP 7.2
Laravel Framework 5.7

#やりたいこと
データの履歴管理はRDBMSの苦手とする領域の一つである。
Elasticsearchのようにより適切な別のソリューションを使うというのも選択肢の一つとしてあるが、
お手軽な方法の一つとして本体テーブルのそっくりさんを用意し、そのテーブルに履歴を貯めておく方法をおすすめしたい。


次のようなテーブルがある場合、

| orders       | 
|:-----------------|
| id             | 
| customer_id           | 
| memo             | 
| applied_at               | 
| canceled_at             |


このようなログテーブルを用意し、

| orders_log      | 
|:-----------------|
| id             | 
| customer_id           | 
| memo             | 
| applied_at               | 
| canceled_at             |
| log_id             |
|log_time|
|ins_flag|

本体テーブル更新が走るたびに、ログテーブルに更新時点でのデータを溜め込むようにする。

#実現方法
トリガーを用いて実現する
まず次のコマンドで本体そっくりさんのログテーブルを生成する。

    CREATE TABLE orders_log AS select * from orders;

そしてログテーブルに解析用のカラムを追加する。
（必要なければ追加しなくてもOK）

    ALTER TABLE orders_log  ADD column (log_id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, log_time datetime, ins_flag tinyint);


さらにデータ追加、更新時にログテーブルへのデータ追加処理が走るようトリガーを設定する。

    CREATE TRIGGER orders_insert_trigger AFTER INSERT ON orders FOR EACH ROW insert into orders_log  ( id,memo,customer_id,canceled_at,applied_at,log_time,ins_flag ) VALUES (NEW.id,NEW.memo,NEW.customer_id,NEW.canceled_at,NEW.applied_at,now(),1);
    CREATE TRIGGER orders_update_trigger AFTER UPDATE ON orders FOR EACH ROW insert into orders_log  ( id,memo,customer_id,canceled_at,applied_at,log_time,ins_flag ) VALUES (NEW.id,NEW.memo,NEW.customer_id,NEW.canceled_at,NEW.applied_at,now(),0);

以上の操作の結果、ordersに対してがデータ追加、更新が走るたびに、orders_logにログレコードが追加されるようになる。

ポイントとして、ログテーブル生成に CREATE TABLE XXX LIKE YYYでなく、CREATE TABLE XX AS select * from YYYを使う点が挙げられる。
前者のコマンドでログテーブルを作成すると、本体に設定されたインデックスも一緒に付いてきてしまい、パフォーマンスが悪化したり、一意制約違反により意図せぬエラーに遭遇する可能性があるので注意されたい（もちろん運用上必要な場合は、ログテーブルにもインデックスを付けるべきである。）

#このやり方のメリット
1.本体のRDBMS以外の別サービスを導入する必要がなくお手軽である。
2.本体データとログデータの整合性がRDBMSレイヤーで保証される。
3.Redshift等のRDBMSベースの解析サービスへの連携が容易。データサイズと気合い次第では本体のRDBMS上で直接データ解析することも十分に可能。

個人的には、2のデータ整合性のメリットが絶大であると考えている。
（ここが崩れると復旧がすさまじくめんどくさいので）

#Laravel Commandでログテーブルを簡単生成
ログテーブルとトリガーを簡単に作成するLaravel Commandスクリプトを用意したので、よろしければ参考にしてください。

```php
<?php

namespace App\Console\Commands;

use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class CreateLogTriggerCommand extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'fn:create_trigger {table_name} {log_time_column_name=utc_log_time} {ins_flag_column_name=ins_flag}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Prepare SQL to create table and trigger for logs';


    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $table_name = $this->argument("table_name");
        $log_time_column_name = $this->argument("log_time_column_name");
        $ins_flag_column_name = $this->argument("ins_flag_column_name");

        $column_array = DB::table('information_schema.columns')->where('TABLE_NAME', $table_name)->pluck("COLUMN_NAME");
        $log_table_name = $table_name . "_log";

        echo "CREATE TABLE $log_table_name AS select * from $table_name;" .PHP_EOL;
        echo "ALTER TABLE $log_table_name  ADD column (log_id bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, $log_time_column_name datetime, $ins_flag_column_name tinyint);".PHP_EOL;

        $columns = "";
        $values = "";
        foreach ($column_array as $column) {
            $columns .= $column . ",";
            $values .= "NEW." . $column . ",";
        }

        $columns = $columns . $log_time_column_name;
        $values = $values . "now()";

        echo "CREATE TRIGGER {$table_name}_insert_trigger AFTER INSERT ON $table_name FOR EACH ROW insert into $log_table_name  ( $columns,$ins_flag_column_name ) VALUES ($values,1);".PHP_EOL;
        echo "CREATE TRIGGER {$table_name}_update_trigger AFTER UPDATE ON $table_name FOR EACH ROW insert into $log_table_name  ( $columns,$ins_flag_column_name ) VALUES ($values,0);".PHP_EOL;

    }
}
```