知道了表是由不同數據類型的列組成的，然后填充了一行一行的數據。

當我們要創建表的時候，就要根據業務需求，選擇合適的數據類型。比如在實戰項目中，文章表就是由下面這些不同數據類型的字段定義的。

目前用到了 bigint、tinyint、varchar、int、timestamp 等數據類型，這些數據類型到底該如何選擇呢？就需要我們提前先了解清楚，MySQL 到底支持哪些數據類型，以及每種數據類型的特點是什么。

整數類型

上面提到的 bigint、tinyint、int 都是整數類型，MySQL 支持的整數類型如下：

smallint 和 mediuint 這兩種類型很少用到，一般我們用的是 tinyint、int、bigint 這三種類型。

比如說技術派中 article 表的文章類型字段 article_type，就是用 tinyint 類型定義的，因為文章類型只有 1（博文）、2（問答）種，所以用 tinyint 就足夠了。

再比如說狀態 status 字段，也是用 tinyint 類型定義的，因為狀態我們只有 0（未發布）、1（發布）兩種。

以及 deleted 字段，也是用 tinyint 類型定義的，因為刪除狀態一般只有 0（未刪除）、1（已刪除）兩種。

那像 int 一般用于用戶的年齡啊、庫存數量啊、評論數量啊、點贊數量啊等等。

技術派中 article 表的 offical_stat（官方推薦狀態）、topping_stat（置頂狀態）、cream_stat（加精狀態）用了 int 類型，其實不太合理，應該用 tinyint 類型就足夠了。暫時也就懶得改了。

bigint 我們用到了表的主鍵上，這也是一種比較常見的做法，尤其是當預計數量超過 int 的最大值（21 億）時，但是就技術派目前的數量來看，用 int 就足夠了。

在做大宗期貨交易的訂單時，一開始用的是 int 類型，后來還真的出現了超出 int 范圍的情況，所以后來改成了 bigint 類型。

bigint 的最大值是 9223372036854775807，也就是 922 億億，這個數字非常非常大，往往到這個數量級的都要做分庫分表了。

另外，對于主鍵的數據類型選擇，不同的業務場景有不同的需求，如果需要確保跨多個數據庫或者系統唯一性，那么 UUID 或者雪花算法生成的 ID 會更合適。

UUID 不依賴于數據庫的自增特性，非常適合分布式系統，但是 UUID 會占用更多的存儲空間（CHAR(36) 或 VARCHAR(36)），而且不是遞增的，會導致索引的性能下降。

有符號和無符號

整型數據類型還可以選擇有符號和無符號，有符號就是可以存儲正數和負數，無符號就是只能存儲正數。默認為有符號，也就是不用指定。

比如說 int 類型，如果是有符號的，那么范圍是 -2147483648 到 2147483647，如果是無符號的，那么范圍是 0 到 4294967295。

無符號的情況下，要特別注意和 Java 數據類型的對應關系。

我們都知道，Java 中的int范圍是?-2147483648 到 2147483647。那如果 MySQL 選擇的 int 類型是無符號的，范圍就超出了 Java 的 int 類型范圍了。

這時候，為了避免出現不兼容的情況，Java 的數據類型要選擇 long 類型。當然了，在數據庫實體（POJO）中，要用包裝類型Long類型。

像自增 ID，肯定是無符號的，所以我們會在定義的時候將其設置為 unsigned，比如說技術派項目中的 article 表。

int(10) 和 int

注意，上圖中我們在定義 id 的時候，設置的數據類型是 int(10)，和 int 有什么區別呢？

這其實是一道不錯的面試題，比如說面試官可能會問你，int(10) 和 int(11) 有什么區別？

如果之前沒有了解過的話，可能一下子就懵了。其實這個和存儲空間沒有關系，只是用來規定顯示寬度的。

我們來創建這樣一張測試表，包含四個字段，一個是主鍵 ID，一個是 int(10)，一個是 int(11)，另外一個是 int。

CREATE TABLE `test` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`int10` int(10) NOT NULL,`int11` int(11) NOT NULL,`int` int NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

