複数の主観をもったテーブル

このパターンは、世間で量産されている。
隠れたアンチパターンです。

簡単にいうと、
主体が全く異なる、STI(Single Table Inheritance)が適応されている、状態です。

実例は、以下のようなテーブルが該当します。

id	value	type
1	東京都	都道府県
2	埼玉県	都道府県
…n	沖縄県	都道府県
n + 1	女性	性別
n + 2	男性	性別
n + 3	不明	性別

なんだかわからん.なんか

上記はひどい例ですが、
時には、都道府県。
時には、性別。
と、少ない共通点で、変幻自在です。
※ ある意味素敵。

あえて、ここで、
テーブル名を記載しなかったのは、
この記事を読んだ皆さんに、このテーブル名を考えてほしいからです。

きっと、abstractsなどといった、他の人が解釈しにくいものになるでしょう。

そうです、
このパターンは、アプリケーションで利用される際、混乱を招く原因になります。

上記の理由から、私は、アンチパターンと考えています。

ただし、テーブル名が明確な場合は、その限りではありません。

artistsテーブル

id	name	type
1	Francis Albert "Frank" Sinatra	jazz
2	David Warren Brubeck	jazz
3	The Prodigy	electric
4	Prodigy	hip hop

上記の例ですと、
ミュージシャンを表しているので、ひどいSTIではありませんし、アプリケーションのコードに、
落とし込まれた際も、以下のようにまだ人間に解釈可能です。

# rubyです。こんばんわ
Artists.jazz_artists

いかがでしょうか、まだわかりますよね。

とはいえ、物理的に利用しなければならない場合もあるのが現実です。
そこで、メリットとデメリットを以下に記載します。

メリット

• テーブル数が少ないから、ER図が書きやすい、だから、管理者は楽です。

デメリット

• 種別を判別する、typeが可変長文字列だから、大きくなるかも。
• SQLでの、制約がしにくい、だから不整合に気づきにくいし、そしてバグる。
• もちろん、検索パフォーマンスが悪い。
• 管理者は、ER図は書きやすいが、意味が不明瞭だから、開発者は、解釈しにくい。

table

join

取り出す

開始日と最終契約終了日などをテーブルに入れただけで年度ごとの更新日の数十日前から通知されたい的な需要があったのですが自動で更新したかった年度ごとの更新日を求めるSQLの部分の備忘録。
タイミングが、日付が過去になった直後に更新される前提。

mysql> select concat(DATE_FORMAT(DATE_ADD(t13_c5,INTERVAL 1 YEAR), '%Y-'), DATE_FORMAT(LAST_DAY(t13_c3), '%m-%d')) from testdb.table1 where t13_c1=80027xxx;
+------------------------------------------------------------------------------------------------------+
| concat(DATE_FORMAT(DATE_ADD(t13_c5,INTERVAL 1 YEAR), '%Y-'), DATE_FORMAT(LAST_DAY(t13_c3), '%m-%d')) |
+------------------------------------------------------------------------------------------------------+
| 2019-05-31                                                                                           | 
+------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> select * from testdb.table1 where t13_c1=80027xxx\G
*************************** 1. row ***************************
         t13_id: 31
      t13_valid: 1
   t13_reg_time: 1552648117
   t13_mod_time: NULL
t13_reg_user_id: 6
t13_mod_user_id: NULL
         t13_c1: 80027xxx
         t13_c2: 
         t13_c3: 2016-05-24
         t13_c4: 2021-05-31
         t13_c5: 2018-03-31　#ここを出した値にしたいだけ

DATE_FORMATは年だけ、または月日だけ出すのを定義、
DATE_ADDでカラム値の1年後をだす、
LAST_DAYでカラム値の月末をだす
concatで文字列連結

過去を出したいときはDATE_SUBみたいだけど今回特に使わなかった。
日付はいってるカラムの型はDATE型。
うっかりintでunixtimeとか入ってるときはcast(t3_c3 as date)で型変換かFROM_UNIXTIME関数でもフォーマット指定できるようです。

