前言

?歡迎來到小K的MySQL專欄，本節(jié)將為大家?guī)鞰ySQL數據類型簡介 | 整數 | 浮點 | 定點類型的分享?

---

0.數據類型簡介

數據類型（data_type）是指系統(tǒng)中所允許的數據的類型。MySQL 數據類型定義了列中可以存儲什么數據以及該數據怎樣存儲的規(guī)則。

數據庫中的每個列都應該有適當的數據類型，用于限制或允許該列中存儲的數據。例如，列中存儲的為數字，則相應的數據類型應該為數值類型。

如果使用錯誤的數據類型可能會嚴重影響應用程序的功能和性能，所以在設計表時，應該特別重視數據列所用的數據類型。更改包含數據的列不是一件小事，這樣做可能會導致數據丟失。因此，在創(chuàng)建表時必須為每個列設置正確的數據類型和長度。

MySQL 的數據類型有大概可以分為 5 種，分別是整數類型、浮點數類型和定點數類型、日期和時間類型、字符串類型、二進制類型等。

注意：整數類型和浮點數類型可以統(tǒng)稱為數值數據類型。

1 整數類型

1.1 類型介紹

整數類型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

類型	說明	大小(字節(jié))
TINYINT	很小的整數	1
SMALLINT	小的整數	2
MEDIUMINT	中等大小的整數	3
INT/INTEGER	普通大小的整數	4
BIGINT	大整數	8

1.2 可選屬性

1.2.1 M

M：表示顯示寬度，M的取值范圍是(0,255)。例如，INT(5)：當數據小于5位時，可以用字符填充 補滿所需寬度。該功能需要配置ZEROFILL使用，表示用 0 填滿寬度，否則指定顯示寬度無效。

注意：設置寬度不會對原來的數據或者插入數據產生任何影響，僅僅是顯示效果不同。從MySQL 8.0.17開始，整數類型不推薦使用顯示寬度屬性。

1.2.2 UNSIGNED

所有整數類型都有一個可選的屬性UNSIGNED，無符號整數類型的最小取值為0。所以，如果需要在MySQL中保存非負整數是，可以將整數類型設置為無符號類型。

1.3 使用場景

TINYINT：一般用于枚舉類型，比如系統(tǒng)設定取值范圍很小且固定的場景。

SMALLINT：可以用于較小范圍的統(tǒng)計數據，比如統(tǒng)計工廠的固定資產庫存數量等。

MEDIUMINT：用于較大整數的計算，比如車站每日的客流量等。

INT、INTEGER：取值范圍足夠大，一般情況下不用考慮超限問題，用的最多，比如商品編號。

BIGINT：只有當你處理特別巨大的整數時才會用到，比如雙十一的交易量、大型門戶網站點擊量、證券公司衍生產品持倉等。

1.4 如何選擇

在評估用哪種整數類型的時候，你需要考慮存儲空間和可靠性的平衡問題：一方面，用占用字節(jié)數少的整數類型可以節(jié)省存儲空間；另一方面，要是為了節(jié)省存儲空間，使用的整數類型取值范圍太小，一旦遇到超出取值范圍的情況，就可能引起系統(tǒng)錯誤，影響可靠性。

舉個栗子，商品編號采用的數據類型是INT。原因就在于，客戶門店中流通的商品種類較多，而且，每天都有舊的商品下架，新的商品上架，這樣不斷迭代，日積月累。

如果使用 SMALLINT 類型，雖然占用字節(jié)數比 INT 類型的證書少，但是卻不能保證數據不會超出 655345。相反，使用INT 就能確保有足夠大的取值范圍，不用擔心數據超出范圍影響可靠性。

你要注意的是，在實際工作中，系統(tǒng)故障產生的成本遠遠超過增加幾個字段存儲空間所產生的成本。因此，我建議你首先確保數據不會超過取值范圍，在這個前提下，再去考慮如何節(jié)省存儲空間。

2 浮點類型

2.1 類型介紹

浮點數類型包括 FLOAT、DOUBLE和REAL。

類型	說明	大小(字節(jié))
FLOAT	單精度浮點數	4
DOUBLE	雙精度浮點數	8
REAL	默認為DOUBLE	把SQL模式設定為“REAL_AS_FLOAT”，REAL就是FLOAT SET sql_mode = "REAL_AS_FLOAT"