這里的反引號 \` 是為了避免關鍵字沖突。

然后我們插入一條數據，看看結果。

INSERT INTO `test` (`int10`, `int11`, `int`) VALUES (1234567890, 1234567890, 1234567890);

似乎沒有什么區別

我們來看一下 MySQL 官方對 int(M) 的解釋。

M indicates the maximum display width for integer types.

也就是說，int(M) 只是規定了顯示寬度，對于存儲空間和范圍沒有影響。通常與 ZEROFILL 一起使用，這樣會在數字前面補 0，直到達到 M 位數。

If ZEROFILL is specified, the column will be zero-filled to the specified width for numeric types.

我們來修改一下之前的表結構，將 int(10) 和 int(11) 改成 int(10) ZEROFILL 和 int(11) ZEROFILL。

CREATE TABLE `test` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`int10` int(10) ZEROFILL NOT NULL,

`int11` int(11) ZEROFILL NOT NULL,

`int` int NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

再插入一條同樣的數據（10 位），看看結果。

INSERT INTO `test` (`int10`, `int11`, `int`) VALUES (1234567890, 1234567890, 1234567890);

結果如下所示：

可以看到，int(10) ZEROFILL 和 int 都是正常顯示，而 int(11) ZEROFILL 在數字前面補了 0，這是因為 1234567890 不足我們規定的 11 位，所以前面補了 0。

也就是說，沒有指定 ZEROFILL 的話，int(M) 和 int 是一樣的；指定了 ZEROFILL 的話，就會在數字前面補 0，直到達到 M 位數。

浮點數類型

浮點數類型包括 float 和 double，它們的取值范圍我從來沒有記住過（😂），太難記了。

它們之間的區別是存儲空間不同，float 是 4 字節，double 是 8 字節。既然存儲空間不同，肯定表示的范圍也就不同，double 占用的空間大，所以精度上也更加準確。

定點數類型

實際工作當中，浮點數其實并不常用，因為很容易出現精度丟失的問題，尤其是一些涉及到貨幣值時，所以我們一般會選擇定點數類型。

記得之前在對接微信支付的時候，微信支付的金額是用 int 類型表示的，單位是分，也就是說 1 元是 100 分。這樣做的好處是，避免了浮點數精度丟失的問題。

定點數類型包括 decimal 和 numeric，網上有說 decimal 的存儲空間是定長的，而 numeric 的存儲空間是變長的，但是我在 MySQL 官方文檔上并沒有找到相關的信息。

換句話說，在 MySQL 中，decimal 和 numeric 是等價的，沒有區別。

例如，我們可以這樣定義一個定點數類型的字段。

CREATE TABLE `test` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`price` decimal(10, 2) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

聲明 decimal(10, 2) 意味著 price 最多有 10 位數，包括小數點后的 2 位。

也就是說，decimal(M, D) 中的 M 表示總的位數，D 表示小數點后的位數。

定點數之所以比浮點數精確，是因為定點數會按照小數點把數字分成兩部分，整數部分和小數部分，而浮點數是按照科學計數法來存儲的。

比方說對于十進制小數 123.456，定點數會把它存儲為 123 和 456 兩部分，而浮點數會把它存儲為 。

大家都知道，計算機存儲的是二進制，遇到小數的時候就容易表示不精確，比如說 0.1 在二進制中是無限循環的。

使用 binaryconvert 可以查看 0.1 在二進制中的表示。

但存儲整數就完全沒問題，0 的二進制表示就是 00，1 的二進制表示就是 01，2 的二進制表示就是 10，3 的二進制表示就是 11，4 的二進制表示就是 0100，以此類推（逢二進一）。

回到定點數上，對于定點數 decimal(M, D)，M 的取值范圍是 1 到 255，D 的取值范圍是 0 到 30；且 M 必須大于等于 D。

那 MySQL 是如何存儲 decimal(16, 4) 這個定點數的呢？

可以選擇字符串的存儲方式，每個數字占用一個字符的位置，比如說數值 123.4567，直接存儲為字符串 "123.4567"。

但這種方式對于計算機來說，并不高效，畢竟計算機的底層仍然是通過二進制來實現存儲的。那怎么辦呢？

對于 decimal(16, 4)，MySQL 會將其拆解為兩部分，整數部分和小數部分，然后采用二進制壓縮存儲的方式來存儲

①、整數部分：有 12 位數字（16-4=12），每組 9 位十進制數字可以被壓縮存儲在 4 個字節的二進制格式中（因為 ）。如果整數部分少于 9 位數字，它將占用足夠存儲該數值的最小字節數。

②、小數部分：有 4 位數字，同樣可以通過壓縮的二進制格式存儲。4 位十進制數字可以壓縮到 2 字節內（因為 ）。

每組中包含的十進制數字位數不同，所需的存儲空間也不同，具體見下表：

所以 decimal(16, 4) 共需要占用 8 個字節的存儲空間：

1. 第 1 組包含 3 個十進制，需要 2 個字節；
2. 第 2 組包含 9 個十進制，需要 4 個字節；
3. 第 3 組包含 4 個十進制，需要 2 個字節。

我們拿 1234567890.1234 舉例：

①、整數部分 1234567890，可以分組為 1 和 234567890，分別占用 1 個字節和 4 個字節。

②、小數部分 1234，可以分組為 1234，占用 2 個字節。

Java 與 decimal 對應的數據類型是 BigDecimal，常用在金融領域。

日期和時間類型

日期和時間類型包括 year、date、time、datetime、timestamp。

1. year 類型用于存儲年份，范圍是 1901 到 2155，占用 1 個字節。
2. date 類型用于存儲日期，范圍是 1000-01-01 到 9999-12-31，占用 3 個字節。
3. time 類型用于存儲時間，范圍是 -838:59:59[.000000] 到 838:59:59[.000000]，占用 3 字節 + 小數秒的存儲空間
4. datetime 類型用于存儲日期和時間，范圍是 1000-01-01 00:00:00[.000000] 到 9999-12-31 23:59:59[.999999]，占用 5字節+小數秒的存儲空間。
5. timestamp 類型用于存儲時間戳，范圍是 1970-01-01 00:00:01[.000000] 到 2038-01-19 03:14:07[.999999]，占用 4 字節 + 小數秒的存儲空間。

datetime 和 timestamp 是最常用的兩個類型，新手經常會搞混，不知道到底該使用哪一個。

1. datetime 是存儲的是實際的時間，不會受到時區的影響。適用于需要存儲較寬時間范圍的日期和時間數據，或者數據不需要考慮時區變化的場景，如出生日期。
2. timestamp 是存儲的是 UTC（Coordinated Universal Time，一個時間標準）時間，可以根據時區進行轉換。特別適合記錄數據的創建時間和修改時間等需要考慮時區的場景。

舉例來說，我們把 2024-02-02 11:12:13 存儲到 datetime 類型的字段中，那么無論在哪個時區，都是 2024-02-02 11:12:13。

而 timestamp 要求存儲的是時間戳，存儲之前，要先計算從 1970-01-01 00:00:00 起到某個時間節點的秒數，比如說 2024-02-02 11:12:13 對應的時間戳是 1706843533000。

那這個時間戳在 UTC+8（北京時間）時區下，就是 2024-02-02 11:12:13，在 UTC-5（紐約）時區下，就是 2024-02-01 20:12:13，在 UTC+1（倫敦）時區下，就是 2024-02-02 03:12:13。

在技術派項目中，article 表的 create_time 和 update_time 字段就是 timestamp 類型的。

注意到 timestamp 類型的字段，還有一個屬性 DEFAULT CURRENT_TIMESTAMP，這是設置默認值的，也就是說，如果插入數據的時候沒有指定 create_time 和 update_time 的值，那么就會自動填充當前時間。這是 MySQL 5.6 之后的新特性。

ON UPDATE CURRENT_TIMESTAMP 是設置更新時間的，也就是說，如果更新數據的時候沒有指定 update_time 的值，那么就會自動填充當前時間。

通過 select CURRENT_TIMESTAMP 可以查看當前時間。

也就是說，我們在插入數據和更新數據的時候，不需要手動填充 create_time 和 update_time 的值，MySQL 會自動幫我們填充。

對應的 Java 數據類型是 java.util.Date。

技術派實戰項目中用 MyBatis-Plus 作為持久層框架，它擴展了 MyBatis，而 MyBatis 會自動將 timestamp 類型的字段映射為 java.util.Date 類型，由 DateTypeHandler 實現。

字符串類型

字符串類型包括 char、varchar、tinytext、text、mediumtext、longtext。

①、char(M)，固定 M 個字符長度，最多 255 個字符，如果省略掉 M，默認為 1。

②、varchar(M)，可變 M 個字符長度，最多 65535 個字符，但實際上存不了這么多，因為需要額外兩個字節來存儲長度（字符數小于 255 時使用一個字節），除此之外，字符集、存儲引擎有關。

下表展示了 char(4) 和 varchar(4) 在單字節字符集（latin1）下的不同。

latin1 是單字節字符集，一個字符占用 1 個字節。

由此可以看出，char 類型是固定長度的，不足的地方會用空格填充，而 varchar 類型是可變長度的；當超過指定長度時，都會截斷。

重點：

也就是說，當我們不確定字段的長度時，應該使用 varchar 類型。這樣可以節省一定的存儲空間。

實際工作中，char 確定也非常少用，項目中用的都是 varchar 類型。

③、文本類型，最常用的就是 longtext類型，比如說技術派項目中 article 表的 content 字段就是 longtext 類型的。

內容是 markdown 格式的字符，所以 longtext 足夠用了，來看一下它們的存儲空間：

1. tinytext，最多 255個字節。
2. text，最多 65,535?個字節，相當于 64KB。
3. mediumtext，最多16,777,215? 個字節，相當于 16MB。
4. longtext，最多 4,294,967,295?個字節，相當于 4GB。

那其實除了上面提到的這幾種字符類型，還有 enum 和 set 類型。

1. enum 類型，用于存儲枚舉類型，比如說性別字段，只有男和女兩種，就可以用 enum 類型。
2. set 類型，用于存儲集合類型，比如說文章標簽字段，可以有多個標簽，就可以用 set 類型。

通過下面這個例子，我們可以看到 enum 和 set 類型的定義方式。

CREATE TABLE `test` (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`gender` enum('男', '女') NOT NULL,

`tags` set('Java', 'Python', 'Go', 'C++') NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

插入兩條數據，看看結果。

INSERT INTO `test` (`gender`, `tags`) VALUES ('男', 'Java,Python');

INSERT INTO `test` (`gender`, `tags`) VALUES ('女', 'Go,C++');

當然了，這兩個類型在實際工作中并不常用，比如說 enum 類型，可以通過 tinyint 配合Java中的枚舉來實現。

這樣會更加靈活，枚舉中的 code 和表中的字段值對應，枚舉中的 desc 就可以定義為枚舉的描述。

至于 set，同樣可以通過一對多的關系來實現，比如說文章和標簽的關系，可以通過一張文章表和一張標簽表來實現。

這樣修改起來會更加容易，比如說哪天 Go 標簽想更改為 Golang 標簽，我只需要改一下 tag 表就可以了，文章表不需要做任何修改。

二進制類型

二進制類型通常用來存儲圖片、音頻、視頻等二進制文件，MySQL 提供了多種二進制類型來滿足不同的存儲要求，包括 binary、varbinary、tinyblob、blob、mediumblob、longblob。

binary 和 varbinary 類型適合存儲需要精確字節長度的二進制數據，比如說 MD5 值等。

1. binary：固定長度的二進制，最多 255 個字節。
2. varbinary：可變長度的二進制，最多 65535 個字節。

blob（Binary Large OBject）類型適合存儲大型二進制數據，比如說圖片、音頻、視頻等。

1. tinyblob：最多 255個字節。
2. blob：最多65535 個字節，相當于 64KB。
3. mediumblob：最多16777215 個字節，相當于 16MB。
4. longblob：最多4294967295 個字節，相當于 4GB。

不過在實際工作中，我們很少直接存儲二進制文件，而是存儲文件的路徑，然后通過路徑來訪問文件。

文件本身通過 OSS（Object Storage Service）等對象存儲服務來存儲，數據庫只存儲文件的元數據，比如說文件名、文件大小、文件類型等。

這樣做的好處是，可以減少數據庫的存儲壓力，提高數據庫的性能，而且還可以實現文件的分布式存儲。

像技術派中的 article 表，就有一個 picture 字段，用來存儲文章的封面圖片，這個字段是 varchar 類型的，存儲的就圖片的路徑。

小結

關于 MySQL 的數據類型，這一節我們就先講到這里，總結一下：

1. 整數類型包括 tinyint、smallint、mediuint、int、bigint，可以選擇有符號和無符號。
2. 浮點數類型包括 float 和 double，double 的精度更高。
3. 定點數類型包括 decimal 和 numeric，用于存儲貨幣值等精度要求高的數據。
4. 日期和時間類型包括 year、date、time、datetime、timestamp，datetime 適用于不需要考慮時區變化的場景，timestamp 適用于需要考慮時區變化的場景。
5. 字符串類型包括 char、varchar、tinytext、text、mediumtext、longtext，char 是固定長度的，varchar 是可變長度的，文本類型適合存儲大型文本數據。
6. 二進制類型包括 binary、varbinary、tinyblob、blob、mediumblob、longblob，適合存儲圖片、音頻、視頻等二進制文件。

在實際工作中，我們要根據業務需求，選擇合適的數據類型，避免浪費存儲空間，提高數據庫性能。

另外，還要注意數據庫和 Java 數據類型的對應關系，避免出現不兼容的情況。