首頁 > 文章 > 資料庫 > mysql日期用什麼類型

mysql日期用什麼類型

青灯夜游原創: 2022-06-15 16:49:0824285瀏覽

有5種：1、YEAR，用來表示年，格式為「YYYY」；2、TIME，用於只需要時間資訊的值，格式為「HH:MM:SS」；3 、DATE，用於僅需要日期值時，格式為“YYYY-MM-DD”；4、DATETIME，用於需要同時包含日期和時間資訊的值，格式“YYYY-MM-DD HH:MM:SS” ；5、TIMESTAMP，與DATETIME類似，用於需要同時包含日期和時間資訊的值。

mysql日期用什麼類型

本教學操作環境：windows7系統、mysql8版本、Dell G3電腦。

MySQL 中有多個表示日期的資料類型：YEAR、TIME、DATE、DTAETIME、TIMESTAMP。

# 類型名稱	日期格式	日期範圍	儲存需求
# YEAR	YYYY	# 1901 ~ 2155	# 1 個位元組
TIME	# HH:MM:SS	-838:59:59 ~ 838:59:59	3 個位元組
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	# 3 個位元組
DATETIME	# YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 個位元組
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 個位元組

YEAR 類型

YEAR 類型是一個單字節類型，用於表示年，在儲存時只需要 1 個位元組。可以使用各種格式指定 YEAR，如下所示：

以 4 位元字串或 4 位元數字格式表示的 YEAR，範圍為 '1901'～'2155'。輸入格式為 'YYYY' 或 YYYY，例如，輸入 '2010' 或 2010，插入資料庫的值均為 2010。
以 2 位元字串格式表示的 YEAR，範圍為 '00' 到 '99'。 '00'～'69' 和 '70'～'99' 範圍的值分別轉換為 2000～2069 和 1970～1999 範圍的 YEAR 值。 '0' 與 '00' 的作用相同。插入超過取值範圍的值將轉換為 2000。
以2 位數字表示的YEAR，範圍為1～99。1～99 和70～99 範圍的值分別轉換為2001～2069 和1970～1999 範圍的YEAR值。請注意，在這裡 0 值將轉換為 0000，而不是 2000。

提示：兩位整數範圍與兩位字串範圍稍有不同。例如，插入 3000 年，讀者可能會使用數位格式的 0 表示 YEAR，實際上，插入資料庫的值為 0000，而不是所希望的 3000。只有使用字串格式的 '0' 或 '00'，才可以正確地解釋為 3000，非法 YEAR值將會轉換為 0000。

TIME 類型

TIME 類型用於只需要時間資訊的值，在儲存時需要 3 個位元組。格式為 HH:MM:SS。 HH 表示小時，MM 表示分鐘，SS 表示秒。

TIME 類型的值範圍為-838：59：59～838：59：59，小時部分如此大的原因是TIME 類型不僅可以用來表示一天的時間（必須小於24 小時），也可能是某個事件過去的時間或兩個事件之間的時間間隔（可大於24 小時，或甚至為負）。

可以使用各種格式指定 TIME 值，如下所示。

'D HH：MM：SS' 格式的字串。也可以使用這些「非嚴格」的語法：'HH：MM：SS'、'HH：MM'、'D HH' 或 'SS'。這裡的 D 表示日，可以取 0～34 之間的數值。在插入資料庫時，D 被轉換為小時保存，格式為 “D*24 HH”。
'HHMMSS' 格式、沒有間隔符號的字串或 HHMMSS 格式的數值，假定是有意義的時間。例如，'101112' 被理解為'10：11：12'，但是 '106112' 是不合法的（它有一個沒有意義的分鐘部分），在存儲時將變為 00：00：00。

提示：為 TIME 欄位指派簡寫值時應注意：如果沒有冒號，MySQL 解釋值時，假設最右邊的兩位表示秒。（MySQL 解釋 TIME 值為過去的時間而不是目前的時間）。例如，讀者可能認為 '1112' 和 1112 表示 11：12：00（即 11 點過 12 分鐘），但MySQL 將它們解釋為 00：11：12（即 11 分 12 秒）。同樣 '12' 和 12 被解釋為00：00：12。相反，TIME 值中如果使用冒號則肯定被看作當天的時間，也就是說，'11：12' 表示 11：12：00，而不是 00：11：12。

範例

讓我們來看看在TIME表中使用資料類型的範例。

首先，建立一個新表命名為tests 一個由四個欄位：id，name，start_at，和end_at。 start_at和end_at欄位的資料類型是TIME。

CREATE TABLE tests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    start_at TIME,
    end_at TIME
);

其次，在tests表中插入一行。

INSERT INTO tests(name,start_at,end_at)
VALUES('Test 1', '08:00:00','10:00:00');

第三，從tests表中查詢資料。

SELECT 
    name, start_at, end_at
FROM
    tests;

mysql日期用什麼類型

DATE 類型

DATE 類型用於只需要日期值時，沒有時間部分，儲存時需要3 個位元組。日期格式為 'YYYY-MM-DD'，其中 YYYY 表示年，MM 表示月，DD 表示日。

