20190523のMySQLに関する記事は2件です。

投稿日：2019-05-23T22:09:46+09:00

MySQL で timestamp 型の差分を出すには

株式会社オズビジョンのユッコ (@terra_yucco) です。
今日はトラブル対応中に出くわした MySQL の小ネタ。

トラブルの内容

抽出ロジックにミスがあり、特定のアクションをしてから 60 分後までにはお知らせが飛ぶ予定だったのですが、それが一部の条件で飛ばなくなっていました。

対応自体は処理を修正して完了しましたが、影響を受けた人の抽出タスクが残ります。

2 つの日付の差分を取りたい

特定のアクションをしてから 60 分後までにはお知らせが飛ぶ予定

なので

特定のアクションをした日時

実際に対応後お知らせが飛んだ日時

がわかれば、取得は可能です。
幸い、この 2 つの値は timestamp 型で DB に入っていました。

MySQL で timestamp 型の差分を出したい

【誤】減算

timestamp 型は名称からして Unix の Epoch 秒なのかと思っていたので、安易に取ろうとしたのがこちら。
mysql> select user_id, register_date, notify_date, (notify_date - register_date) from actions where (notify_date - register_date) > 3600 limit 10;
+----------+---------------------+---------------------+-------------------------------+
| user_id  | register_date       | notify_date         | (notify_date - register_date) |
+----------+---------------------+---------------------+-------------------------------+
| 99999901 | 2019-05-23 19:35:07 | 2019-05-23 20:16:15 |                          8108 |
| 99999902 | 2019-05-23 19:35:08 | 2019-05-23 20:16:17 |                          8109 |
| 99999903 | 2019-05-23 19:40:07 | 2019-05-23 20:16:17 |                          7610 |
| 99999904 | 2019-05-23 19:40:08 | 2019-05-23 20:16:17 |                          7609 |
| 99999905 | 2019-05-23 19:40:09 | 2019-05-23 20:16:18 |                          7609 |
| 99999906 | 2019-05-23 19:40:09 | 2019-05-23 20:16:20 |                          7611 |
| 99999907 | 2019-05-23 19:40:11 | 2019-05-23 20:16:20 |                          7609 |
| 99999908 | 2019-05-23 19:45:09 | 2019-05-23 20:16:20 |                          7111 |
| 99999909 | 2019-05-23 19:45:10 | 2019-05-23 20:16:21 |                          7111 |
| 99999910 | 2019-05-23 19:50:07 | 2019-05-23 20:16:23 |                          6616 |
+----------+---------------------+---------------------+-------------------------------+
10 rows in set (0.00 sec)
What!?
一番下とかどう考えても 25 分くらいしか空いてないんですが、差分がすごいことに。

種明かし

回答はここにありました。

地中へのはしご > MySQLでDATETIME型の時刻差を計算

そのまま減算
数字列の差分になります。

確かに
[root@localhost ~]# php -a
Interactive shell

php > echo (20190523201623 - 20190523195007) . PHP_EOL;
6616
mysql> select register_date, notify_date, (notify_date - register_date) from actions where user_id = 99999910;

+---------------------+---------------------+-------------------------------+
| register_date       | notify_date         | (notify_date - register_date) |
+---------------------+---------------------+-------------------------------+
| 2019-05-23 19:50:07 | 2019-05-23 20:16:23 |                          6616 |
+---------------------+---------------------+-------------------------------+
TIMESTAMPDIFF()

今回こちらを使いました。

Synopsis

12.7 日付および時間関数 > TIMESTAMPDIFF()
TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)
atetime_expr2 − datetime_expr1 を返します。datetime_expr1 と datetime_expr2 は、日付または日付時間式です。式の一方が日付で、他方が日付時間にすることもできます。日付値は、必要に応じて時間部分が '00:00:00' の日付時間として処理されます。結果 (整数) の単位は、unit 引数で指定されます。

単純な減算とは、引数の指定が逆になります。

Result

これで期待する結果を取ることが出来ました！
mysql> select register_date, notify_date, timestampdiff(second, register_date, notify_date) from actions where user_id = 99999910;

+---------------------+---------------------+---------------------------------------------------+
| register_date       | notify_date         | timestampdiff(second, register_date, notify_date) |
+---------------------+---------------------+---------------------------------------------------+
| 2019-05-23 19:50:07 | 2019-05-23 20:16:23 |                                              1576 |
+---------------------+---------------------+---------------------------------------------------+
その他の方法

使えなかったもの

DATEDIFF()

今回は日付ではなく分単位だったため

SUBTIME()

動きそうだと思ったけど NULL が返却された

使えそうなもの