unixtimeをdateコマンドでDATE型っぽく変換するには以下のとおり。
date +%Y-%m-%d --date "1552648117"

shellスクリプト上で分岐処理で差分を求めたくてわざわざunixtimeで出したいときは、UNIX_TIMESTAMPという関数で出せました。

select t13_c1,UNIX_TIMESTAMP(t13_c4),UNIX_TIMESTAMP(t13_c5) from ${dbname}.${tblname} where t13_c4 > now() and t13_c5 < now();

select UNIX_TIMESTAMP(now());で出るのをdateで出したいときに。
date +%s
特定の日付をunixtimeにしたいならdate --date '2012/12/1' +%sなど。

通知対象の抽出は残り日のdatediffのbetweenで最初に0いれると過去の日付でない。

select t13_c1 as enrolle_num,t13_c2 as company,t13_c5 as current_end,t13_c4 as final_end,datediff(t13_c5,now()) 'valid days' from ${dbname}.${tblname} where datediff(t13_c5,now()) between 0 and 60;

やりたい内容を適当な文字列でググるとだいたい関数とか用例とか出てくるの楽だし実行してみてその通り動くのは楽しいですね。

今回時刻の型は扱わなかったけどカラムのDEFAULTなどの指定をCURRENT_TIMESTAMPとかにできるのは時刻の型なんだなーというのはなんとなく見かけました。
やってみた記事も個人的にはとっかかりにわかりやすくて好きですが、マニュアルみたほうが情報量がだいぶ多いかなとも思いました。

参考
https://hit.hateblo.jp/entry/mysql/concat-pipe
http://nowork.jugem.jp/?eid=12
https://qiita.com/sakura1116/items/3fef2ca5b5280eae22e8
http://mysql.javarou.com/dat/000849.html
https://www.dbonline.jp/mysql/type/index4.html
https://qiita.com/ykawakami/items/2449a24e3b82ff0cbab6
https://dev.mysql.com/doc/refman/5.6/ja/timestamp-initialization.html
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html

BLOBデータにINSERT

やりたいこと

作成したBLOBのcolumnにデータをINSERTしたい。
BLOBはバイナリに変換して登録してあげる必要があります。

登録したデータの確認方法は下記参照
【OracleDB】BLOBデータをVARCHAR2にキャストして内容確認

コード

変換にはutl_raw.cast_to_rawを使用します。

  INSERT INTO table1(
    emd_cd,
    emp_name
  ) VALUES (
    utl_raw.cast_to_raw('111000'),
    utl_raw.cast_to_raw('田中太郎')
  );

SELECT結果でINSERTする(INSERT SELECT)

やりたいこと

SELECT結果をINSERT文に使用します。
INSERT文の基本的な構成は以下のようになります。
(
[更新対象列名]
)VALUES(
[値]
)

ここでは[値]の代わりにSELECTの結果をINSERTに使用します。
データの更新は下記を参照してください。
【PL/SQL】SELECT結果でUPDATEする(UPDATE SELECT)

INSERT INTO {テーブル}
( 
  {列1},{列2}
) VALUES(
  値1,値2
);

コード(列構成が異なるテーブルからのINSERT)

以下の例ではSELECT結果を2つの項目にINSERTしています。
組み合わせによっては、様々な情報を取得してINSERTに使用できます。
この時、列の数やデータ定義に注意してください。

--SELECTした結果でINSERTする
INSERT INTO emp_mst
(address,add_no) 
SELECT address,add_no 
     FROM addno 
     WHERE add_no = '111-1111';

コード(列構成が同じテーブルからのINSERT)

以下の例では同じテーブル構成を持つ「emp_mst2」から「emp_mst」へ全件のデータがINSERTされます。
データを絞りたい場合にはSELECT文のWHERE句を追記します。

--SELECTした結果でINSERTする
INSERT INTO emp_mst
SELECT *
FROM emp_mst2;