2.2 精度說明

• MySQL允許使用非標準語法(其他數據庫未必支持，因此如果涉及到數據前移，則最好不要這么用)：FLOAT(M,D)或DOUBLE(M,D)。其中M稱為精度，表示總共的位數；D稱為標度，表示小數的位數。D<=M<=255，0<=D<=30。

  例如：定義為FLOAT(5,2)的一個列可以顯示為[-999.99，999.99]，超出這個范圍會報錯

• FLOAT 和 DOUBLE 在不指定精度時，默認會按照實際的精度（由計算機硬件和操作系統(tǒng)決定）。

• 注意：浮點類型也可以加UNSIGNED，但是不會改變數據范圍，例如：FLOAT(3,2) UNSIGNED任然只能表示0-9.99的范圍。

• 不管是否顯示設置了精度(M,D)，MySQL有自己的處理方案：

  • 如果存儲時，整數部分超出了范圍，則直接報錯。
  • 如果存儲時，小數點部分超出范圍，就分一下情況：
    • 若四舍五入后，整數部分沒有超出范圍，就可以存儲。如在FLOAT(5,2)列插入999.009，近似結果是999.01。
    • 若四舍五入后，整數部分超出范圍，則直接報錯。如在FLOAT(5,2)列插入999.995和-999.995都會報錯。

• 從MySQL8.0.17開始，FLOAT(M,D)和DOUBLE(M,D)用法在官方文檔中已經明確不推薦使用了，將來可能會被移除；另外關于浮點類型FLOAT和DOUBLE的UNSIGNED也不推薦使用了，將來也可能被移除。

浮點數相對于定點數的優(yōu)點是在長度一定的情況下，浮點數能夠表示更大的范圍；缺點是會引起精度問題。

最后再強調一下：在 MySQL 中，定點數以字符串形式存儲，在對精度要求比較高的時候（如貨幣、科學數據），使用 DECIMAL 的類型比較好，另外兩個浮點數進行減法和比較運算時也容易出問題，所以在使用浮點數時需要注意，并盡量避免做浮點數比較

3 定點類型

定點數類型為 DECIMAL。

類型	說明	大小(字節(jié))
DECIMAL/DEC	壓縮的“嚴格”定點數	M+2

DECIMAL(M,D)。其中M稱為精度，表示總共的位數；D稱為標度，表示小數的位數。0<=M<=65，0<=D<=30，D<M。例如：定義為DECIMAL(5,2)，表示該列的取值范圍是[-999.99，999.99]。

• DECIMAL(M,D)的最大值范圍與DOUELE類型一樣，但是有效的數據方位是由M和D決定的。DECIMAL的存儲空間并不固定，由精度M決定，總共占用的存儲空間為M+2個字節(jié)。也就是說，在一些對精度要求不高的場景下，比起占用同樣字節(jié)長度的定點數，浮點數表達的數值范圍可以更大。
• 定點數在MySQL內部是以字符串的形式進行存儲的，這就決定了它一定是精確的。
• 當DECIMAL類型不指定精度和標度時，其默認為DECIMAL(10,0)，當數據的精度超出了定點數的精度范圍時，則MySQL同樣會進行四舍五入。
• 浮點數 vs 定點數
  • 浮點數相對于定點數的有點是在長度一定的情況下，浮點類型取值范圍大，但是不精確，適用于需要取值范圍大，又可以容忍誤差的科學計算場景
  • 定點數類型取值范圍相對小，但是精準，沒有誤差，設置對于精度要求極高的場景(比如設計金額計算的場景)

4 日期/時間類型

包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。

類型	說明	日期范圍	大小(字節(jié))
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	1970-01-01 00:00:00 UTC ~ 2038-01-19 03:14:07 UTC	4

YEAR類型

YEAR 類型是一個單字節(jié)類型，用于表示年，在存儲時只需要 1 個字節(jié)?？梢允褂酶鞣N格式指定 YEAR，如下所示：