TIMEDIFF()

以下のような結果なので使えそうだけど、結果から「60 より大きい」を取得するには更に変換が必要そう
mysql> select register_date, notify_date, timediff(notify_date, register_date) from actions where user_id = 99999910;

+---------------------+---------------------+--------------------------------------+
| register_date       | notify_date         | timediff(notify_date, register_date) |
+---------------------+---------------------+--------------------------------------+
| 2019-05-23 19:50:07 | 2019-05-23 20:16:23 | 00:26:16                             |
+---------------------+---------------------+--------------------------------------+
TO_SECONDS()

まどろっこしいですが使えそう

ただし、日付が古すぎる場合はうまく動かないそうです
mysql> select register_date, notify_date, (to_seconds(notify_date) - to_seconds(register_date)) from actions where user_id = 99999910;

+---------------------+---------------------+-------------------------------------------------------+
| register_date       | notify_date         | (to_seconds(notify_date) - to_seconds(register_date)) |
+---------------------+---------------------+-------------------------------------------------------+
| 2019-05-23 19:50:07 | 2019-05-23 20:16:23 |                                                  1576 |
+---------------------+---------------------+-------------------------------------------------------+
UNIX_TIMESTAMP()

私の実行系では TO_SECONDS() と同じ結果が得られました

Conclusion

timestamp 型のカラムを利用して日付の差分を取得したい場合は、そのまま減算しちゃダメ。
MySQL には各種日付用の関数があるので、適切なものを使いましょう。

　 Tweet
Qiitaで続きを読む

投稿日：2019-05-23T13:48:49+09:00

ジョジョを例に学ぶDB正規化及び、中間テーブルという概念について

とある案件でやりたいことがうまくできなかった

　とある商品に関連する紹介ページに対して、その商品に関するジャンルを紹介用文言の中に表示するというものなのですが、これを表示させるのにDB上のフラグを0or1で取る他、商品IDとジャンルIDが別のテーブルに存在するため、どうやって取ってくるか悩むというものです。今思えば、前に別の案件で名前とそれに関連するIDを調べる際に直にSQLクエリでjoin（テーブル結合）させたことがあるので、中間テーブルの存在まではいかなくとも、joinさせればいい、という考え方にいきたかったですね。ここにたどり着けなかった理由は、ORMであるActiveRecordとにらめっこしていたからなのですが、これに関してはまた別途記事にしていきたいと思います。
　さて、とうわけで今回、圧倒的に技術知識不足を痛感した瞬間がタイトルにもある、データベースの正規化と中間テーブルです。

筆者がイメージし易いように例としてあげるDBテーブルに全てジョジョを採用しています。ジョジョ（主に今やってる第５部）を知らないと何このカラム？となる恐れがあります。

もしかしたらどこか解釈間違っているかもしれません・・・お気づきになられた方、ぜひ指摘をお願いします！！

そも、非正規化のデータベースは何が問題で、正規化が重要なのは何故か？

　一言で言ってしまうと、データの重複や整合性を保つためにデータベースを設計する必要があるからである。正規化によって、データの更新、追加、などがやり易くなる。やり易くなる理由は、非正規なデータベースで起き得る問題を解決してくれるからである。

具体的にどんな問題が起こるのかというと、

id character_name gang_team

1 Blono Bucciarati underling_team

2 Leone Abbacchio underling_team

3 Peschi assassin_team

　上記のような顧客テーブルがあったとして、gang_teamカラムのunderling_teamが重複しています。これを繰り返し項目と呼ぶ。
こういったデータベースで起こり得る弊害は、

事前登録不可

重複更新/重複登録

関係性の喪失

が挙げられる。

事前登録不可

　例えば、先のテーブルの場合、gang_teamを登録するためには、character_nameも用意しなければならないということ。加えて。character_nameに属するデータがない場合、gang_teamを作れない弊害もある。
　こんな状態にするならば、テーブルを分けて後でテーブル結合した方が合理的・・・と、早くもテーブル結合の利点と直結する感じ。

重複更新/重複登録

　例えば、ブチャラティが幹部クラスに昇進したので、ブチャラティチーム全員をgang_teamのunderling_teamからexecutive_teamへと更新する必要が出ます。ブチャラティが幹部になったおかげで、ブチャラティのチームにいる皆んながUndeling_team（下っ端チーム）からexecutive_team（幹部チーム）へと昇格するからです。
　この時に、gang_teamのunderling_teamを更新したいと思った時に、上記のテーブルだと２つのレコードに対してUpdateをしなければならないので、もし、更新に抜け漏れがあった場合、データの整合性が取れなくなる(重複更新)。別テーブルで分けておけば、更新はもっと楽になるなーというのもまた頷けるところ。また、データベースに新たなギャングスタがunderling_teamの所属だった場合、またunderling_teamと同じデータを入れなくてはならなくなる(重複登録)。

