MySQL技術內幕:時間和日期數據類型

2020-12-16 IT168

        【IT168 技術】MySQL資料庫中有五種與日期和時間有關的數據類型，各種日期數據類型所佔空間如表2-1所示。

　　DATETIME和 DATE

　　DATETIME佔用8位元組，是佔用空間最多的一種日期類型。它既顯示了日期，同時也顯示了時間。其可以表達的日期範圍為「1000-01-01 00：00：00」到「9999-12-31 23：59：59」。

　　DATE佔用3位元組，可顯示的日期範圍為「1000-01-01」到「9999-12-31」。

　　在MySQL資料庫中，對日期和時間輸入格式的要求是非常寬鬆的，以下的輸入都可以視為日期類型。

　　·2011-01-01 00：01：10

　　·2011/01/01 00+01+10

　　·20110101000110

　　·11/01/01 00@01@10

　　其中，最後一種類型中的「11」有些模稜兩可，MySQL資料庫將其視為2011還是1911呢?下面來做個測試：

mysql> SELECT CAST('11/01/01 00@01@10' AS DATETIME) AS datetime\G;
*************************** 1. row ***************************
datetime: 2011-01-01 00:01:10
1 row in set (0.00 sec)

　　可以看到資料庫將其視為離現在最近的一個年份，這可能不是一個非常好的習慣。如果沒有特別的條件和要求，還是在輸入時按照標準的「YYYY-MM-DD HH：MM：SS」格式來進行。在MySQL 5.5版本之前(包括5.5版本)，資料庫的日期類型不能精確到微秒級別，任何的微秒數值都會被資料庫截斷，例如：

mysql> CREATE TABLE t ( a DATETIME );
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO t SELECT '2011-01-01 00:01:10.123456';
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 2011-01-01 00:01:10
1 row in set (0.00 sec)

　　不過MySQL資料庫提供了函數MICROSECOND來提取日期中的微秒值，示例如下：

mysql> SELECT MICROSECOND('2011-01-01 00:01:10.123456')\G;
*************************** 1. row ***************************
MICROSECOND('2011-01-01 00:01:10.123456'): 123456
1 row in set (0.00 sec)

　　有意思的是， MySQL的CAST函數在強制轉換到DATETIME時會保留到微秒數，不過在插入後同樣會截斷，示例如下：

mysql> SELECT CAST('2011-02-01 00:01:10.123456' AS DATETIME) D\G;
*************************** 1. row ***************************
D: 2011-02-01 00:01:10.123456
1 row in set (0.00 sec)

mysql> INSERT INTO t
    ->SELECT CAST('2011-02-01 00:01:10.123456' AS DATETIME);
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 2011-01-01 00:01:10
*************************** 2. row ***************************
a: 2011-02-01 00:01:10
2 rows in set (0.00 sec)

　　然而從MySQL 5.6.4版本開始，MySQL增加了對秒的小數部分(fractional second)的支持，具體語法為：

　　其中，type_name的類型可以是TIME、DATETIME和TIMESTAMP。fsp表示支持秒的小數部分的精度，最大為6，表示微秒(microseconds);默認為0，表示沒有小數部分，同時也是為了兼容之前版本中的TIME、DATETIME和TIMESTAMP類型。對於時間函數，如CURTIME()、SYSDATE()和UTC_TIMESTAMP()也增加了對fsp的支持，例如：

mysql> SELECT CURTIME(4) AS TIME\G;
*************************** 1. row ***************************
TIME: 10:22:37.4456
1 rows in set (0.00 sec)

　　注意　MariaDB 5.3版本就對TIME、DATETIME、TIMESTAMP類型的微秒部分進行了支持。詳細的說明可以從http://kb.askmonty.org/en/microseconds-in-mariadb中得到。

${PageNumber}

　　TIMESTAMP

　　TIMESTAMP和DATETIME顯示的結果是一樣的，都是固定的「YYYY-MM-DD HH:MM:SS」的形式。不同的是，TIMESTAMP佔用4位元組，顯示的範圍為「1970-01-01 00:00:00」UTC到「2038-01-19 03:14:07」UTC。其實際存儲的內容為「1970-01-01 00:00:00」到當前時間的毫秒數。

　　注意　UTC 協調世界時，又稱世界統一時間、世界標準時間和國際協調時間。它從英文Coordinated Universal Time和法文Temps Universel Cordonné而來。

　　TIMESTAMP類型和DATETIME類型除了在顯示時間範圍上有所不同外，還有以下不同：

　　在建表時，列為TIMESTAMP的日期類型可以設置一個默認值，而DATETIME不行。

　　在更新表時，可以設置TIMESTAMP類型的列自動更新時間為當前時間。

　　首先來看一個默認設置時間的例子。