• 以 4 位字符串或者 4 位數字格式表示 YEAR，范圍為 ‘1901’～’2155’。輸入格式為 ‘YYYY’ 或者 YYYY，例如，輸入 ‘2010’ 或 2010，插入數據庫的值均為 2010。
• 以 2 位字符串格式表示的 YEAR，范圍為 ‘00’ 到 ‘99’?！?0’～’69’ 和 ‘70’～’99’ 范圍的值分別被轉換為 2000～2069 和 1970～1999 范圍的 YEAR 值。‘0’ 與 ‘00’ 的作用相同。插入超過取值范圍的值將被轉換為 2000。

從MySQL5.5.27開始，2位格式的YEAR已經不推薦使用。YEAR默認格式就是“YYYY”，沒必要寫成YEAR(4)。

CREATE TABLE test_year
(f1 YEAR,f2 YEAR(4)
);INSERT INTO test_year(f1) VALUES('2021'),(2022);
INSERT INTO test_year(f1) VALUES('2155');
-- out of range value for column 'f1' at row 1
INSERT INTO test_year(f1) VALUES('2156');
INSERT INTO test_year(f1) VALUES('69'),('70');	#2069 1970
INSERT INTO test_year(f1) VALUES(0),('0');

DATE 類型

DATE 類型用于僅需要日期值時，沒有時間部分，在存儲時需要 3 個字節(jié)。日期格式為 ‘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 解釋兩位年值的規(guī)則：‘00～69’ 范圍的年值轉換為 ‘2000~ 2069’，‘70~99’ 范圍的年值轉換為 ‘1970～1999’。例如，輸入 ‘15-12-31’，插入數據庫的日期為 2015-12-31；輸入 ‘991231’，插入數據庫的日期為 1999-12-31。
• 使用 CURRENT_DATE() 或者 NOW()，插入當前系統(tǒng)日期。

提示：MySQL 允許“不嚴格”語法：任何標點符號都可以用作日期部分之間的間隔符。例如，‘98-11-31’、‘98.11.31’、‘98/11/31’和’98@11@31’ 是等價的，這些值也可以正確地插入數據庫。

CREATE TABLE test_date1
(f1 DATE
);INSERT INTO test_date1 VALUES('2022-04-16'),('20220416'),(20220416);
INSERT INTO test_date1 VALUES('22-04-16'),('220416'),('69-04-16'),('690416'),('70-04-16'),('700416'),('99-04-16'),('990416');
INSERT INTO test_date1 VALUES(220416),(690416),(700416),(990416);
INSERT INTO test_date1 VALUES(CURRENT_DATE()),(NOW());

TIME 類型

TIME 類型用于只需要時間信息的值，在存儲時需要 3 個字節(jié)。格式為 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’ 是不合法的（它有一個沒有意義的分鐘部分），在存儲時將變?yōu)?00：00：00。
• 使用 CURRENT_TIME() 或者 NOW()，插入當前系統(tǒng)時間。

提示：為 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。

CREATE TABLE test_time1
(f1 TIME
);
INSERT INTO test_time1 
VALUES('10:35:30'),('2 10:35:30'),('10:35'),('2 10:35'),('1 35'),('30');
INSERT INTO test_time1 VALUES('103530'),(103530),(1035);
INSERT INTO test_time1 VALUES(NOW()),(CURRENT_TIME());

DATETIME 類型

DATETIME 類型用于需要同時包含日期和時間信息的值，在存儲時需要 8 個字節(jié)。日期格式為 ‘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’。
• 以 ‘YY-MM-DD HH：MM：SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期，在這里 YY 表示兩位的年值。與前面相同，‘00～79’ 范圍的年值轉換為 ‘2000～2079’，‘80～99’ 范圍的年值轉換為 ‘1980～1999’。
• 以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 數字格式表示的日期和時間。

提示：**MySQL 允許“不嚴格”語法：任何標點符號都可用作日期部分或時間部分之間的間隔符。**例如，‘98-12-31 11：30：45’、‘98.12.31 11+30+35’、‘98/12/31 113045’ 和 ‘98@12@31 11 ^ 30 ^ 45’ 是等價的，這些值都可以正確地插入數據庫。

CREATE TABLE test_datetime1
(dt DATETIME
);
INSERT INTO test_datetime1 VALUES('2022-04-16 18:50:30'),('20220416185030');
INSERT INTO test_datetime1 VALUES('99-04-16 18:50:30'),('990416185030'),('22-04-16 18:50:30'),('220416185030');
INSERT INTO test_datetime1 VALUES(20220202000000);
INSERT INTO test_datetime1 VALUES(CURRENT_TIMESTAMP()),(NOW());

