要了解 MySQL 資料庫,首先必須要了解 MySQL 支援的資料型別。實際上,MySQL 支援很多資料型別,包括整數型別、實數型別、字串型別、列舉型別、時間型別等。
原文地址:服務端指南 資料儲存篇 | MySQL(01) 資料型別的使用與選擇
部落格地址:blog.720ui.com/
整數型別
對於整數型別,可以使用 TINYINT、 SMALLINT、 MEDIUMINT、 INT、 BIGINT 等。每個整數型別都對應著不同的儲存空間。
資料型別 | 儲存(Bytes) |
---|---|
TINYINT | 1 |
SMALLINT | 2 |
MEDIUMINT | 3 |
INT | 4 |
BIGINT | 8 |
當使用整數型別時,存在一些使用細節,這裡,給讀者進行說明。
整數型別可以選擇 UNSIGNED 屬性,表示不允許負數,這樣可以使得正數的上限提高一倍。舉個例子,INT 的儲存範圍是 -2-31 ~ 231 - 1,也就是 -2147483648 ~ 2147483647,那麼 UNSIGNED INT 可以儲存的範圍就是 0 ~ 263 - 1,即 0 ~ 9223372036854775807。
整數型別可以指定寬度,然而對大多數場景是沒有意義的,它並不會限制整數型別的合法範圍,它只是規定某些互動工具顯示出來的字元個數。如果不顯示地指定寬度,則預設為 INT(11)。有讀者會誤認為 INT(11) 指定整數型別的長度是 11 位,這個想法是錯誤的。實際上,在 Zerofill 屬性中,表示當陣列寬度小於 11 位時,在數字前面加 0 填滿寬度。
更小的資料型別通常更快,因為佔用更少的磁碟空間。舉個例子,如果需要儲存性別狀態(1-男;2-女;99-未知),這個時候應該優先考慮 TINYINT,而不是 INT,因為 TINYINT 佔用更少的磁碟空間。因此,需要選擇合適的整數型別來減少佔用的磁碟空間,而不是選擇全部使用 INT 或 BIGINT。
實數型別
對於實數型別,可以使用 FLOAT、 DOUBLE、 DECIMAL 等。每個實數型別都對應著不同的儲存空間。
資料型別 | 儲存(Bytes) |
---|---|
FLOAT | 4 |
DOUBLE | 8 |
FLOAT(M,D) 和 DOUBLE(M,D) 表示一共顯示 M 位整數,D 位小數。舉個例子,FLOAT(5,2) 可以顯示為 100.99。此外,讀者還要注意的是,MySQL 儲存時會進行四捨五入,因此,如果值為 100.0099, 會儲存近似結果 100.01。
FLOAT 只保證 6 位有效數字的準確性,所以 FLOAT(M,D) 中,M<=6 時,數字通常是準確的。
DOUBLE 只保證 16 位有效數字的準確性,所以 DOUBLE(M,D) 中,M<=16 時,數字通常是準確的。
在使用實數型別,要重點考慮精度問題。DOUBLE 是 MySQL 內部浮點計算的型別,它比 FLOAT 有更高的精度和更大的範圍,但是 FLOAT 和 DOUBLE 都是不精確的,如果要實現精確浮點運算,就需要使用 DECIMAL 型別。
因此,如果需要考慮精度問題,優先考慮 DECIMAL 型別。如果對精度問題不是特別敏感,可以考慮使用 FLOAT 或者 DOUBLE 型別。
字串型別
對於實數型別,可以使用 CHAR、 VARCHAR、 BLOB、 TEXT 等。
CHAR 型別是定長的。MySQL 會根據定義的長度分配空間。CHAR 長度可以是 0 到 255之間的值。
VARCHAR 型別用於儲存可變長字串,它更加節省空間。值得注意的是, VARCHAR 實際上會使用 1 或 2 個額外位元組記錄字串的長度。VARCHAR 長度可以指定 0 到 65535 之間的值。
BLOB 和 TEXT 主要用來儲存大文字,分別採用二進位制和字串方式儲存。如果 BLOB 和 TEXT 還無法滿足需求,還可以使用 TINYBLOB、 MEDIUMBLOB、 LONGBLOB、 TINYTEXT、 MEDIUMTEXT、 LONGTEXT。值得注意的是,它們不能夠有預設值。
對於字串型別的使用場景,可以大概總結下。
定長的字串,可以選擇 CHAR。舉個例子,身份證是定長型別,那麼選擇 CHAR 是非常合適的。
變長的字串,可以選擇 VARCHAR, 因為 VARCHAR 更加節省空間。
儲存大文字的場景,可以考慮使用 BLOB 和 TEXT。如果 BLOB 和 TEXT 還無法滿足需求,還可以使用 TINYBLOB、 MEDIUMBLOB、 LONGBLOB、 TINYTEXT、 MEDIUMTEXT、 LONGTEXT。
列舉型別
實際上, 列舉型別儲存的是整數型別,但其顯示為字串。如果將一個非法值插入,即允許的值之外的字串,列舉型別將不允許操作,但是列舉型別允許空字串和 NULL 型別。
儘量不要使用列舉型別,因為列舉型別增加了維護成本。試想,如果需要增加新的列舉型別,需要全表更新,如果對於大資料量的更新場景,會造成鎖表,這是多麼恐怖的事情。
時間型別
對於時間型別,MySQL 提供了豐富的資料型別: YEAR、 DATE、 TIME、 DATETIME、 TIMESTAMP。每個時間型別都對應著不同的時間格式。
資料型別 | 時間格式 | 案例 |
---|---|---|
YEAR | 0000 | 2017 |
DATE | 0000-00-00 | 2017-01-01 |
TIME | 00:00:00 | 01:01:01 |
DATETIME | 0000-00-00 00:00:00 | 2017-01-01 01:01:01 |
TIMESTAMP | 0000-00-00 00:00:00 | 2017-01-01 01:01:01 |
MySQL 能夠儲存的最小單位是秒,如果需要更精確的儲存,就必須自己定義儲存格式。
MySQL 時間型別是存在範圍的。
資料型別 | 範圍 |
---|---|
DATE | '1000-01-01' ~ '9999-12-31' |
DATETIME | '000-01-01 00:00:00' ~ '9999-12-31 23:59:59' |
TIMESTAMP | '1970-01-01 00:00:01'UTC ~ '2038-01-19 03:14:07' UTC |
DATETIME 和 TIMESTAMP 都可以儲存相同型別的資料,而 TIMESTAMP 只使用 DATETIME 一半的儲存空間。通常情況下,建議優先考慮 TIMESTAMP,因為它的空間利用率更高。
(完)
更多精彩文章,盡在「服務端思維」微信公眾號!