TIMESTAMP列はDEFAULTでは、NOT NULLに設定されます。
あらかじめNULL値が入る事を想定している場合は明示的にNULLを
指定してテーブルを作成する。
以下テーブル作成後のALTER TABLEにて属性変更している。
mysql> CREATE TABLE timestamp_null (
-> data_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> i INT
-> );
Query OK, 0 rows affected (0.16 sec)
mysql> desc timestamp_null;
+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| data_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)
mysql> ALTER TABLE timestamp_null
-> MODIFY data_time TIMESTAMP NULL
-> DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc timestamp_null;
+-----------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+-------------------+-----------------------------+
| data_time | timestamp | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| i | int(11) | YES | | NULL | |
+-----------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)
mysql>
mysql> INSERT INTO timestamp_null (data_time, i) VALUES (NULL, 10);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT data_time, i FROM timestamp_null;
+-----------+------+
| data_time | i |
+-----------+------+
| NULL | 10 |
+-----------+------+
1 row in set (0.00 sec)
mysql> INSERT INTO timestamp_null (data_time, i) VALUES (now(), 10);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT data_time, i FROM timestamp_null;
+---------------------+------+
| data_time | i |
+---------------------+------+
| NULL | 10 |
| 2009-07-15 04:31:42 | 10 |
+---------------------+------+
2 rows in set (0.00 sec)
mysql>
TIMESTAMP関連の検証
mysql> CREATE TABLE timestamp_chk (data_time TIMESTAMP NULL);
Query OK, 0 rows affected (0.00 sec)
mysql> desc timestamp_chk;
+-----------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-----------+------+-----+---------+-------+
| data_time | timestamp | YES | | NULL | |
+-----------+-----------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> INSERT INTO timestamp_chk VALUES (NULL);
Query OK, 1 row affected (0.00 sec)
mysql> select * from timestamp_chk;
+-----------+
| data_time |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)
mysql> INSERT INTO timestamp_chk VALUES ('文字列');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 | ← 文字列は対象外なので"0" Valueがinsertされる。
+---------------------+
2 rows in set (0.00 sec)
mysql>
日付列に12 Digit と14 DigitのNumberをInsertした時の違い
mysql> INSERT INTO timestamp_chk VALUES (200202082139);
Query OK, 1 row affected (0.01 sec)
mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
+---------------------+
3 rows in set (0.00 sec)
mysql> INSERT INTO timestamp_chk VALUES (20020208213900);
Query OK, 1 row affected (0.00 sec)
mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
+---------------------+
4 rows in set (0.00 sec)
無効な日付をINSERTした場合
- ※2月31日は存在しない日付
mysql> INSERT INTO timestamp_chk VALUES ('2002-02-31 23:59:59');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
| 0000-00-00 00:00:00 |
+---------------------+
5 rows in set (0.00 sec)
- ※60秒が指定されている。
mysql> INSERT INTO timestamp_chk VALUES ('2002-02-28 23:59:60');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'data_time' at row 1 |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from timestamp_chk;
+---------------------+
| data_time |
+---------------------+
| NULL |
| 0000-00-00 00:00:00 |
| 2020-02-02 08:21:39 |
| 2002-02-08 21:39:00 |
| 0000-00-00 00:00:00 |
| 0000-00-00 00:00:00 |
+---------------------+
6 rows in set (0.00 sec)
mysql>