Database JUNKY

MySQL,MariaDBを中心としたブログです

トップ > MySQL > 超便利！MySQL テーブル定義のデフォルト値で関数をつかっちゃおう

超便利！MySQL テーブル定義のデフォルト値で関数をつかっちゃおう

MySQL CREATE TABLE DEFAULT STORED PROCEDURE Generated Columns 自動セット 列関数

いままでなんで、きずかなかったのだろう。。

例えば、こんなテーブルがあったとして

DROP TABLE IF EXISTS test_tab1 ;

CREATE TABLE `test_tab1` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Numeric_only_date_time` varchar(8) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
;

idはオートインクリメント値

numeric_only_date_timeは、現在の日付を、数字のみに表したもの（20180511みたいな)

という形式でデータを格納したい場合、みなさまはどうしますか？

f:id:hit10231023:20180309104332j:plain

自分でしたら

INSERT INTO test_tab1
  (numeric_only_date_time) VALUES (DATE_FORMAT(CURRENT_TIMESTAMP,'%Y%m%d')) ;

で、まずINSERTすると思います。

結果は以下のような感じになります

SELECT * FROM test_tab1 ;
+----+---------------------+------------------------+
| Id | CreatedAt           | Numeric_only_date_time |
+----+---------------------+------------------------+
|  1 | 2018-05-11 12:50:16 | 20180511               |
+----+---------------------+------------------------+

今回シンプルなデータなので、あまりメリットは感じないかもしれませんが、仮に、numeric_only_date_timeのデフォルト（現在日付）にしたい場合で、もっとシンプルにINSERT文を発行したい時はどうしましょう？実はそれが簡単にできてしまうのです！！

生成カラム（Generated Columns）を活用する

生成カラムを活用します。これ、MySQL 5.7からの新機能なのですが、とにかく便利。 さっそく使ってみようと思います。

カラム定義の変更

まず、numeric_only_date_timeのカラム定義を、このGenerated Columnsに変更したいと思います

ALTER TABLE test_tab1
  MODIFY COLUMN numeric_only_date_time varchar(8) 
  GENERATED ALWAYS AS 
    (DATE_FORMAT(CreatedAt,'%Y%m%d')) STORED NOT NULL ;
  • 変更後の定義は以下の通りとなります
CREATE TABLE `test_tab1` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `CreatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `numeric_only_date_time` varchar(8) GENERATED ALWAYS AS (date_format(`CreatedAt`,'%Y%m%d')) STORED NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ;

でわ、INSERTしてみましょう

ちょっと面白いSQLになりました。これだけですw

INSERT INTO test_tab1 (id) VALUES (NULL) ;

結果を確認してみましょう

SELECT * FROM test_tab1 ;
+----+---------------------+------------------------+
| Id | CreatedAt           | numeric_only_date_time |
+----+---------------------+------------------------+
|  1 | 2018-05-11 12:50:16 | 20180511               |
|  2 | 2018-05-11 12:56:11 | 20180511               |
+----+---------------------+------------------------+
2 rows in set (0.00 sec)

面倒な関数、date_format(CreatedAt,'%Y%m%d')　は、GENERATED ALWAYSにて内包しております。また、CreatedAtについては、DEFAULT値で、CURRENT_TIMESTAMPが設定されているので、そのセットされた、CreatedAtを引数として、numeric_only_date_timeの値を生成する仕組みになります。

ちょっと、例がシンプルすぎて、あまりメリットを感じられないかもしれませんが、これ、かなり便利です。

また、今回は実験しておりませんが、下記のように、独自のSTORED FUNCTIONも設定できると思います。

ALTER TABLE test_tab1
  MODIFY COLUMN numeric_only_date_time varchar(8) 
  GENERATED ALWAYS AS 
    (my_func1(CreatedAt) STORED NOT NULL ;

ぜひぜひ活用してください