mysql> CREATE TABLE t
    -> ( a INT,
    ->   b TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t (a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 1
b: 2011-02-02 16:42:57
1 row in set (0.01 sec)

　　接著來看一個執行UPDATE時更新為當前時間的例子。

mysql> CREATE TABLE t
     -> ( a INT ,
     ->  b TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
     -> );

mysql> INSERT INTO t SELECT 1,CURRENT_TIMESTAMP;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 1
b: 2011-02-02 16:45:40
1 row in set (0.01 sec)

# 等待一段時間

mysql> UPDATE t SET a=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 2
b: 2011-02-02 16:47:39
1 row in set (0.00 sec)

　　可以發現在執行UPDATE操作後，b列的時間由原來的16:45:40更新為了16:47:39。如果執行了UPDATE操作，而實際上行並沒有得到更新，那麼是不會更新b列的，例如：

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 2
b: 2011-02-02 16:47:39
1 row in set (0.00 sec)

mysql> UPDATE t SET a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM t\G;
*************************** 1. row ***************************
a: 2
b: 2011-02-02 16:47:39
1 row in set (0.00 sec)

　　可以看到執行UPDATE t SET a=2並沒有改變行中的任何數據，顯示Changed:0表示該行實際沒有得到更新，故b列並不會進行相應的更新操作。

　　當然，可以在建表時將TIMESTAMP列設為一個默認值，也可以設為在更新時的時間，例如：

mysql> CREATE TABLE t (
   -> a INT,
   -> b TIMESTAMP DEFAULT ON UPDATE CURRENT_TIMESTAMP
   ->)ENGINE=InnoDB;

${PageNumber}

　　YEAR和TIME

　　YEAR類型佔用1位元組，並且在定義時可以指定顯示的寬度為YEAR(4)或YEAR(2)，例如：

mysql> CREATE TABLE t ( a YEAR(2));
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO t SELECT '1990';

mysql> SELECT * FROM t;
+------+
| a    |
+------+
|   90 |
+------+
1 row in set (0.00 sec)

　　對於YEAR(4)，其顯示年份的範圍為1901～2155;對於YEAR(2)，其顯示年份的範圍為1970～2070。在YEAR(2)的設置下，00～69代表2000～2069年。

　　TIME類型佔用3位元組，顯示的範圍為「-838：59：59」～「838：59：59」。有人會奇怪為什麼TIME類型的時間可以大於23。因為TIME類型不僅可以用來保存一天中的時間，也可以用來保存時間間隔，同時這也解釋了為什麼TIME類型也可以存在負值。和DATETIME類型一樣，TIME類型同樣可以顯示微秒時間，但是在插入時，資料庫同樣會進行截斷操作，例如：

mysql> CREATE TABLE t ( a TIME );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t SELECT '14:40:20.123456';
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM t;
+----------+
| a        |
+----------+
| 14:40:20 |
+----------+
1 row in set (0.00 sec)

相關焦點

  • MySQL那些與日期和時間相關的函數
    【IT168 技術】日期函數可能是比較常使用的一種函數。下面介紹一些最為常用的日期函數及一些容易忽略的問題。　　1. NOW、CURRENT_TIMESTAMP和SYSDATE　　這些函數都能返回當前的系統時間，它們之間有區別嗎?先來看個例子。
  • MySQL--SQL的數據類型
    MySQL中的數據類型主要分為三類：數值型、字符串型和日期時間型。      MySQL的數值數據類型主要分為兩類，一類是整數，一類是小數。每個子類型支持大小不同的數據，並且可以指定欄位中的值是否有正負之分或者用0來補。      對於整數通常使用int、bigint、smallint，對於小數類型通常使用Decimal。
  • MySQL中「詭異」的TIMESTAMP數據類型
    數據類型打交道，有時候TIMESTAMP類型看起來莫名其妙，測試環境都測試OK了上了預發就直接報錯了；有時候TIMESTAMP類型看起來又很詭異，表中的真實數據跟開發人員提交的表結構又南轅北轍並非所需要的；本篇文章將抽絲剝繭帶你重新認識一下這個熟悉又陌生的TIMESTAMP類型。
  • MySQL進階之路:日期類型datetime和timestamp區別在哪裡?
    一、相同點datetime和timestamp都可以表示 YYYY-MM-DD HH:MM:SS 這種年月日時分秒格式的數據。並且從MySQL5.6.4之後這兩者都可以包含秒後的小數部分，精度最高為微妙（6位）。
  • mysql field json MySQL JSON 類型數據操作
    test"'='1';https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.htmlmysql>SET@j=』{「a」: 1, 「b」: 2, 「c」: {「d」: 4}}』;mysql>SET@j2=『1』;mysql>SELECTJSON_CONTAINS
  • MySQL教程之MySQL數據類型詳解
    ，我們一定要為合適的列選取合適的數據類型，即到底用不用得到這種數據類型？日期類型接著我們看一下MySQL中的日期類型，MySQL支持五種形式的日期類型：date、time、year、datetime、timestamp，用一張表格總結一下這五種日期類型：下面我們還是用SQL來驗證一下：drop table if exists test_time;create table test_time
  • MySQL環境配置和入門講解!
    本文中介紹的結構型資料庫MySQL的基礎知識，能夠讓你快速入門MySQL，具體內容包含：一、相關術語資料庫database：以某種有組織的方式存儲的數據集合。通常是一個文件或者一組文件。表table：結構化的文件，用來存儲某種特定類型的數據。存儲在表中的數據是同一種類型的數據或者清單。
  • MySQL環境配置和10分鐘快速入門
    本文中介紹的結構型資料庫MySQL的基礎知識，能夠讓你快速入門MySQL，具體內容包含：一、相關術語資料庫database：以某種有組織的方式存儲的數據集合。通常是一個文件或者一組文件。表table：結構化的文件，用來存儲某種特定類型的數據。存儲在表中的數據是同一種類型的數據或者清單。
  • 學習MySQL中日期與時間類型發現:細節決定成敗
    在前面的學習中我們提到過欄位類型這個概念，本篇的主題就是來講一種常用而相對複雜的類型：日期與時間。MySQL中表示日期與時間的數據類型有很多種，但主要的不外乎下面五種：記住上面表中的「範圍」信息很重要，因為日期與時間的任何操作都是依賴於此進行延展。本文主要以案例的方式展開。
  • mysql和oracle語法異同
    一、mysql裡的ifnull(a,b)對應oracle的nvl(a,b);二、日期比較：mysql: 可以用Date類型的日期進行比較時間比較。oracle:必須用to_date()函數和to_char()函數配合轉換成統一格式的日期字符串,然後進行比較。
  • mysql UNIX時間戳與日期的相互轉換
    UNIX時間戳轉換為日期用函數： FROM_UNIXTIME() selectFROM_UNIXTIME(1156219870); 日期轉換為UNIX時間戳用函數： UNIX_TIMESTAMP()
  • MySQL mysqldump 數據導出詳解
    為了正確恢復，該選項應該用於導出mysql資料庫和依賴mysql資料庫數據的任何時候。mysqldump  -uroot -p --all-databases --flush-privileges--force在導出過程中忽略出現的SQL錯誤。
  • 選擇合適的 MySQL 日期時間類型來存儲你的時間
    構建資料庫寫程序避免不了使用日期和時間，對於資料庫來說，有多種日期時間欄位可供選擇，如 timestamp 和 datetime 以及使用 int 來存儲 unix timestamp。不僅新手，包括一些有經驗的程式設計師還是比較迷茫，究竟我該用哪種類型來存儲日期時間呢？
  • MySqlODBC進行MYsql和MSsql7的數據轉換
    使用MySql ODBC後就比較好辦，可以使用MSSQL7的管理工具，也可以使用mysql的管理工具，更可以使用其它方的管理工具 這裡介紹一個使用SQL7的MMC的方法 ，將MSsql7的數據轉化為Mysql的資料庫，將源和目的反之，就可以將Mysql的資料庫轉化為Mssql7的資料庫。
  • 去 BAT 面試,總結了這 55 道 MySQL 面試題!
    存儲引擎稱為表類型，數據使用各種技術存儲在文件中。技術涉及：15、Mysql驅動程序是什麼？四種TEXT類型TINYTEXTTEXTMEDIUMTEXT和LONGTEXT它們對應於四種BLOB類型，並具有相同的最大長度和存儲要求。BLOB和TEXT類型之間的唯一區別在於對BLOB值進行排序和比較時區分大小寫，對TEXT值不區分大小寫。
  • MySQL 常用語法總結
    大多數資料庫對象可以下面的命令刪除：　　drop object_name　　mysql> DROP TABLE tb1;　　5．執行查詢　　查詢是使用最多的SQL命令。查詢資料庫需要憑藉結構、索引和欄位類型等因素。大多數資料庫含有一個優化器(optimizer)，把用戶的查詢語句轉換成可選的形式，以提高查詢效率。
  • MySQL面試題集錦,據說知名網際網路公司都用
    存儲引擎稱為表類型，數據使用各種技術存儲在文件中。技術涉及：Storage mechanismLocking levelsIndexingCapabilities and functions.15、MySQL驅動程序是什麼?
  • MYSQL資料庫操作案例
    MySQL 創建資料庫CREATE DATABASE 資料庫名;以下命令簡單的演示了創建資料庫的過程，數據名為 RUNOOB:[root@host]# mysql -u root -p MySQL 數據類型MySQL支持多種類型，大致可以分為三類：數值、日期/時間和字符串(字符)類型。
  • 在MySQL中使用XML數據—數據格式化
    SQL語句，它是預編譯的，這意味著Prepared Statement執行時，資料庫不用再編譯SQL了，這樣可以有效減少執行時間。name");SET @qry = CONCAT("SELECT ExtractValue(@xml, \"//row[2]/field[1]\") AS ", @header, ";");PREPARE stmt FROM @qry;EXECUTE stmt;DEALLOCATE PREPARE stmt;　　現在我們看到的數據格式就是和我們平時看到的一樣了
  • 關於mysql中運算符與函數的概述
    運算符是告訴mysql執行特定的算術或邏輯操作的符號，運算符連接著各個表達式中的操作數，其作用是用來指明對操作數進行的運算。>1.算術運算符：2.比較運算符：3.邏輯運算符：MySQL數值函數：1.獲取整數的函數：2.四捨五入的函數：3.截斷函數：4.取模：mysql