MySQL – 月単位の集計~残高もある場合

例題設定

例題として個人の銀行口座の入出金記録の以下の構造のテーブルを考える。

ここでidは入出金記録の順番に付されていて、変更されないものとする。すなわち、日・月・年などの単位でグルーピングしたとき、idが最も大きいレコードのbalanceがその期の残高となっている。

合計計算のみの場合

月ごとの合計を計算するだけの場合、年月でグルーピングしてSUM関数を使う。

要点は以下の通り。

  • GROUP BY句で、yyyymm形式でフォーマットされた年月単位でグルーピング
  • グループごとにSUM関数で出金額と入金額を合計
  • 年月表示はGROUP BY句のフォーマットと異なってもよい
  • ただしグルーピングされた複数レコード中のどのレコードのdateを使うかを明示するため、MAX関数を使っている。
    • こうしないと(オプション指定なしでは)エラーが発生する

各月の残高も含める場合

月ごとの最終取引の抽出

各月の残高がデータに含まれていて、取引がidの昇順であることがわかっている場合、月ごとにグルーピングされたレコードのうちidが最大のレコードがその月の最後の取引になり、そのbalanceが月の残高になる。

そこで、まず各月の最終取引のidを取得する。

各月最終取引のレコード取得と残高表示

最終取引のidに一致するレコードのみ元のテーブルから取り出すため、INNER JOINを使う。

要点は以下の通り。

  • 最終取引を抽出したクエリーをサブクエリ―として、そのidと等しいレコードのみを元のテーブルから抽出している
  • サブクエリ―にはエイリアスが必須
  • 抽出された最終取引のbalanceを月末残高として表示
  • SELECT句のidは確認用で、表示目的としては不要

月ごとの入出金額を追加

上記のクエリーに、グルーピングされた月ごとの入出金額を追加する。

要点は以下の通り。

  • 月ごとにグルーピングしているサブクエリ―で、SUM関数によって月単位の出金・入金額を計算
  • それらの値をエイリアスを使って元のテーブルで表示

このクエリーのチェックは、前月残高に当月の入出金額を加減して当月残高となることで確認できる。

 

 

MySQL – CSVファイルのインポート

概要

MySQLでCSVファイルをインポートする手順。

要点は以下の通り。

  • CSVファイルはUTF-8(BOMなし)で準備する
  • 改行コードに注意
  • MySQLサーバーとクライアントの両方でローカルのファイル入力の許可が必要
    • クライアントはログイン時のオプションで、サーバー側はグローバル変数で設定する

CSVファイルの準備

CSVファイルを準備し、MySQLで読み込む場所に配置する。CSV作成時の型と文字コードに注意。

型に関する注意点

数字だが001のように表現したい場合は文字列として扱う。

  • Excelの列の形式で適切な型を設定する

日付・時刻形式の空白値に注意。

  • 日付・時刻値の列に空のデータがある場合にMySQLのdate/time/datetimeで読み込むと、NULL値ではなく'0000-00-00''0000-00-00 00:00:00'で埋められてしまう

文字コードの注意点

CSVファイルはUTF-8のBOMなしで準備する。

  • ExcelのシートをCSVファイルとして保存すると、UTF-8(BOMあり)になるので、メモ帳などで開いてBOMなしで保存し直す必要がある

MySQLログイン時のオプション

MySQLにログインするクライアントで、ローカルのファイル入力を許可する必要がある。

ログイン時に以下のオプションのいずれかを付ける

  • --enebale-local-infile
  • --local_infile=1またはon

MySQLログイン後の設定

グローバル変数の確認

SELECTSHOW GLOBAL VARIABLESlocal_infileの値を確認。この値が0/OFFの場合は許可されていない。以下のいずれかで確認できる。

SELECT @@local_infile;

SHOW GLOBAL VARIABLES LIKE 'local_infile';

グローバル変数の設定

SET GLOBALlocal_infileの値を1またはONにセットする。

SET GLOBAL local_infile=1またはON;

