我們在設計資料庫時需要為表中的每個欄位指定一個數據類型。數據類型決定了欄位中允許存儲的數據以及支持的操作，例如字符串允許的最大長度、數字類型可以進行算術運算等；另一方面，不同的類型佔用的存儲空間和處理性能也不同。因此，本文給大家介紹一下 MySQL 支持的各種數據類型，以及設計表時如何選擇合適的欄位類型。
常見數據類型MySQL 實現了 SQL 標準中定義的大部分數據類型，主要可以分為以下幾類：數字類型、字符串類型、日期和時間類型、JSON 數據類型以及空間類型。
數字類型MySQL 實現了 SQL 標準中的精確數字類型和近似數字類型，包括整數（INTEGER、SMALLINT）、定點數（DECIMAL、NUMERIC）和浮點數（FLOAT、REAL、DOUBLE PRECISION）。這些數字類型又可以分為有符號類型和無符號類型。
整數數字下表列出了 MySQL 中的所有整數類型：
整數類型的定義如下：
TINYINT[(M)] [UNSIGNED] [ZEROFILL]
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]
INT[(M)] [UNSIGNED] [ZEROFILL]
INTEGER[(M)] [UNSIGNED] [ZEROFILL]
BIGINT[(M)] [UNSIGNED] [ZEROFILL]
其中，INT 是 INTEGER 的同義詞。M 表示的是顯示寬度，不會影響數據的存儲；如果實際數據小於指定寬度，可以指定 ZEROFILL 在左側使用 0 填充顯示。UNSIGNED 表示無符號整數；如果指定了 ZEROFILL，MySQL 會自動加上 UNSIGNED。例如：
DROP TABLE IF EXISTS t;
CREATE TABLE t(id int(4) ZEROFILL);
INSERT INTO t VALUES (1), (99999);
mysql> SELECT * FROM t;
+--+
| id |
+--+
| 0001 |
| 99999 |
+--+
2 rows in set (0.00 sec)
mysql> desc t;
+--+-+-++----+--+
| Field | Type | Null | Key | Default | Extra |
+--+-+-++----+--+
| id | int(4) unsigned zerofill | YES | | NULL | |
+--+-+-++----+--+
1 row in set (0.00 sec)
⚠️從 MySQL 8.0.17 開始，整數類型的顯示寬度 M 和 ZEROFILL 選項已經被棄用，將來的版本中會刪除。可以考慮使用其他方法實現相同的效果，例如使用 LPAD() 函數將數字填充至指定寬度，或者使用 CHAR 類型存儲格式化的數字。
對於 BIGINT 類型，需要注意以下內容：
SERIAL 是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE 的同義詞；
所有的算術運算都使用有符號的 BIGINT 或者 DOUBLE 數據進行運算，所以不要使用大於 9223372036854775807 的無符號大整數參與運算，除非使用位函數和運算符。如果參與了運算，由於將 BIGINT 轉換為 DOUBLE 時存在捨入錯誤，可能會導致結果中的最後幾位數字出錯。MySQL 會在以下情況中使用 BIGINT：使用整數類型存儲 BIGINT 欄位中的無符號大整數；針對 BIGINT 欄位的 MIN() 或者 MAX() 函數；兩個整數的算術運算（+、-、* 等）；
可以將 BIGINT 數據存儲為字符串， MySQL 在運算時會執行字符串到數字的類型轉換，而不會涉及到雙精度格式的中間結果。
當兩個操作數都是整數類型時，-、+ 和 * 運算符使用 BIGINT 算術。這意味著如果將兩個大整數（或者返回整數值的函數）相乘，如果結果大於 9223372036854775807，可能會得到一個異常的結果。
定點數字MySQL 使用 DECIMAL 和 NUMERIC 類型存儲精確的數字值，通常用於需要保留完整精確的欄位，例如財務系統中的貨幣餘額。MySQL 中的 NUMERIC 和 DECIMAL 是同義詞。
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
其中，DEC 和 FIXED 是 DECIMAL 的同義詞。M 表示可以存儲的總位數（精度），最大取值為 65，默認值為 10，小數點和負號（-）不算位數；D 是小數點後的位數（刻度），最大取值為 30 並且小於等於 M，默認值為 0（表示整數）。ZEROFILL 表示如果實際數據小於指定寬度，顯示時在左側使用 0 填充。UNSIGNED 表示無符號數字；如果指定了 ZEROFILL，MySQL 會自動加上 UNSIGNED。例如：
DROP TABLE IF EXISTS t;
CREATE TABLE t(salary DECIMAL(5,2));
INSERT INTO t VALUES (-999.99), (0), (999.99);
mysql> INSERT INTO t VALUES (1000);
ERROR 1264 (22003): Out of range value for column 'salary' at row 1
mysql> INSERT INTO t VALUES (0.001);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+--+-++
| Level | Code | Message |
+--+-++
| Note | 1265 | Data truncated for column 'salary' at row 1 |
+--+-++
1 row in set (0.00 sec)
mysql> SELECT * FROM t;
+----+
| salary |
+----+
| -999.99 |
| 0.00 |
| 999.99 |
| 0.00 |
+----+
4 rows in set (0.00 sec)
其中，salary 欄位的精度為 5，刻度為 2。因此，該欄位可以存儲 -999.99 到 -999.99 之間的數值。此時，插入超過精度的數據返回錯誤，插入超過刻度的數據會進行四捨五入。
MySQL 中的 DECIMAL 和 DECIMAL(M) 等價於 DECIMAL(M,0)，M 的默認值為 10。
⚠️從 MySQL 8.0.17 開始，DECIMAL 類型的 UNSIGNED 選項已經被棄用，將來的版本中會刪除。可以考慮使用 CHECK 約束實現相同的功能。
DECIMAL 數據使用二進位格式進行存儲，9 位十進位數字存儲為 4 個字節，整數部分和小數部分的數值分別存儲。每 9 位數字需要 4 字節存儲，剩餘的位數需要的存儲空間如下表所示：
剩餘位數字節數001–213–425–637–94例如，DECIMAL(18,9) 在小數點兩邊各有 9 位，所以整數部分和小數部分各需要 4 字節存儲；DECIMAL(20,6) 擁有 14 位整數和 6 位小數，整數部分需要 4 字節（9 位數字）加上 3 字節（7 位數字），6 位小數部分需要 3 字節存儲。
DECIMAL 欄位不會存儲前置的 +、- 字符或者 0。如果為 DECIMAL(5,1) 欄位插入 +0003.1，最終會存儲 3.1。對於負數，不會存儲負號。
浮點數字MySQL 使用 FLOAT 和 DOUBLE 類型表示近似數字，對於單精度使用 4 字節存儲，雙精度使用 8 字節存儲。
FLOAT(p) [UNSIGNED] [ZEROFILL]
其中，p 是以比特為單位的精度。MySQL 中的 p 只用於決定最終類型為 FLOAT 還是 DOUBLE，如果 p 的值為 0 到 24 則為 FLOAT，如果 p 的值為 25 到 53 則為 DOUBLE；ZEROFILL 表示如果實際數據小於指定寬度，顯示時在左側使用 0 填充。UNSIGNED 表示無符號數字；如果指定了 ZEROFILL，MySQL 會自動加上 UNSIGNED。
對於單精度 FLOAT 類型，理論上支持 -3.402823466E+38 到 -1.175494351E-38、0、以及 1.175494351E-38 到 3.402823466E+38 之間的數值，實際上取決於硬體和作業系統。
對於雙精度 DOUBLE 類型，理論上支持 -1.7976931348623157E+308 到 -2.2250738585072014E-308、0 以及 2.2250738585072014E-308 到 1.7976931348623157E+308 之間的數值，實際上取決於硬體和作業系統。
📝DOUBLE 是 DOUBLE PRECISION 的同義詞。如果指定了 SQL 模式 REAL_AS_FLOAT，REAL 是 FLOAT 的同義詞；否則它是 DOUBLE PRECISION 的同義詞。
由於浮點類型存儲的是近似數值而不是精確數值，如果嘗試將它們進行比較可能會返回錯誤的結果，而且結果和平臺或者具體實現相關。例如：
mysql> SELECT (1.0/3)*3, (1.0/3)*3=1.0;
+-++
| (1.0/3)*3 | (1.0/3)*3=1.0 |
+-++
| 1.00000 | 0 |
+-++
1 row in set (0.00 sec)
雖然 (1.0/3)*3 的結果顯示為 1.00000，但是在系統內部它並不等於 1.0。
📝為了方便移植，存儲近似數值的數據類型應該使用 FLOAT 或者 DOUBLE PRECISION 定義，而不需要指定精度或者位數。
除了以上定義之外，MySQL 還提供了一些非標準的語法：
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
REAL[(M,D)] [UNSIGNED] [ZEROFILL]
其中，M 表示可以存儲的總位數（精度），D 是小數點後的位數（刻度）；如果省略 M 和 D，具體的範圍取決於硬體實現，單精度浮點數大概支持 7 位小數，雙精度浮點數大概支持 15 位小數。例如，FLOAT(7,4) 類型顯示時的內容可能為 -999.9999。MySQL 存儲數據值會進行捨入，如果插入 999.00009，近似的結果為 999.0001。
⚠️從 MySQL 8.0.17 開始，FLOAT、DOUBLE 類型的 UNSIGNED 屬性已經被棄用，將來的版本中會刪除。可以考慮使用 CHECK 約束實現相同的功能。從 MySQL 8.0.17 開始，非標準的 FLOAT(M,D) 和 DOUBLE(M,D) s語法已經被棄用，將來的版本中會刪除。
BIT 類型MySQL 中的 InnoDB、MyISAM、MEMORY 以及 NDB 存儲引擎支持特殊的位類型（BIT），用於存儲比特數據值。
BIT(M)
其中，M 表示比特個數，範圍從 1 到 64，默認為 1；BIT(M) 大概需要 (M+7)/8 字節的存儲。例如：
CREATE TABLE work_days(
year INT,
week INT,
days BIT(7),
PRIMARY KEY(year, week)
);
INSERT INTO work_days
VALUES(2020, 1, b'0110110');
其中，days 表示這一周中的每一天是否是工作日，1 表示工作日，0 表示周末或者假期。INSERT 語句表示 2020 年第一周的周日、周三和周六是休息日。
位類型的常量可以使用以下格式指定：
b'111'
B'111'
0b111
以上數據表示十進位中的 3。查詢時可以使用 BIN 函數進行轉換顯示：
mysql> select year, week, bin(days) from work_days;
+-+-+-+
| year | week | bin(days) |
+-+-+-+
| 2020 | 1 | 110110 |
+-+-+-+
1 row in set (0.00 sec)
MySQL 沒有提供內置的 BOOLEAN 或者 BOOL 數據類型，而是使用 TINYINT(1) 進行表示。以下三種語法等價：
BOOL
BOOLEAN
TINYINT(1)
在 MySQL 中，0 被看作 false，非 0 的數值被看作 true。布爾類型的常量可以使用 TRUE 和 FALSE 表示，結果分別為 1 和 0。
mysql> SELECT IF(0, 'true', 'false');
+----+
| IF(0, 'true', 'false') |
+----+
| false |
+----+
mysql> SELECT IF(1, 'true', 'false');
+----+
| IF(1, 'true', 'false') |
+----+
| true |
+----+
mysql> SELECT IF(2, 'true', 'false');
+----+
| IF(2, 'true', 'false') |
+----+
| true |
+----+
不過， TRUE 和 FALSE 僅僅是 1 和 0 的別名。例如：
mysql> SELECT IF(0 = FALSE, 'true', 'false');
+--+
| IF(0 = FALSE, 'true', 'false') |
+--+
| true |
+--+
mysql> SELECT IF(1 = TRUE, 'true', 'false');
+-+
| IF(1 = TRUE, 'true', 'false') |
+-+
| true |
+-+
mysql> SELECT IF(2 = TRUE, 'true', 'false');
+-+
| IF(2 = TRUE, 'true', 'false') |
+-+
| false |
+-+
mysql> SELECT IF(2 = FALSE, 'true', 'false');
+--+
| IF(2 = FALSE, 'true', 'false') |
+--+
| false |
+--+
最後兩個語句都返回了 false，因為 2 即不等於 1，也不等於 0。
字符串類型MySQL 字符串類型用於存儲字符和字符串數據，包括二進位數據，例如圖片或者文件。字符串數據可以支持比較運算符和模式匹配運算符，例如 LIKE、正則表達式匹配以及全文檢索。
MySQL 支持的字符串類型包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 以及 SET，其中 CHAR、VARCHAR、TEXT、ENUM 以及 SET 包含字符集（Charset）和排序規則（Collation）屬性，默認繼承表的字符集和排序規則。MySQL 8.0 默認使用 utf8mb4 字符集。
CHARCHAR(n) 和 CHARACTER(n) 類型表示長度固定的字符串，其中 n 表示字符串中字符的最大數量，取值範圍從 0 到 255。例如：
CREATE TABLE t (c1 CHAR, c2 CHAR(5));
INSERT INTO t VALUES ('a','a');
其中，c1 只能存儲 1 個字符；c2 最多能夠存儲 5 個字符。對於定長字符串，如果輸入的字符串長度不夠，將會使用空格進行填充。因此，欄位 c2 中實際存儲的內容為「a」加上 4 個空格。
mysql> select concat(c1, '!'), concat(c2, '!') from t;
+--+--+
| concat(c1, '!') | concat(c2, '!') |
+--+--+
| a! | a! |
+--+--+
1 row in set (0.00 sec)
mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select concat(c1, '!'), concat(c2, '!') from t;
+--+--+
| concat(c1, '!') | concat(c2, '!') |
+--+--+
| a! | a ! |
+--+--+
1 row in set (0.00 sec)
mysql> SET sql_mode = default;
Query OK, 0 rows affected (0.00 sec)
默認情況下沒有設置 SQL 模式 PAD_CHAR_TO_FULL_LENGTH，MySQL 讀取 CHAR 欄位時自動截斷了尾部的空格。
MySQL 使用比較運算符（=、<>、>、< 等）和 LIKE 操作符比較和匹配 CHAR 欄位數據時不考慮尾部的空格。例如：
mysql> select c2 from t where c2='a';
+-+
| c2 |
+-+
| a |
+-+
1 row in set (0.00 sec)
mysql> select c2 from t where c2 like 'a ';
Empty set (0.00 sec)
📝通常來說，只有存儲固定長度的數據時，才會考慮使用定長字符串類型。例如 18 位身份證，6 位郵政編碼等。
VARCHARVARCHAR(n) 和 CHARACTER VARYING(n) 類型表示長度不固定的字符串，其中 n 表示字符串中字符的最大數量，取值範圍從 0 到 65535。例如：
DROP TABLE IF EXISTS t;
CREATE TABLE t (c VARCHAR(5));
INSERT INTO t VALUES ('a '), ('abcde');
欄位 c 最多存儲 5 個字符。
📝VARCHAR 欄位的實際最大長度受限於最大的行大小（65536 字節，所有欄位長度之和）以及字符集。例如，utf8mb4 字符集中的一個字符最多佔用 4 個字節，因此這種字符集的 VARCHAR 欄位可以聲明的最大長度為 16383。
對於變長字符串，如果輸入的字符串長度不夠，存儲實際的內容。例如類型為 VARCHAR(5) 的欄位，如果輸入值為「a」，實際存儲的內容為「a」。SQL 模式 PAD_CHAR_TO_FULL_LENGTH 對 VARCHAR 欄位沒有影響，MySQL 讀取 VARCHAR 欄位時不會截斷尾部的空格。
mysql> select c,length(c) from t;
+--+-+
| c | length(c) |
+--+-+
| a | 5 |
| abcde | 5 |
+--+-+
2 rows in set (0.00 sec)
MySQL 存儲的 VARCHAR 數據包括 1 字節或 2 字節的長度信息前綴加上具體數據，長度前綴標識了數據的字節數。如果 VARCHAR 欄位的數據小於等於 255 字節，使用 1 個字節存儲長度；如果數據可能大於等於 256 字節，使用 2 個字節存儲長度。
📝通常來說，變長字符串類型一般用於存儲長度不固定的內容，例如名字、電子郵箱、產品描述等。
TEXTMySQL 提供了 4 種形式的 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 以及 LONGTEXT。
TINYTEXT，最大長度為 255 個字節，類似於 VARCHAR(255)。每個 TINYTEXT 值需要 1 字節額外的存儲表示長度；
TEXT，最大長度為 65535 個字節，類似於 VARCHAR(65535)。每個 TEXT 值需要 2 字節額外的存儲表示長度；
MEDIUMTEXT，最大長度為 16777215 個字節。每個 MEDIUMTEXT 值需要 3 字節額外的存儲表示長度；
LONGTEXT，最大長度為 4294967295 個字節。每個 LONGTEXT 值需要 4 字節額外的存儲表示長度。
TEXT 類型可以用於存儲長文本字符串，長度支持 1 字節到 4 GB；但是 MySQL 不會在伺服器內存中緩存 TEXT 數據，而是從磁碟中讀取，所有訪問時比 CHAR 和 VARCHAR 類型更慢一些。MySQL 插入或者查詢時不會對 TEXT 數據尾部空格進行任何處理。📝TEXT 數據類型通常用於存儲文章內容、產品描述等信息。
二進位字符串BINARY(M) 和 VARBINARY(M) 類型與 CHAR 和 VARCHAR 類型類似，但是存儲的內容為二進位字節串，而不是普通字符串。其中 M 表示最大的字節長度，分別為 255 和 65535。這兩種類型使用 binary 字符集和排序規則，基於字節數值進行比較和排序。
存儲 BINARY 數據時，在尾部使用 0x00（字節 0）填充到指定長度，查詢時不會刪除尾部的 0 字節。所有的字節對應比較操作都有意義，包括 ORDER BY 和 DISTINCT 操作，0x00 和空格比較的結果不相等，0x00 的排序在空格前面。
DROP TABLE IF EXISTS t;
CREATE TABLE t (c BINARY(3));
INSERT INTO t VALUES ('a');
mysql> SELECT * FROM t where c='a';
Empty set (0.00 sec)
mysql> SELECT * FROM t where c='a\0\0';
+--+
| c |
+--+
| 0x610000 |
+--+
1 row in set (0.00 sec)
存儲 VARBINARY 數據時，不會使用 0x00（字節 0）填充，查詢時不會刪除尾部的 0 字節。所有的字節對應比較操作都有意義，包括 ORDER BY 和 DISTINCT 操作，0x00 和空格比較的結果不相等，0x00 的排序在空格前面。
BINARY(M) 和 VARBINARY(M) 類型與 CHAR 和 VARCHAR 類型的存儲需求也類似，但是以字節為單位。
BLOBMySQL 提供了 4 種形式的 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 以及 LONGBLOB。
TINYBLOB，最大長度為 255 個字節，類似於 VARBINARY(255)。每個 TINYBLOB 值需要 1 字節額外的存儲表示長度；
BLOB，最大長度為 65535 個字節，類似於 VARBINARY(65535)。每個 BLOB 值需要 2 字節額外的存儲表示長度；
MEDIUMBLOB，最大長度為 16777215 個字節。每個 MEDIUMBLOB 值需要 3 字節額外的存儲表示長度；
LONGBLOB，最大長度為 4294967295 個字節。每個 LONGBLOB 值需要 4 字節額外的存儲表示長度。
BLOB 類型可以用於存儲二進位大對象，長度支持 1 字節到 4 GB；BLOB 類型使用 binary 字符集和排序規則，基於字節數值進行比較和排序。MySQL 不會在伺服器內存中緩存 BLOB 數據，而是從磁碟中讀取，所有訪問時比 BINARY 和 VARBINARY 類型更慢一些。MySQL 插入或者查詢時不會對 BLOB 數據尾部空格進行任何處理。📝BLOB 數據類型通常用於存儲圖片、文檔、視頻等信息。
ENUMENUM(『value1』,『value2』,…) 類型定義了一個枚舉，即取值限定為 『value1』、『value2』、…、NULL 或者 『』 之一的字符串對象。ENUM 數據在內部使用整數表示，最多包含 65535 個不同的值。
每個枚舉元素最大的長度為 M <= 255 並且 (M x w) <= 1020，其中 M 是元素的字面長度，w 是字符集中字符可能佔用的最大字節數。
使用枚舉類型的優勢在於：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'), ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+----+---+
| name | size |
+----+---+
| t-shirt | medium |
+----+---+
如果插入 100 萬條 『medium』 數據，需要 100 萬字節存儲；如果直接使用 VARCHAR 類型，需要 6 倍存儲。
另一方面，使用枚舉類型時需要注意枚舉值的排序使用內部的索引數字，而不是字符串。例如，對於 ENUM(『b』, 『a』) 字符 b 排在 a 之前。
SETSET(『value1』,『value2』,…) 類型定義了一個集合，即取值限定為 『value1』、『value2』、…中零個或多個的字符串對象。SET 數據在內部使用整數表示，最多包含 64 個不同的成員。
每個集合元素最大的長度為 M <= 255 並且 (M x w) <= 1020，其中 M 是元素的字面長度，w 是字符集中字符可能佔用的最大字節數。
例如：
DROP TABLE IF EXISTS t;
CREATE TABLE t (c SET('a','b'));
INSERT INTO t VALUES (''),('a'),('b'),('a,b');
mysql> SELECT c+0 FROM t;
+-+
| c+0 |
+-+
| 0 |
| 1 |
| 2 |
| 3 |
+-+
4 rows in set (0.00 sec)
SET 對象的存儲空間由集合成員的個數決定；如果個數為 N，對象佔用 (N+7)/8 字節，向上取整為 1、2、3、4 或者 8 字節。
日期時間類型MySQL 提供了以下存儲時間值的數據類型：DATE、TIME、DATETIME、TIMESTAMP 以及 YEAR。其中，TIME、DATETIME、TIMESTAMP 支持小數秒，最多 6 位小數（微秒）。
日期類型DATE 表示日期類型，支持的範圍從 『1000-01-01』 到 『9999-12-31』，佔用 3 個字節。DATE 數據的顯示格式為 『YYYY-MM-DD』。例如：
DROP TABLE IF EXISTS t;
CREATE TABLE t (birth_date date);
INSERT INTO t VALUES ('2020-10-01');
mysql> SELECT * FROM t;
+--+
| birth_date |
+--+
| 2020-10-01 |
+--+
1 row in set (0.00 sec)
MySQL 使用 4 位數字存儲日期數據中的年份，如果輸入 2 位年份，將會使用以下規則：
INSERT INTO t VALUES ('01-10-31'), ('81-10-31');
mysql> SELECT * FROM t;
+--+
| birth_date |
+--+
| 2020-10-01 |
| 2001-10-31 |
| 1981-10-31 |
+--+
3 rows in set (0.00 sec)
以上規則同樣適用於其他數據類型中的年份信息，包括 DATETIME、TIMESTAMP 以及 YEAR。
時間類型MySQL 使用 TIME 類型表示一天中的時間，格式為 『HH:MM:SS』，範圍小於 24 小時。另外，也可以使用 TIME 表示兩個事件之間的時間間隔，格式為 『hhh:mm:ss』，範圍從 『-838:59:59』 到 『838:59:59』。TIME 類型需要 3 字節的存儲。例如：
DROP TABLE IF EXISTS t;
CREATE TABLE t (start_time time, end_time time);
INSERT INTO t VALUES ('09:00:00', '10:00:00');
mysql> SELECT * FROM t;
+--++
| start_time | end_time |
+--++
| 09:00:00 | 10:00:00 |
+--++
1 row in set (0.00 sec)
MySQL 使用 TIME(N) 表示包含小數部分的時間，最多包含 6 位小數（微秒），默認為 0 位。如果包含了小數秒，TIME 需要額外的存儲，TIME(1) 和 TIME(2) 需要 4 字節，TIME(3) 和 TIME(3) 需要 5 字節，TIME(5) 和 TIME(6) 需要 6 字節存儲。
時間戳類型DATETIME(N) 和 TIMESTAMP(N) 類型可以同時存儲日期（DATE）和時間（TIME）信息，也就是時間戳。
DATETIME 類型使用 『YYYY-MM-DD hh:mm:ss[.fraction]』 格式顯示，支持範圍 『1000-01-01 00:00:00.000000』 到 『9999-12-31 23:59:59.999999』，默認 0 位小數秒，需要 5 字節存儲。如果支持小數秒，額外的存儲和 TIME(N) 類似。
TIMESTAMP 類型使用 UTC 時區進行存儲，支持範圍 『1970-01-01 00:00:01.000000』 UTC 到 『2038-01-19 03:14:07.999999』 UTC，默認 0 位小數秒，需要 4 字節存儲。如果支持小數秒，額外的存儲和 TIME(N) 類似。
例如：
DROP TABLE IF EXISTS t;
CREATE TABLE t (dt DATETIME, ts TIMESTAMP);
SET time_zone = '+00:00';
INSERT INTO t VALUES (now(), now());
mysql> SELECT * FROM t;
+-+-+
| dt | ts |
+-+-+
| 2020-09-10 14:09:57 | 2020-09-10 14:09:57 |
+-+-+
1 row in set (0.00 sec)
兩者在 UTC 時區相同，然後修改會話的時區：
mysql> SET time_zone = '+08:00';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t;
+-+-+
| dt | ts |
+-+-+
| 2020-09-10 14:09:57 | 2020-09-10 22:09:57 |
+-+-+
1 row in set (0.00 sec)
結果顯示，TIMESTAMP 類型會隨著當前時區進行調整。
DATETIME 和 TIMESTAMP 類型支持自動初始化或者更新為當前日期時間，在欄位定義時分別使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 屬性進行設置。例如：
DROP TABLE IF EXISTS t;
CREATE TABLE t (
id int,
dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
INSERT INTO t(id) VALUES (1);
mysql> SELECT * FROM t;
+-+-+-+
| id | dt | ts |
+-+-+-+
| 1 | 2020-09-10 14:33:25 | 2020-09-10 22:33:25 |
+-+-+-+
1 row in set (0.00 sec)
如果只需要存儲年份信息，可以使用 YEAR 類型。YEAR 類型佔用 1 個字節，顯示格式為 『YYYY』，範圍從 1901 到 2155，以及 0000。
JSON 數據類型MySQL 5.7.8 開始支持原生 JSON 數據類型，可以支持更加高效的 JSON 文檔存儲和管理。原生 JSON 數據類型提供了自動的格式驗證以及優化的存儲格式，可以快速訪問文檔中的元素節點。例如：
CREATE TABLE employee_json(
emp_id INTEGER NOT NULL PRIMARY KEY,
emp_info JSON NOT NULL
);
INSERT INTO employee_json
VALUES (1, '{"emp_name": "劉備", "sex": "男", "dept_id": 1, "manager": null, "hire_date": "2000-01-01", "job_id": 1, "income": [{"salary":30000}, {"bonus": 10000}], "email": "liubei@shuguo.com"}');
mysql> INSERT INTO employee_json VALUES (2,'{"emp_name": "劉備" ');
ERROR 3140 (22032): Invalid JSON text: "Missing a comma or '}' after an object member." at position 23 in value for column 'employee_json.emp_info'.
除了 JSON 數據類型之外，MySQL 還提供了許多 JSON 處理函數和操作符，例如構造 JSON 對象的 JSON_OBJECT、JSON_ARRAY，查詢指定元素的 ->（JSON_EXTRACT）、->> （JSON_UNQUOTE + JSON_EXTRACT），將 JSON 數據轉換為 SQL 數據的 JSON_TABLE、更新 JSON 數據的 JSON_SET、JSON_INSERT、JSON_REPLACE、JSON_REMOVE 以及格式驗證的 JSON_VALID 函數。例如：
SELECT emp_id, jt.*
FROM employee_json,
JSON_TABLE(emp_info, '$'
COLUMNS (emp_name VARCHAR(50) PATH '$.emp_name',
sex VARCHAR(10) PATH '$.sex',
dept_id INTEGER PATH '$.dept_id',
manager INTEGER PATH '$.manager',
hire_date DATE PATH '$.hire_date',
job_id INTEGER PATH '$.job_id',
salary INTEGER PATH '$.income[0].salary',
bonus INTEGER PATH '$.income[1].bonus',
email VARCHAR(100) PATH '$.email')
) jt;
emp_id|emp_name|sex|dept_id|manager|hire_date |job_id|salary|bonus|email |
-|---|---|--|--||-|-||----|
1|劉備 |男 | 1| |2000-01-01| 1| 30000|10000|liubei@shuguo.com |
一般來說，JSON 欄位所需的存儲和 LONGBLOB 或者 LONGTEXT 差不多。不過，JSON 文檔的二進位編碼需要額外的存儲，包括元數據和字典信息。舉例來說，JSON 文檔中的字符串需要額外的 4 到 10 個字節存儲。除此之外，JSON 文檔的最大長度不能超過系統變量 max_allowed_packet 的限制。
空間數據類型MySQL 支持許多包含各種幾何和地理數據的空間數據類型，包括：
GEOMETRY，任何類型的空間數據值；
POINT，X-Y 坐標系中的點；
LINESTRING，曲線，一個或多個 POINT 數據值；
POLYGON，多邊形；
GEOMETRYCOLLECTION，GEOMETRY 數據集合；
MULTILINESTRING，LINESTRING 數據集合；
MULTIPOINT，POINT 數據集合；
MULTIPOLYGON，POLYGON 數據集合。
MySQL 存儲空間數據時使用 4 字節標識 SRID（空間參照標識符），然後存儲 Well-Known Binary 格式的數據。LENGTH() 函數可以返回數據佔用的字節數。MySQL 中的 MyISAM、InnoDB、NDB 以及 ARCHIVE 存儲引擎支持空間數據類型的存儲和處理函數，MyISAM 和 InnoDB 存儲引擎支持空間欄位的 SPATIAL 索引。
關於 MySQL 空間數據擴展，具體參考官方文檔。
選擇合適的數據類型最後我們來看看如何選擇合適的數據類型。首先，應該滿足存儲業務數據的需求；其次，還需要考慮性能和使用方便。一般來說，先確定基本的類型：
文本數據，使用字符串類型；
數值數據，尤其是需要進行數學運算的數據，選擇數字類型；
日期和時間信息，最好使用原生的日期時間類型，也可以考慮數字類型；
文檔、圖片、音頻和視頻等，使用二進位類型；或者可以考慮存儲在文件伺服器上，然後在資料庫中存儲文件的路徑。
接下來需要進一步確定具體的數據類型。在滿足數據存儲和擴展的前提下，儘量使用更小的數據類型，可以節省一些存儲，通常性能也會更好。例如，對於一個小型公司而言，員工編號通常不會超過幾百，使用 SMALLINT 已經足夠。對於 MySQL 而言，不需要支持負數的話可以考慮 UNSIGNED 類型。對於字符數據，一般使用 VARCHAR 類型；如果數據長度能夠確保一致，可以使用 CHAR；指定最大長度時，滿足存儲需求的前提下儘量使用更小的值。只有在普通字符串類型長度無法滿足時才考慮 TEXT 欄位類型。
如果需要存儲精確的數字，不要使用浮點數類型。對於金額，可以使用 DECIMAL(p, s)；或者將數據乘以 10 的 N 次方，例如將 10.35 元存儲為整數 103500，然後在應用程式中進行處理和前端顯示轉換。
只需要年月日信息時使用 DATE 類型，例如出生日期；只需要時間信息時使用 TIME 類型，例如發車時間；選擇時間戳類型時需要注意 DATETIME 和 TIMESTAMP 的區別。
可以考慮將日期時間拆成多個數字類型存儲，或者使用 UNIX 時間戳表示；但是不要使用字符串存儲日期時間數據，它們無法支持數據的運算。例如獲得兩個日期之間的間隔，需要依賴應用程式進行轉換和處理。最好也不要使用整數類型存儲當前時間距離 1970 年 1 月 1 日的毫秒數來表示時間，這種方式在顯示時需要進行轉換，不是很方便。
對於特殊應用場景，考慮使用其他類型。例如電商的產品信息可以考慮使用 JSON 數據類型，處理地理位置信息使用空間數據類型等。
另外，如果一個欄位同時出現在多個表中，使用相同的數據類型。例如，員工表中的部門編號（dept_id）欄位與部門表的編號（dept_id）欄位保持類型一致。
總結本文詳細分析了 MySQL 中的各種數據類型以及選擇數據類型時的一些通用的原則，使用任何數據類型之前都應該查看相關的資料庫文檔。
如果覺得文章對你有用，歡迎關注❤️、點讚👍、推薦🎁