在賦值 DATE 類型的欄位時，可以使用字串類型或數字類型的資料插入，只要符合 DATE 的日期格式即可。如下所示：

以'YYYY-MM-DD' 或'YYYYMMDD' 字元中格式表示的日期，取值範圍為'1000-01-01'～'9999- 12-3'。例如，輸入 '2015-12-31' 或 '20151231'，插入資料庫的日期為2015-12-31。
以 'YY-MM-DD' 或者 'YYMMDD' 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：'00～69' 范围的年值转换为 '2000~2069'，'70~99' 范围的年值转换为 '1970～1999'。例如，输入 '15-12-31'，插入数据库的日期为 2015-12-31；输入 '991231'，插入数据库的日期为 1999-12-31。
以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。
使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

示例

我们来看看下面的例子。

首先，创建一个新表名为people，其中出生日期列为DATE数据类型。

CREATE TABLE people (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL
);

接下来，在people表中插入一行。

INSERT INTO people(first_name,last_name,birth_date)
VALUES('John','Doe','1990-09-01');

然后，查询数据来自people表。

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;

mysql日期用什麼類型

之后，使用两位数年份格式将数据插入people表中。

INSERT INTO people(first_name,last_name,birth_date)
VALUES('Jack','Daniel','01-09-01'),
      ('Lily','Bush','80-09-01');

在第一行中，我们使用01（范围00-69）作为年份，因此MySQL将其转换为2001年。在第二行中，我们使用80（范围70-99）作为年份，MySQL将其转换为1980年。

最后，我们可以从people表中查询数据，以检查数据是否已根据转换规则进行转换。

SELECT 
    first_name, 
    last_name, 
    birth_date
FROM
    people;

mysql日期用什麼類型

DATETIME 类型

DATETIME 类型用于需要同时包含日期和时间信息的值，在存储时需要 8 个字节。日期格式为 'YYYY-MM-DD HH:MM:SS'，其中 YYYY 表示年，MM 表示月，DD 表示日，HH 表示小时，MM 表示分钟，SS 表示秒。

在给 DATETIME 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATETIME 的日期格式即可，如下所示。

以 'YYYY-MM-DD HH：MM：SS' 或者 'YYYYMMDDHHMMSS' 字符串格式表示的日期，取值范围为 '1000-01-01 00：00：00'～'9999-12-3 23：59：59'。例如，输入 '2014-12-31 05：05：05' 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。
以 'YY-MM-DD HH：MM：SS' 或者 'YYMMDDHHMMSS' 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，'00～79' 范围的年值转换为 '2000～2079'，'80～99' 范围的年值转换为 '1980～1999'。例如，输入 '14-12-31 05：05：05'，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。
以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

DATETIME值可以包括具有YYYY-MM-DD HH:MM:SS[.fraction]例如的格式的尾随小数秒到微秒 2015-12-20 10:01:00.999999。包含小数秒精度时，DATETIME值需要更多存储空间，如下表所示：

分数秒精度	存储 (字节)
0	0
1, 2	1
3, 4	2
5, 6	3

例如，2015-12-20 10:01:00.999999 需要8个字节，5个字节用于2015-12-20 10:01:00 ，3个字节用于.999999 而2015-12-20 10:01:00.9 只需要6个字节，1个字节用于小数秒精度。

TIMESTAMP 类型

TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01'UTC～'2038-01-19 03：14：07'UTC。在插入数据时，要保证在合法的取值范围内。

提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；
而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 '00：00：00'，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

MySQL TIMESTAMP以UTC值存储。但是，MySQL将DATETIME值存储为没有时区的值。我们来看下面的例子。

首先，将当前连接的时区设置为+00:00。

SET time_zone = '+00:00';

接下来，创建一个表命名timestamp_n_datetime是由两列组成：ts与dt用TIMESTAMP和DATETIME使用下面的语句类型。

CREATE TABLE timestamp_n_datetime (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ts TIMESTAMP,
    dt DATETIME
);

然后，将当前日期和时间插入 timestamp_n_datetime 表的两个ts和每dt列，

INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());

之后，从timestamp_n_datetime表中查询数据。

SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;

mysql日期用什麼類型

DATETIME和TIMESTAMP列中的值都相同。

最后，将连接的时区设置为+03:00并再次从timestamp_n_datetime表中查询数据。

SET time_zone = '+03:00';
 
SELECT 
    ts, 
    dt
FROM
    timestamp_n_datetime;

mysql日期用什麼類型

如您所见，TIMESTAMP列中的值不同。这是因为当我们更改时区时，TIMESTAMP列以UTC格式存储日期和时间值，根据新时区调整列的值。

这意味着如果您使用TIMESTAMP数据存储日期和时间值，则在将数据库移动到位于不同时区的服务器时应认真考虑。

【相关推荐：mysql视频教程】

以上是mysql日期用什麼類型的詳細內容。更多資訊請關注PHP中文網其他相關文章！

陳述：

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

上一篇：mysql可以建立聯合索引嗎下一篇：mysql可以建立聯合索引嗎

看更多