関係性の喪失

　上記のテーブルのように、Peschiを削除する場合、一緒にassassin_teamというgang_teamまで消失してしまう。これは、かつて存在していたgang_teamを残すことができない設計である。故に、データの整合性に欠けるデータベース、つまり設計の弱いデータベースと言える。

　以上の理由から、データベースの正規化は非常に重要であるということが理解できる。

正規化における基本概念

　正規化されたデータベースを見る上で必要になってくる基本視点。

　関数従属性

　上記のテーブルで言えば、idを使えば、character_nameを特定することができる。もし仮に同性同盟の人間がいたとしても、idを使えば調べることができる。こう言った関係を関数従属性と呼び、関係性を→で表す。
id → character_name
これは、『character_nameが、idに関数従属している』ことを意味する。

関数の従属性には以下のような推論の法則が働く。

反射律

　ベン図を考えると、イメージしやすい。例えば、柴犬が犬の部分集合だったとする。すると、それは、犬→柴犬という従属関係が成立することを意味する。そして、柴犬が成立すれば、犬であることが成立するということも意味する。

増加律

増加律からは、下記のテーブルを使って考える。

id character_name stand_name

1 Giorno Giovana Gold Experience

2 Guido Mista Sex Pistols

3 Narancha Ghirga Aerosmith

id→character_nameが成立するのならッ、別のカラムを加えたとしてもその従属関係は崩れないッッ！
{id, stand_name}→{character_name, stand_name}が成り立つということだッ。

推移律

先のテーブルを使って同じく、これも考えてみる。
id→character_nameかつ、character_name→stand_nameならば、id→stand_nameである。ということ。
1に従属するGiornoとGiornoに従属するGold Experienceならば、Gold Experienceは1に従属するッてことじゃあないかッ！

合併律

さっきの推移律の理屈を使うと・・・
id→character_nameかつ、id→stand_nameならば、id→{character_name, stand_name}ということができる。

分解律

単純に合併律の逆なだけだッッ！！

　完全関数従属と部分関数従属と推移関数従属

関数従属性には種類がある。カラム同士の関係性には、相互的なものと、そうでないものがある。

完全関数従属

faculty student_ID student_name addres country

Medical 1 Chocolata Roma ×××-×× Italy

Archeology 1 Jonathan Joestar London ○○○-○○ United Kingdom

Marine_biology 4 Kuujou Joutarou Moriou-town △△ Japan

{faculty, student_ID}→student_nameという関係になるが、２つのカラムを利用しなければ、生徒の名前が分からないようになっている。上記のテーブルではスペースの問題のため書いていないが、医学部には他にもたくさん生徒がいるだろうし、クラス番号が1の生徒は、他の学部にもいるということを考えると、facluty→student_IDや、student_name→student_IDは成立しないということになる。両方が存在しない限り、検索をかけられない関数従属を、完全関数従属と呼ぶ。

部分関数従属

部分関数従属は、{student_ID, student_name}→addressのような関係のことで、完全関数従属と異なり、どちらか一方が成立していれば、従属関係を再現できる。上記のテーブルでは、student_ID→addressは成り立たないが、student_name→addressは成立する。

推移関数従属

１つの従属関係が導き出されることによって、成立し、最初と最後のカラム同士では従属関係が結ばれないようになる従属関数。ここでは、student_name→addressであり、この関係が成り立つということは、address→countryが導き出される。つまり、student_name→address→countryということになり、student_nameからcountryが導き出されることはない従属関係があることを意味する。

　キー属性

キー属性とは、今までやってきたカラムの従属性を導くためのポイントになり得る性質を持つということを意味する言葉です。

候補キー

テーブルに於いてカラムとカラムの組み合わせによってレコードを特定できるようになるカラムのこと全般を指す。

主キー（primary_key）

候補キーの中で、1レコードから、最も多くのもしくは全てのフィールドを取ってこれるように設計されたカラムを指す。

代替キー

候補キーの中で主キー程ではないが、特定するのに使えるカラムを指す。

非キー

候補キー以外のカラムを指す。

外部キー

他のテーブルの主キーや候補キーを参照できるカラムを指す。

キーの種類から、上記の２つのテーブルにおけるカラムが何キーになるか見ていきます。
以下の２つのテーブルを用いて見ていきます。

id character_name stand_name style

1 Risottot Nero Metallica solo

2 Formaggio little feat solo

3 Prosciutto the grateful dead pair