インポート実行

テーブルの準備

CSVの各カラムに対応した構造で、空のテーブルを準備しておく。

MySQLのtimestamp型の2038年問題に注意。

LOAD DATAの実行

SQLの例

CSVファイルをテーブルにインポートするSQLの例。

この例では、WindowsのようにCRLFで改行されたファイルを読み込み、区切り文字がカンマのCSVとして、先頭1行を飛ばして読み込む。

FIELDS

TERMINATED BY
CSV保存時に選んだ区切り文字を指定する。1文字である必要はない。デフォルトは'\t'
ENCLOSED BY
フィールドを囲み文字を1文字で指定する。デフォルトは''(空)で、フィールドが区切り文字で囲まれていることを期待しない。OPTIONALLYを付けると文字列のみ囲み文字で囲まれていることを期待する。
ESCAPED BY
エスケープ文字を1文字で指定する。エスケープ文字に続く文字がエスケープシーケンスとして解釈される。デフォルトは'\\''\'一文字を表している。

LINES

STARTING BY
指定したprefix以降のみ行として扱い、それ以外はスキップする。prefixは1文字でなくてもよい。デフォルトは''でprefixを設定していない(常に行頭から読み込む)。
TERMINATED BY
行末の文字列を指定する。デフォルトは'\n'

IGNORE

IGNORE 数値 lines/rowsで、先頭の指定した数の行/列を読み込まない。

 

 

 

PHP – 変数のコンソール表示

概要

PHPをコマンドラインで実行する場合に、変数の内容をコンソールに表示させるいくつかの方法を整理。JavaScriptのconsole.log()に比べて、何となく帯に短し襷に長し。個人的には使うとしたらview_export()か。

var_export()print_r()については、結果の文字列を表示させるのではなく、戻り値として扱うことができる。

jason_encode()は趣旨が少し違うが一応加えておく。

変数の内容をコンソールではなくブラウザーに表示する場合はこちら

準備

以下のような変数・値を表示させる。

var_dump

var_dump()は引数の変数の内容を型まで含めて詳しく表示する。少し冗長。

  • 変数ごとの表示後に改行される
  • 変数を複数指定しても、別々に実行したのと同じ結果
  • キー・値の文字列はダブルクォートで囲まれる
  • 実数はfloat[1]の様に表示される

var_export

var_export()は、var_dump()より簡潔な形で表示する。

  • 結果をPHPのソースにコピー・ペーストして使える
  • 表示後に改行されない
  • キー・値の文字列はシングルクォートで囲まれる
  • 実数は小数部がゼロでも1.0のように表示される
  • 配列の各要素末尾にカンマが付く

print_r()var_export()より更に簡潔に表示する。

  • 単純変数の表示後は改行されないが、配列の表示後は改行される
  • 文字列はクォートで囲まれない
  • 実数の小数部がゼロの時、整数と同じ表示になる
  • 配列の要素末尾にカンマは付かない

json_encode

変数の内容をJSON形式で返す。オプションのJSON_UNESCAPED_UNICODEを指定しないと、マルチバイト文字がHex表示になる。

何とか'\n'を改行として表示するなら、以下の様にするか。

 

Vagrant/Linux環境でPHPサーバーを起動する

概要

VagrantのLinux上でPHPのビルトインサーバーを立ち上げる。

ゲスト側IPアドレスの確認

Vagrant環境下でip aifconfig -aを実行してネットワークインタフェイスのアドレスを確認する。

サーバーの起動

ビルトインサーバーの起動コマンドは以下のとおり。

php -S ホスト:ポート -t ドキュメントルート
php --server ホスト:ポート --docroot ドキュメントルート

ホスト部分に、確認したIPアドレスを指定する。ドキュメントルートに、実行ファイルが置かれる場所を指定する。

ブラウザーでの表示

ブラウザーでのURL指定は以下のとおり。

 

Laravel – artisan – ルーティングリストの表示

下記コマンドでルーティングのリストが表示される。

