TIMESTAMP DEFAULT NOT NULL

by

TIMESTAMP列はDEFAULTでは、NOT 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>

null_timestamp

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>

timestamp_check

日付列に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)

timestamp_digit

無効な日付を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>

timestamp_digit_2

Posted in: apache, DB, Linux, MYSQL, security, system全般, シェルスクリプト, パフォーマンス, 設定, 運用 ⋅ Tagged: , , , ,

Comments are closed.

Post Navigation