4 Tizano talking-head pair

id tel_num gang_team

1 090-2243-4324 assassin_team

2 090-3432-4255 assassin_team

3 090-3457-8456 assassin_team

4 020-1242-2466 boss_superiors

まず、候補キーの内、主キーは間違いなくidです。そして、候補キーの内、character_nameや、stand_name、tel_numは代替キーになり得ると思います。gang_teamやstyleは非キーになると思われます。

また、別テーブルを参照する時に使う外部キーは主キーでもあるidになります。

ここまでが、正規化の必要性と、正規化するにあたり持っていなくてはならない前提知識になります。

正規形には段階がある

ようやく、ここから正規化理論に本格的に入っていきます。正規形には段階があり、非正規形、第一正規形、第二正規形、第三正規形、第四正規形、第五正規形からなる。数字が上に進むにつれ、正規化が進んでいく。レベル感覚でいくと、全部第五正規形でいいじゃないかと思われるかもしれないが、正規化が進めば進むほど、データがテーブルで分けられ、テーブルの数が増えるということになりデータ取得の際のレスポンスがダウンするというデメリットが発生するので、扱うデータによってベストな正規形を選択する必要がある。
　今回は、第三正規形までをアウトプットします。残りはまたおいおい加筆していきたいと思います。

非正規形

　非正規形のデータベースは以下のような繰り返しグループがあるデータベースのことを指します。繰り返しグループとは、繰り返し項目とは違い、同一レコード内に複数のデータが入り込んでいる状態を指す。この状態を非正規形と呼ぶ。繰り返し項目のあるデータベースは非正規形とは呼ばないので、区別をしっかりしておくこと。

id stand_user stand_name power speed range persistence precision Growth gang_num

1 Vinegar Doppio King Crimson A A C C E C 0002

Diavolo 0001

2 Ghiaccio White Album A C C A E E 0035

3 Pannacotta Fugo Purple Haze A B C E E B 0456

stand_userにはVinegar DoppioとDiavoloが一緒になって入っています。これが非正規データベースです。

第一正規形

第一正規形は、非正規形データベースの繰り返しグループを修正してあげるだけの状態を指す。つまり、先の非正規形データベースを例にすると、

id stand_user stand_name power speed range persistence precision growth gang_num

1 Vinegar Doppio King Crimson A A C C E C 0002

2 Diavolo King crimson A A C C E C 0001

3 Ghiaccio White Albu A C C A E E 0035

4 Pannacotta Fugo Purple Haze A B C E E B 0456

ということになる。

第二正規形

ここから、テーブルを分離させていく。第一正規形では、部分関数従属がスタンドのステータスを表すpower~growthまで存在した。{id, stand_name}→power...growth
故にこれを全て完全関数従属になるようにテーブルを分ける。

Stand_Userテーブル

id stand_user gang_num

1 Vinegar Doppio 0002

2 Diavolo 0001

3 Ghiaccio 0035

4 Pannacotta Fugo 0456

Standテーブル

stand_user stand_name power speed range persistence precision growth

Vinegar Doppio King Crimson A A C C E C

Diavolo King crimson A A C C E C

Ghiaccio White Album A C C A E E

Pannacotta Fugo Purple Haze A B C E E B

部分関数従属性を排除してあるデータベースを第二正規形という。

第三正規形

第二正規形の状態から、推移関数従属性を排除したものを第三正規形と呼ぶ。今回の場合は、stand_userからstand_nameへ、そして、そこからスタンドのステータスを示すpower~growthを割り出すことができるという推移関数従属性が存在するので、ここの関係を削除していく。

Stand_User-gang_numテーブル

id stand_user gang_num

1 Vinegar Doppio 0002

2 Diavolo 0001

3 Ghiaccio 0035

4 Pannacotta Fugo 0456

Stand_user-Stand_nameテーブル

stand_user stand_name

Vinegar Doppio King Crimson

Diavolo King Crimson

Ghiaccio White Album

Pannacotta Fugo Purple Haze

Stand_name-Stand_statusテーブル

stand_name power speed range persistence precision growth

King Crimson A A C C E C

White Album A A C C E C

Purple Haze A B C E E B

これで第三正規化されたデータベースができたことになります。

中間テーブルについて

　今回の場合、Vinegar Doppio,Diavoloとpower~growthが多対多関係ですので、ここに中間テーブルを作成できます。
　中間テーブルは、多対多のテーブルが存在する時に作られるもので、２つのカラムの関係性をこの関係性を示したい２つ以外のカラムで共通しているカラムを用いてそぞれを分類し、JOINさせて出来上がります
この中間テーブル用のIDも割り振ります。