php artisan route:list

ただし、各ルーティングで呼ばれるコントローラー・アクションが定義されていなければならない。

実行例。

 

MySQL – timestampの2038年問題

timestamp型とdatetime型

MySQLの日付・時刻表現にはtimestamp型とdatetime型がある。

MySQLのtimestamp型は内部表現に整数を用いていて、これが32bit精度の場合には、2038-01-19 03:14:07UTCまでしか表現できない。これは日本のタイムゾーンだと2038-01-19 12:14:07JSTに対応する。

一方datetime型は文字列で日時を表現していて、その範囲は1000年~9999年の間とされている。

timestamp型とdatetime型の有効範囲を比較すると以下のとおり。

timestamp datetime
UTC内部表現で保持 タイムゾーン文字列で保持
1970-01-01 00:00:01UTC~2038-01-19 03:14:07UTC 1000-01-01 00:00:00~9999-12-31 23:59:59

timestamp型の2038年問題

確認環境

timestamp型で上限値より大きな値を登録しようとすると、エラーで登録ができない。このことを確認してみる。環境は以下のとおり。

  • Windows(64bit版)上のVagrant+VirtualBOXで構築したCentOS7
  • MySQLのバージョンは8.0.26

確認用のテーブルは以下のとおり。

上限を超える値の登録は不可

まず上限値一杯の日時を登録してみる。UTCに対して日本のタイムゾーンで表現した2038年1月19日12時14分07秒を登録すると、問題なく登録される。

次に上限値に1秒を加えた日時を登録しようとすると、"Incorrect datetime value"となって登録できない。

timestamp型に対する加算の場合は9999年まで

次に登録された上限一杯の値に対してインターバルを加えていき、問題が生じるまでその境界を探していった。

その結果、9999年12月31日23時59分59秒999999…までは登録可能だが、10000年1月1日0時0分0秒に達することはできず、その場合にはNULLとなることがわかった。

32bitの上限を超えた後も値は保持されているが、その次の上限はdatetime型の上限と一致している。以下の何れかと推測される。

  • 内部的には64bit表現だが、入力時には32bitの上限で、演算時にはdatetime型の上限で抑えている
  • 内部的に32bit表現だが、その上限を超えたときにはdatetime型に内部的に切り替えている

まとめ

  • MySQLのtimestamp型は、2038年の上限値を超える値を登録できない
  • timestamp型に対する加算を行った場合、datetime型と同じ上限まで値を保持できる
    • 加算後にdatetime型の上限を超えた場合の値はNULLになる

64bit整数の場合はtimestampも西暦3000億年弱まで扱い可能だが、一部が64bitシステムでも、データベース、言語、フレームワークの全てが対応していないとまずそうなので、日付時刻を扱う際はdatetime型としておくのが安全なようだ。

 

Tips – カラムの幅を一杯に広げる

概要

外枠の中に複数カラムを起き、1つのカラムを最大幅まで伸長するためのCSS。

外枠

外枠を表示領域の80%の幅とし、センタリング。

  • 幅を表示領域の80%に:width: 80%;
  • 左右方向のセンタリング:margin: 0 auto;
 width: 80%; margin: 0 auto;

外枠の中に複数カラム

親要素にdisplay: flex;を指定し、伸長する子要素のみflex: 1;を指定。

2カラム~右拡張

  • 外枠(親要素):display: flex;
  • 左側カラム:幅指定なし→内容に合わせる
  • 右側カラム:flex: 1;→残り幅一杯
margin: 10px;
margin: 10px;flex: 1;
いづれの御時にか、女御、更衣あまた候ひ給ひける中に、いとやむごとなき際にはあらぬが、すぐれて 時めき給ふありけり。

3カラム~中央拡張

  • 外枠(親要素):display: flex;
  • 左側カラム:width: 10%;
  • 中央カラム:flex: 1;→残り幅一杯
  • 右側カラム:width: 10%;
