MySQLの日付型にINSERTできる文字列の書式

自己紹介で「技術系のことを書きたい」的なことを言ったのに、それらしいことをさっぱり書いてなかったので猛省。

MySQLに縁があり、ちょっと調べてみたことがあるので書きなぐってみます。

題して「MySQLの日付型についてシリーズ」

今回は「日付型のカラムに値を入れるときにどんな書式の文字列なら使えるか」です。

当然、YYYY-MM-DD とか YYYY/MM/DD が使えるのはほとんどの人がご存知のとおりだし、正しい日付にヴァリデートしてから使っている人には何の役にも立たない情報です。

最近はおそらく何も考えなくてもSQL文に渡すときにはすでにヴァリデートしてあるっていうケースの方が実際には多いんじゃないかとは思いますが、それはそれ。

せっかく調べたんだから吐き出させてください。

ということで以下の文章は

INSERT INTO `table` (`date`) VALUES ( ? );

の?にバインドする文字列としてみていただきたい。

使える区切り文字

「-」と「/」はもちろんOK。

それ以外はというと・・・

・実は半角記号ならなんでもよいようだ。

・しかも何個重ねても処理される。（ただし、「\」は偶数個連続にする必要がある。）

・記号はDDの後ろ側につけても問題ないが、YYYYの前につけると正しく処理されなくなる。

・全角文字や半角英数は区切り文字として評価されない。

・区切り文字で区切られている場合、年月日はそれぞれ何桁で表現されても整数の範囲が間違っていなければ処理される。

以下に「2009/03/03」としてINSERTしようとしたときに正しく処理されるものが○、そうでないものに×をつけた。

　○ 2009*03+03

　○ 2009-/*+=03(()03

　○ 2009/03/03/

　× /2009/03/03/

　× 2009\03\03

　○ 2009\\03\\03

　× 2009a03a03

　× 2009年03月03日

　○ 2009-3-3

　○ 00002009-00003-00003

　× 2009000-03-03

年を2桁で表現する場合

YY-MM-DDの書式の場合はYYの評価範囲が

 00～69 → 2000～2069、 70～99 → 1970～1999 となっている。

よって、「09-03-03」は「2009/03/03」として処理される。

年が1桁と3桁以上の場合はこの評価はされない。

9-03-03   → 0009-03-03

209-03-03 → 0209-03-03

ただし、MySQLのDATE型がサポートするのは 1000-01-01～9999-12-31 らしく、

このような日付は挿入できてしまうが「保障されない」らしい。

区切り文字なし

「20090303」と区切りなしでもOK。

「090303」としても「2009/03/03」として評価される。

※注　「2009/1/3」を「200913」のように区切り文字を省くと、「2020/09/13」として処理される。

日付として評価されなかった場合

先述で「×」なパターンをDATEの列に保存しようとした場合にどうなるかというと、

MySQLの場合、何も設定していなければ「0000-00-00」として登録される。

「設定」というのはずばり「SQL-MODE」だ。

SQL-MODEの話は先人のブログに詳しくあるので紹介しておく。(後述)

が、日付についてだけ絞って「0000-00-00」という文字列（実在しない日付）も含めて、改めて別に書きたいと思う。

DATE()関数

先述の○×判定は「DATE()関数で正しく処理できるかどうか」の基準とほぼ同じだと思われる。(0日付を除く)

DATE()関数は引数の文字列を日付型に変換し、日付として評価できない場合はNULLを返す。

よって、

INSERT INTO `table` (`date`) VALUES ( DATE( ? ) );

 としておくと、DATE( ? )の結果が日付になれば日付が挿入されるし、日付として評価できない文字列だったらNULLが挿入される。

列がNULLを許可していない、もしくはNULLが大嫌いならNULL判別をかぶせて0000-00-00に変換してしまえばいい。

INSERT INTO `table` (`date`) VALUES ( COALESCE( DATE( ? ), '0000-00-00'));

(これならSQL-MODEが「STRICT」でもエラーを出さずに済む。もちろん0日付を許可してあれば。)

いや、例えばの話ですよ。

ちゃんとヴァリデートしておけばこんなことしなくてもいいわけですから。

検証環境

OS:WindowsXP

　（OS入れ替える前に検証して、やっと記事にしたんです）

MySQL:5.1.42

文字コード:UTF-8

参考・出展

[mysql]これだけは覚えておきたい!!MySQL の６つの自動変換 sakaik氏

[mysql]MySQLの自動変換を丁重にお断りするためのたった１種類の呪文 sakaik氏

MySQL 5.1 リファレンスマニュアル 4.2.6. SQL モード MySQL公式

MySQL 5.1 リファレンスマニュアル 10.3. 日付と時刻タイプ MySQL公式

今度は「実在しない日付との比較」などについて書きたい。

間違いがあったら指摘コメントをくださると喜びます。