SQL 資料型態 TIMESTAMP

這邊來看一下 TIMESTAMP 這個資料型態.

測試環境為 Ubuntu16.04 x86_64 虛擬機.

建立一個有 timestamp 欄位的資料表 table ,預設值為 current_time stamp (日期時間函數,功能與 NOW 一樣,都是回傳目前系統的日期時間),on update CURRENT_TIMESTAMP 資料更新時,time stamp 也一同更新.

root@ubuntu:~# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 35
Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Ubuntu 16.04

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database testdb;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> use testdb;
Database changed
MariaDB [testdb]> create table employee (Name char(20),ts timestamp);
Query OK, 0 rows affected (0.03 sec)

MariaDB [testdb]> DESCRIBE employee; 
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| Name  | char(20)  | YES  |     | NULL              |                             |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

建立一筆新資料時,time stamp 會自動產生,值為目前日期+時間.

MariaDB [testdb]> INSERT INTO employee (Name) VALUES ('Ben');
Query OK, 1 row affected (0.01 sec)

MariaDB [testdb]> select * from employee;
+------+---------------------+
| Name | ts                  |
+------+---------------------+
| Ben  | 2018-01-05 01:15:13 |
+------+---------------------+
1 row in set (0.00 sec)

資料更新時,ts (time stamp) 也會更新.

MariaDB [testdb]> update employee set Name="Ben10" where Name="Ben";
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [testdb]> select * from employee;
+-------+---------------------+
| Name  | ts                  |
+-------+---------------------+
| Ben10 | 2018-01-07 23:48:16 |
+-------+---------------------+
1 row in set (0.01 sec)

如果不希望更新時把 ts(time stamp) 也同步更新,可以使用 alter 把 on update CURRENT_TIMESTAMP 移除.

MariaDB [testdb]> alter table employee change column ts ts timestamp default CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> describe employee;
+-------+-----------+------+-----+-------------------+-------+
| Field | Type      | Null | Key | Default           | Extra |
+-------+-----------+------+-----+-------------------+-------+
| Name  | char(30)  | YES  |     | NULL              |       |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP |       |
+-------+-----------+------+-----+-------------------+-------+
2 rows in set (0.00 sec)

恢復一樣用 alter 修改.

MariaDB [testdb]> alter table employee change column ts ts timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [testdb]> describe employee;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type      | Null | Key | Default           | Extra                       |
+-------+-----------+------+-----+-------------------+-----------------------------+
| Name  | char(30)  | YES  |     | NULL              |                             |
| ts    | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)

其他與日期與時間相關的資料型態

Data Type “Zero” Value
DATE ‘0000-00-00’
TIME ’00:00:00′
DATETIME ‘0000-00-00 00:00:00’
TIMESTAMP ‘0000-00-00 00:00:00’
YEAR 0000

與日期與時間相關的函式:
CURDATE() – 回傳目前日期.
CURTIME() – 回傳目前時間.

更多關於 日期與時間的函數請參考 https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

沒有解決問題,試試搜尋本站其他內容

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料