信頼はずっと、挑戦はもっと。

お問い合わせ
TEL:03-3496-3888

BLOG コアテックの社員ブログ (毎週月曜~金曜更新中)

2019

13

1月

データベースに関するTips

テクログ

どうも、もう少しで50歳(半世紀)になる大西です。

開発に携わっていると同じ結果を導くにあたり多くの手法が存在する事に気付くだろう。1つしか手法を知らず後になって損をしていた事に気付く・・・そのような経験も多いのではないだろうか。

データベースについては30年以上前から今だに「そのような手法があるのか」と感心する事が多々ある。今回はデータベースの中でも MySQL に関しての「その方法でも出来るね」について記そう。



■ TIMESTAMP および DATETIME の自動初期化および更新機能

さて、まずはこれだ。

最近でもカラムとして登録日時と最終更新日時を持たせている設計を多く見かける。きちんとシステム上必要なのであれば良いのだが「とりあえず入れとけ」は止めて頂きたいと常に思っているカラム達だ。

このカラムが存在するにも関わらず無視しているプログラムは論外として、INSERT 時に「登録日時」と「最終更新日時」を、UPDATE 時に「最終更新日時」を、それぞれ NOW() を使用して値をセットするプログラムを多く見かけるのだが、経験上データベース側の日時値ではなくプログラム側の日時値でお願いしたい(プログラム側で日時データを作りその値をデータベースに適用するようにして欲しいのだ)。

特に MySQL の日時については Ver8.0 になっても 2038年問題は残ったままのようだしプログラム側の日時値でお願いしたいものだ。

何はともかくこれら私の希望を一旦棚の上に置くとして、登録日時と最終更新日時カラムが単にそのレコードの登録/更新の日時を表すだけならば NOW() を使うのは避けるべきだと述べておく。

ではどうするのか。

それは INSERT の時は登録日時と最終更新日時を、UPDATE の時は最終更新日時だけを自動でセットするようにすれば良い。


テーブルを見てみよう。

Create Table: CREATE TABLE `TBL_TEST` (
    `key` varchar(32) NOT NULL,
    `value` text NOT NULL,
    `update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

update_at カラムはデフォルト値として CURRENT_TIMESTAMP を、そして ON UPDATE CURRENT_TIMESTAMP で更新時はその時の日時をセットするカラムにしている。

create_at カラムは update_at と同様だが更新時には反応しないカラムにしている。


INSERT してみる。

mysql> INSERT INTO `TBL_TEST` (`key`,`value`) VALUES ('test','hoge');
mysql> select * from `TBL_TEST`;
+------+-------+---------------------+---------------------+
| key  | value | update_at           | create_at           |
+------+-------+---------------------+---------------------+
| test | hoge  | 2018-01-13 02:01:04 | 2018-01-13 02:01:04 |
+------+-------+---------------------+---------------------+

update_at、create_at 共に自動セットされている。


UPDATE してみる。

mysql> UPDATE `TBL_TEST` SET value='fuga' WHERE `key`='test';
mysql> SELECT * FROM TBL_TEST;
+------+-------+---------------------+---------------------+
| key  | value | update_at           | create_at           |
+------+-------+---------------------+---------------------+
| test | fuga  | 2018-01-13 02:01:38 | 2018-01-13 02:01:04 |
+------+-------+---------------------+---------------------+

update_at のみ自動セットされている。


最近はデータベース等のレコード結果を KVS などにキャッシュし高速化を図る手法も多く見かける。その場合も NOW() は避けよう(繰り返すが本心はプログラム側で生成した日時値をセットして欲しい)。何にせよ SQL もスッキリするしこちらの方がエレガントなので是非とも検討/実践して欲しい。



■ SQL_CALC_FOUND_ROWS

次はちょっとした Tips のようなものだ。

検索結果などを表示する場合にはページングというテクニックを使用する場合があると思う。その場合ページングのため(結局何ページ分必要なのかを先に知る必要があるため)総レコード数を知る必要がある。この際に同じ WHERE句 で SELECT と COUNT(*) を発行して1ページ分のレコードと総レコード数を取得するプログラムを多く見かける。

このような場合は SQL_CALC_FOUND_ROWS と FOUND_ROWS() を使うと良いだろう。

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE `hoge`>100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

最初の SELECT で条件に合致するデータセット(1ページ分)が取得出来て、次の SELECT で条件に合致する(OFFSET / LIMIT 無関係の)レコード総数が取得可能だ。

SELECT を2回発行する事に変わりはないのだが、こちらの方がエレガントであろう。



今回は案件でも多用しているであろう MySQL についてその Tips 的な事柄を書いてみた。

この他にも MySQL には寿司ビール問題やハハパパ/アルファベットの大文字小文字問題といったキャラセット/コレーションに関する問題もある(これらの問題について知らないというエンジニアは今すぐ調べて欲しい)。MySQL もこれら問題に対応すべくデフォルトの設定を見直すなど出来るところから対応してくれている。プログラムやシステム、ミドルウェアのデフォルト設定などが「そうなっている」のには必ず理由があるものだ。その理由を知るだけでもスキルアップに繋がると思う。手元を見返し「そうなっている」理由を是非とも考えて欲しい。

それではまた。

この記事を書いた人

マスオさん

ひろくん

所 属:
WEBインテグレーション事業部
出身地:
神奈川県
仕事内容:
部長・マネージメント