【Laravel】InnoDBで二番目以降のキーにAuto Incrementをマイグレーションで設定する

InnoDBではMyISAなどと違い、サブキーにAuto Incrementをつけて「主キーがかぶるとサブキーが+1される」みたいなことができません。

なのでストアドファンションを使って同様の機能を実装してみました。

ストアドファンクション

データベース上で実行される関数のことをストアドファンクションと言います。

下に書いたように多少癖はありますが、よくある関数と似たような書き方をします。

DELIMITER // (；を//に変更)

CREATE FUNCTION {ファンクション名}(パラメータ {型}) RETURN {型}
BEGIN
  DECLARE result INT UNSIGNED; //変数の設定
  SET result := 1; //1を代入
  RETURN result; //result=1を返す
END//

DELIMITER ; (//を;に変更)

またこれと似たようなものでストアドプロシージャというものがあります。
こちらはストアドファンクションとは違い、返り値がなく実行されるだけとなります。

マイグレーションで実行する

今回は「記事にコメントを投稿すると自動的に記事毎のコメント番号が作成される」というストアドファンクションを作成したいと思います。

+----+---------+-------------+------------------+
| id | post_id | comment_num | comment_body     |
+----+---------+-------------+------------------+
| 1  |       1 |           1 | sample comment １ |
| 2  |       1 |           2 | sample comment 2 |
| 3  |       1 |           3 | sample comment 3 |
| 4  |       2 |           1 | sample comment 4 |
| 5  |       2 |           2 | sample comment 5 |
+----+---------+-------------+------------------+

マイグレーションファイルの作成

まずはベースとなるマイグレーションファイルを作成しましょう。

class CreateCommentsTable extends Migration
{
    public function up()
    {
        Schema::create('comments', function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedinteger('post_id')
            $table->unsignedinteger('comment_num');
            $table->text('comment_body');
            $table->timestamps();
        });
    }
}

ストアドファンクションの作成

同じマイグレーションファイル内にストアドファンクションを設定するコードを追加します。

    public function up()
    {
        ...略...

        $stored_function = '
        DROP PROCEDURE IF EXISTS incr_comment_num;
        # 重複して作成されないように、既にあるストアドファンクションを削除する。

        CREATE FUNCTION incr_comment_num (article_id int) RETURNS int unsigned READS SQL DATA SQL SECURITY INVOKER
        BEGIN
            DECLARE ret INT UNSIGNED;

            SELECT MAX(comment_num) + 1 FROM comments WHERE post_id = article_id INTO ret;
            # post_idと引数のarticle_idが同じ時、commentsテーブル内のcomment_numの最大値にプラス１した値をretに入れる。

            SELECT IFNULL(ret, 1) INTO ret;
            # 上記のコードが実行されずretがnullの時、1を入れる。

            RETURN ret;
        END;
        ';

        DB::connection()->getPdo()->exec($stored_function);
        // SQL文として実行する。
    }

では実際にストアドファンクションが登録されたか確認してみましょう。

php artisan migrateを実行し、MySQL内のcommentsテーブルを作成したデータベースをuseします。
そしてこちらの記事の通り、以下のSQL文をコピペして実行しましょう。

SELECT
  ROUTINE_SCHEMA, /* ストアドプロシージャがあるデータベース */
  ROUTINE_NAME,   /* ストアドプロシージャの名前 */
  ROUTINE_TYPE    /* プロシージャとファンクションのどちらかを示す */
FROM
  information_schema.ROUTINES
WHERE 
  ROUTINE_TYPE = 'FUNCTION'; /* ファンクションのみ抽出 */

ストアドファンクションが登録されている一覧が出力され、その中に先ほどマイグレーションしたincr_comment_numが表示されていれば成功です。

シーダーファイルの作成

ストアドファンクションが登録されたので実際に使用してみましょう。

以下のようにシーダーファイルを作成します。

class CommentsTableSeeder extends Seeder
{
    public function run()
    {
        for ($i = 1; $i <= 5; $i++) {
            DB::table('comments')->insert([
                'post_id' => rand(1, 2),
                'comment_num' => DB::raw('incr_comment_num(post_id)'),
                // DB::rawを使用することでSQL文を実行することができます。
                'comment_body' => 'sample comment' . $i,
                'created_at' => new DateTime('now'),
                'updated_at' => new DateTime('now'),
            ]);
        }
    }
}

post_idが重複するとcomment_numが+1されるデータが出力されていれば成功です。

最後に

今回はInt型で数字が重複することをトリガーにしましたが、それ以外にも文字などでも同様のことができますので必要に応じてぜひ試してみてください。

参考文献

MySQLのInnoDBでもPRIMARY KEYの2カラム目以降にAUTO_INCREMENTを使いたい
SQL Serverのストアドファンクションを作成する
初心者からのMySQLストアドプロシージャ＆ファンクション入門
ストアドファンクションの一覧を確認する方法
Laravel5 マイグレーションファイルで素のSQLクエリを実行する
Creating MYSQL Procedure in Laravel 4 Migrations