中間テーブル

id stand_user power speed range persistence precision growth

1 Vinegar Doppio A A C C E C

2 Diavolo A A C C E C

3 Ghiaccio A A C C E C

4 Pannacotta Fugo A B C E E B

参考文献

データ正規化をお寺と仏像で考えてみた

『データベース技術　第6版』　アイテック情報技術教育研究所編著

id	character_name	gang_team
1	Blono Bucciarati	underling_team
2	Leone Abbacchio	underling_team
3	Peschi	assassin_team

id	character_name	stand_name
1	Giorno Giovana	Gold Experience
2	Guido Mista	Sex Pistols
3	Narancha Ghirga	Aerosmith

faculty	student_ID	student_name	addres	country
Medical	1	Chocolata	Roma ×××-××	Italy
Archeology	1	Jonathan Joestar	London ○○○-○○	United Kingdom
Marine_biology	4	Kuujou Joutarou	Moriou-town △△	Japan

id	character_name	stand_name	style
1	Risottot Nero	Metallica	solo
2	Formaggio	little feat	solo
3	Prosciutto	the grateful dead	pair
4	Tizano	talking-head	pair

id	tel_num	gang_team
1	090-2243-4324	assassin_team
2	090-3432-4255	assassin_team
3	090-3457-8456	assassin_team
4	020-1242-2466	boss_superiors

id	stand_user	stand_name	power	speed	range	persistence	precision	Growth	gang_num
1	Vinegar Doppio	King Crimson	A	A	C	C	E	C	0002
	Diavolo								0001
2	Ghiaccio	White Album	A	C	C	A	E	E	0035
3	Pannacotta Fugo	Purple Haze	A	B	C	E	E	B	0456

id	stand_user	stand_name	power	speed	range	persistence	precision	growth	gang_num
1	Vinegar Doppio	King Crimson	A	A	C	C	E	C	0002
2	Diavolo	King crimson	A	A	C	C	E	C	0001
3	Ghiaccio	White Albu	A	C	C	A	E	E	0035
4	Pannacotta Fugo	Purple Haze	A	B	C	E	E	B	0456

id	stand_user	gang_num
1	Vinegar Doppio	0002
2	Diavolo	0001
3	Ghiaccio	0035
4	Pannacotta Fugo	0456

stand_user	stand_name	power	speed	range	persistence	precision	growth
Vinegar Doppio	King Crimson	A	A	C	C	E	C
Diavolo	King crimson	A	A	C	C	E	C
Ghiaccio	White Album	A	C	C	A	E	E
Pannacotta Fugo	Purple Haze	A	B	C	E	E	B

id	stand_user	gang_num
1	Vinegar Doppio	0002
2	Diavolo	0001
3	Ghiaccio	0035
4	Pannacotta Fugo	0456

stand_user	stand_name
Vinegar Doppio	King Crimson
Diavolo	King Crimson
Ghiaccio	White Album
Pannacotta Fugo	Purple Haze

stand_name	power	speed	range	persistence	precision	growth
King Crimson	A	A	C	C	E	C
White Album	A	A	C	C	E	C
Purple Haze	A	B	C	E	E	B

id	stand_user	power	speed	range	persistence	precision	growth
1	Vinegar Doppio	A	A	C	C	E	C
2	Diavolo	A	A	C	C	E	C
3	Ghiaccio	A	A	C	C	E	C
4	Pannacotta Fugo	A	B	C	E	E	B

　 Tweet
Qiitaで続きを読む

20190523のMySQLに関する記事は2件です。

MySQL で timestamp 型の差分を出すには

トラブルの内容

2 つの日付の差分を取りたい

MySQL で timestamp 型の差分を出したい

【誤】減算

種明かし

TIMESTAMPDIFF()

Synopsis

Result

その他の方法

使えなかったもの

使えそうなもの

Conclusion

ジョジョを例に学ぶDB正規化及び、中間テーブルという概念について

とある案件でやりたいことがうまくできなかった

そも、非正規化のデータベースは何が問題で、正規化が重要なのは何故か？

事前登録不可

重複更新/重複登録

関係性の喪失

正規化における基本概念

関数従属性

反射律

増加律

推移律

合併律

分解律

完全関数従属と部分関数従属と推移関数従属

完全関数従属

部分関数従属

推移関数従属

キー属性

候補キー

主キー（primary_key）

代替キー

非キー

外部キー

正規形には段階がある

非正規形

第一正規形

第二正規形

第三正規形

中間テーブルについて

参考文献

日付別

タグ一覧

　関数従属性

　完全関数従属と部分関数従属と推移関数従属

　キー属性