width 10%;
margin: 10px;flex: 1;
いづれの御時にか、女御、更衣あまた候ひ給ひける中に、いとやむごとなき際にはあらぬが、すぐれて 時めき給ふありけり。
width 10%;

 

Laravel – フォームリクエスト

概要

  • FormRequestクラスは、ユーザー権限の認証と入力のバリデーションを専用に扱う
  • 実装は、artisanでFormRequestクラスを継承したクラスを生成し、認証やバリデーションの内容を記述
  • コントローラーのインジェクションをRequestからFormRequestの継承クラスに変更することで、チェック機能をコントローラーから分離することができる

準備

バリデーションで使ったアプリケーションを利用する。

ビュー

コントローラーのアクション

フォームリクエストクラスの生成

以下のコマンドでFormRequestを継承したクラスを作成する。

php artisan make:request リクエストクラス名

リクエストクラスは、app/Http/Requestsディレクトリー下に作成される。

リクエストクラスの内容。ユーザー認証のためのauthorize()メソッドと、バリデーションのためのrules()メソッドが定義されている。

アクションの引数の変更

  • コントローラーの先頭でリクエストクラスをuseでインポート
  • コントローラーのアクションのメソッドインジェクションをRequestからリクエストクラスに変更
  • validate()メソッドはアクションには書かない

今回の例の場合。

ユーザー認証の記述

準備中。認証された場合はauthorize()メソッドの戻り値がtrue

バリデーションルールの記述

rules()メソッドの戻り値の配列に、バリデーションルールを書く。

準備したコントローラーのバリデーション部分をリクエストクラスに記述。

エラーメッセージのカスタマイズ

フォームリクエストのmessages()メソッドをオーバーライドすることで、メッセージのカスタマイズができる。

参考:Laravel 6.x バリデーション~エラーメッセージのカスタマイズ

Laravel – マスアサインメント

概要

  • マスアサインメントによって、フォームからPOSTされたパラメーターを一括してモデルの属性にセットして、データベースに書き込める
  • ただしセットできる属性をモデルの$fillable配列に限定列挙する必要がある
  • モデルのインスタンス生成時に、セットできるパラメーターをonlyメソッドで限定列挙できる
  • $fillableのほかに$guarded配列も指定できて、こちらは指定した属性をモデルのセットの際に排除する

準備

以下のようなフォームがあって、namecommentをPOSTする。

POSTはコントローラーのstore()メソッドにルーティングされる。

ルーティング先のコントローラーでは、モデルのインスタンスを生成し、その属性にPOSTされたパラメーターをセットしてデータベースに書き込む。

マスアサインメント

Requestのall()メソッド

Request引数のall()メソッドで得られる内容を確認。

CSRF対策のトークンを含んだパラメーターの配列が得られる。

$request->all()の結果を、モデルインスタンスのfill()メソッドによって属性にセット。dd()で内容を確認してみる。

エラー。

マスアサインメントのためにはfillable属性に加えなければならないと言われる。

モデルの$fillableプロパティー

モデルの定義で、$fillable配列に取得したいプロパティーを列挙する。

先ほどのコードの実行結果。エラーがなくなる。dd()でプロパティーがセットされたインスタンスの内容を確認。

  • fillableプロパティーに2つのパラメーター名がセットされている
  • attributesに属性とその内容が配列としてセットされている
  • guardedは指定しておらず、サイズ1、内容'*'の配列となっている

以上を踏まえて、fillableがセットされたモデルのマスアサインメントとデータベース登録を以下に例示。

マスアサインメントの記述方法

マスアサインメントの書き方には複数あって、同じ結果が得られる。create()スタティックメソッドは、インスタンスの生成とデータベースの書き込みを一つのメソッドで行う。

Requestのonly()メソッドによる限定

意図しないパラメーターの追加を避けるため、all()メソッドではなくonly()メソッドでパラメーターを限定列挙できる。

 

Git/GitHub

Git

基本

コミット

ブランチ

リモート

GitHub

スタートアップ

操作