TIMESTAMP 類型

TIMESTAMP 的顯示格式與 DATETIME 相同，顯示寬度固定在 19 個字符，日期格式為 YYYY-MM-DD HH：MM：SS，在存儲時需要 4 個字節(jié)。但是 TIMESTAMP 列的取值范圍小于 DATETIME 的取值范圍，為 '1970-01-01 00：00：01’UTC～’2038-01-19 03：14：07’UTC。在插入數據時，要保證在合法的取值范圍內。

提示：協調世界時（英：Coordinated Universal Time，法：Temps Universel Coordonné）又稱為世界統(tǒng)一時間、世界標準時間、國際協調時間。英文（CUT）和法文（TUC）的縮寫不同，作為妥協，簡稱 UTC。

TIMESTAMP 與 DATETIME 除了存儲字節(jié)和支持的范圍不同外，還有一個最大的區(qū)別是：

• DATETIME 在存儲日期數據時，按實際輸入的格式存儲，即輸入什么就存儲什么，與時區(qū)無關；
• 而 TIMESTAMP 值的存儲是以 UTC（世界標準時間）格式保存的，存儲時對當前時區(qū)進行轉換，檢索時再轉換回當前時區(qū)。即查詢時，根據當前時區(qū)的不同，顯示的時間值是不同的。

提示：如果為一個 DATETIME 或 TIMESTAMP 對象分配一個 DATE 值，結果值的時間部分被設置為 ‘00：00：00’，因此 DATE 值未包含時間信息。如果為一個 DATE 對象分配一個 DATETIME 或 TIMESTAMP 值，結果值的時間部分被刪除，因此DATE 值未包含時間信息。

CREATE TABLE test_timestamp
(ts TIMESTAMP
);
INSERT INTO test_timestamp VALUES('1999-01-01 01:02:03'),('1999-01-01 01:02:03'),('99-01-01 01:02:03'),('990101010203');
INSERT INTO test_timestamp VALUES('2022@01@02@11@22@33');
INSERT INTO test_timestamp VALUES(CURRENT_TIMESTAMP()),(NOW());

TIMESTAMP 和 DATETIME區(qū)別

CREATE TABLE temp_time
(d1 DATETIME,d2 TIMESTAMP
);
INSERT INTO temp_time VALUES('2022-04-16 19:09:55','2022-04-16 19:09:55');
INSERT INTO temp_time VALUES(NOW(),NOW());SELECT * FROM temp_time;SET time_zone = '+9:00'SELECT * FROM temp_time;	#設置之后再查詢一次

• 設置時區(qū)

SET time_zone = '+9:00'

TIMESTAMP妙用

TIMESTAMP時間戳在創(chuàng)建的時候可以有多重不同的特性，如：

1.在創(chuàng)建新記錄和修改現有記錄的時候都對這個數據列刷新：

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2.在創(chuàng)建新記錄的時候把這個字段設置為當前時間，但以后修改時，不再刷新它：

TIMESTAMP DEFAULT CURRENT_TIMESTAMP

3.在創(chuàng)建新記錄的時候把這個字段設置為0，以后修改時刷新它：

TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

4.在創(chuàng)建新記錄的時候把這個字段設置為給定值，以后修改時刷新它：

TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP

模擬用戶登錄

CREATE TABLE t_user
(id INT PRIMARY KEY AUTO_INCREMENT,createtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,lastlogintime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,is_online TINYINT DEFAULT 0
);
INSERT INTO t_user(is_online) VALUES(1);SELECT * FROM t_user;# 數據更新
UPDATE t_user SET is_online=0 WHERE id=1;

本來兩個時間是一模一樣的，但是當用戶下線，lastlohintime發(fā)生自動更新

總結

總的來說，選擇合適的數據類型可以提高數據庫的性能、節(jié)省存儲空間、確保數據的一致性和有效性，并方便進行數據處理和計算。因此，在設計數據庫時，選擇合適的數據類型非常重要。~下節(jié)帶來字符串 | 二進制類型的分享