在上一節(jié)中我們了解了 VARCHAR 這個(gè)使用頻率很高的數(shù)據(jù)類型的用法，并介紹了 VARCHAR 如何優(yōu)化的一些知識(shí)點(diǎn)，比如要避免行溢出，避免設(shè)計(jì)了太多的 VARCHAR 字段導(dǎo)致無(wú)法擴(kuò)展的尷尬等等，希望你能從中得到一些收獲。
這一節(jié)我們來(lái)學(xué)習(xí)整形數(shù)據(jù)類型，那么整形這個(gè)數(shù)據(jù)類型又該如何使用呢？
1. INT 1、INT 20 與顯示寬度
在我的日常工作當(dāng)中，經(jīng)常遇到一些程序員朋友發(fā)來(lái)一些建庫(kù)建表語(yǔ)句，其中的部分字段是這樣子的：
status int(1) not null default '1'
type int(1) not null default '1'
那么 INT (1) 就真的是 1 個(gè)字節(jié)了嗎，這個(gè)括號(hào)中間的數(shù)字究竟有什么意義？
我們還是做一個(gè)實(shí)驗(yàn)來(lái)看一下。
下面我們創(chuàng)建一個(gè)數(shù)據(jù)類型都是 INT 的新表，這個(gè)表里面有 3 個(gè)字段，分別定義為 INT、INT (1)、和 INT (20)，并且插入一些數(shù)據(jù)：
mysql> create table int_test(id int auto_increment, col_1 int(1), col_2 int(20), primary key (id));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into int_test values(1,1,1),(10,10,10);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
查詢一下結(jié)果，看下輸出的值：
mysql> select * from int_test;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
| 1 | 1 | 1 |
| 10 | 10 | 10 |
+----+-------+-------+
2 rows in set (0.01 sec)
在這個(gè)實(shí)驗(yàn)中你可以看到，不管你定義 INT (1) 還是 INT (20)，MySQL 在存儲(chǔ)的時(shí)候都不會(huì)做出超出 INT 的限制范圍。
那這個(gè)括號(hào)里面的值是做什么用的呢？
其實(shí)它定義的是顯示寬度。
當(dāng)你在程序中定義顯示寬度之后，如果你插入的值不夠這個(gè)寬度，在查詢時(shí)會(huì)在左邊用相應(yīng)寬度的空格填充。
這個(gè)空格在通常情況下，我們是看不到的，因?yàn)樵诳蛻舳溯敵鰰r(shí)（MySQL Workbench、mysql-connector-java 等客戶端）默認(rèn)會(huì)去掉左側(cè)的空格。
所以為什么說(shuō)它是顯示寬度，是因?yàn)樗⒉幌拗拼鎯?chǔ)的值的范圍，不管是 INT (1) 還是 INT (20)，存儲(chǔ)范圍都是 INT：
- 有符號(hào)：-2147483648 ~ 2147483647
- 無(wú)符號(hào)：0 ~ 4294967295
這里再提及一點(diǎn)，整形數(shù)據(jù)類型中還有一個(gè) ZEROFILL 的屬性，如果建表時(shí)指定了這個(gè)屬性，剛剛的空格就會(huì)變成 0，并且自動(dòng)給這個(gè)列加上 UNSIGNED 屬性。
我們?cè)賮?lái)做實(shí)驗(yàn)看下：
mysql> show create table int_test \G
*************************** 1. row ***************************
Table: int_test
Create Table: CREATE TABLE `int_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col_1` int(1) DEFAULT NULL,
`col_2` int(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.01 sec)
mysql> alter table int_test modify col_2 int(20) zerofill;
Query OK, 2 rows affected (0.07 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> show create table int_test \G
*************************** 1. row ***************************
Table: int_test
Create Table: CREATE TABLE `int_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`col_1` int(1) DEFAULT NULL,
`col_2` int(20) unsigned zerofill DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select * from int_test;
+----+-------+----------------------+
| id | col_1 | col_2 |
+----+-------+----------------------+
| 1 | 1 | 00000000000000000001 |
| 10 | 10 | 00000000000000000010 |
+----+-------+----------------------+
2 rows in set (0.01 sec)
在上面的實(shí)驗(yàn)中我們可以看到，col_2 這一列已經(jīng)在左側(cè)填充了一串無(wú)意義的 0。但是在一些客戶端仍然會(huì)自動(dòng)去掉左側(cè)這個(gè)無(wú)意義的 0。
由于這個(gè)顯示寬度實(shí)在雞肋，MySQL 官方也表示會(huì)在未來(lái)的版本中去掉 ZEROFILL 和顯示寬度這兩個(gè)屬性。
在現(xiàn)在的版本中，我們只需要了解它，并且不要用錯(cuò)數(shù)據(jù)類型即可。存儲(chǔ)的值的范圍不超過(guò) 128/256 的話就用 TINYINT，存儲(chǔ)值超出 INT 的范圍的話就用 BIGINT。
下圖是各種整形數(shù)據(jù)類型的數(shù)值范圍表：
2. 兩個(gè) UNSIGNED 的值無(wú)法相減？
UNSIGNED 屬性就是無(wú)符號(hào)的數(shù)字類型，如果用做自增主鍵的話，相比有符號(hào)的整形數(shù)據(jù)類型能擴(kuò)展 1 倍的空間。
有的大型互聯(lián)網(wǎng)公司會(huì)在內(nèi)部的開(kāi)發(fā)規(guī)范中要求使用 UNSIGNED 的自增值。
看起來(lái)還蠻不錯(cuò)，但是在使用時(shí)有一個(gè)小問(wèn)題你需要注意，那就是兩個(gè)數(shù)值相減得到負(fù)數(shù)的情形。
我們?cè)賮?lái)做一個(gè)實(shí)驗(yàn)，建一個(gè)兩列都帶有 UNSIGNED 屬性的表，然后再插入一行數(shù)據(jù)：
mysql> create table int_test_2 (col_1 int unsigned, col_2 int unsigned);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into int_test_2 values(1,2);
Query OK, 1 row affected (0.01 sec)
mysql> select col_1 - col_2 from int_test_2;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`imooc_mysql_interview`.`int_test_2`.`col_1` - `imooc_mysql_interview`.`int_test_2`.`col_2`)'
在這里你可以看到，本來(lái)該輸出 - 1 的結(jié)果卻報(bào)錯(cuò)了。并且錯(cuò)誤看起來(lái)還有點(diǎn)奇怪，提示 BIGINT UNSIGNED 超出了范圍。
那為什么會(huì)發(fā)生這樣的問(wèn)題呢？
其實(shí)和編程語(yǔ)言中的問(wèn)題類似，對(duì)于有符號(hào)的整形數(shù)來(lái)說(shuō)，-1 的十六進(jìn)制值是 0xFFFFFFFF；而對(duì)于無(wú)符號(hào)的整形數(shù)來(lái)說(shuō)，4294967295 的十六進(jìn)制值也是 0xFFFFFFFF。
在 MySQL 數(shù)據(jù)庫(kù)中，對(duì)于 UNSIGNED 數(shù)的操作，它的返回值都是 UNSIGNED 的，不能是負(fù)值，所以就導(dǎo)致了上面的錯(cuò)誤產(chǎn)生。
那么如果非要獲得負(fù)值呢？只需要改一下 SQL_MODE 即可：
mysql> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 3090 | Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. |
+---------+------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
但是不太建議這么做，如果需要避免這個(gè)情況還是老老實(shí)實(shí)的使用有符號(hào)的整形數(shù)據(jù)類型，如果擔(dān)心 INT 的數(shù)值范圍不夠用的話換成 BIGINT 基本也夠用了。
3. 選 TINYINT 還是 ENUM？
你在設(shè)計(jì)數(shù)據(jù)庫(kù)表結(jié)構(gòu)的時(shí)候，可能會(huì)設(shè)計(jì)一些枚舉的列來(lái)存儲(chǔ)一些屬性值，比如用 (1,2,3) 代表蘋(píng)果、安卓、其他操作系統(tǒng)。
在數(shù)據(jù)類型的選擇上，可以存儲(chǔ)枚舉值的數(shù)據(jù)類型除了 TINYINT 和 ENUM，其實(shí) VARCHAR (N) 也有很多人在用。那么這 3 種數(shù)據(jù)類型哪個(gè)最合適呢？
先放下 TINYINT 不說(shuō)，我們來(lái)看 ENUM 這個(gè)數(shù)據(jù)類型，和前面一樣，我們先做個(gè)實(shí)驗(yàn)，下面是一個(gè)建表語(yǔ)句：
mysql> CREATE TABLE smartphone (
-> vendor VARCHAR(40),
-> os_name ENUM('ios', 'android', 'other')
-> );
Query OK, 0 rows affected (0.03 sec)
MySQL 官方在實(shí)現(xiàn) ENUM 這個(gè)數(shù)據(jù)類型時(shí)，實(shí)際上是用 TINYINT 來(lái)存儲(chǔ)的。
可讀性也比較好，不需要映射成類似 1,2,3 這樣的數(shù)值。
但是 ENUM 這個(gè)數(shù)據(jù)類型在 MySQL 數(shù)據(jù)庫(kù)中有一點(diǎn)不好的地方是，字符串列表是預(yù)定義的，以后如果要添加或刪除屬性必須使用 ALTER TABLE。
即便現(xiàn)在 MySQL 數(shù)據(jù)庫(kù)有了 online DDL，ALTER TABLE 時(shí)不會(huì)鎖表，在大表上這也是一個(gè)很重的操作。
另外 ENUM 的數(shù)據(jù)類型還有兩個(gè)問(wèn)題需要注意：
- 如果給 ENUM 列插入了一個(gè)無(wú)效值，則會(huì)轉(zhuǎn)換為空字符串（ ’ ’ ）存儲(chǔ)（SQL_MODE 設(shè)置在嚴(yán)格模式下會(huì)直接拋出錯(cuò)誤）。
示例如下：
mysql> insert into smartphone values('MicroSoft','WindowsPhone');
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 'os_name' at row 1 |
+---------+------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from smartphone;
+-----------+---------+
| vendor | os_name |
+-----------+---------+
| MicroSoft | |
+-----------+---------+
1 row in set (0.00 sec)
- 給 ENUM 的列進(jìn)行排序操作時(shí)，是按照表定義中指定的順序，而不是根據(jù)字母表的順序。
示例如下：
mysql> insert into smartphone values('HUAWEI','android'),('Apple','ios'),('NOKIA','other'),('XIAOMI','android');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from smartphone order by os_name asc;
+-----------+---------+
| vendor | os_name |
+-----------+---------+
| MicroSoft | |
| Apple | ios |
| HUAWEI | android |
| XIAOMI | android |
| NOKIA | other |
+-----------+---------+
5 rows in set (0.00 sec)
由于 ENUM 實(shí)際存儲(chǔ)是用的 TINYINT，因此在存儲(chǔ)較小的數(shù)字時(shí)，建議直接使用 TINYINT 即可。
只有一些比較特殊的場(chǎng)景，例如星期、季節(jié)、顏色、性別等固定不變的枚舉值，才建議你選用 ENUM 這個(gè)數(shù)據(jù)類型。
那 VARCHAR (N) 能用嗎？
選擇 VARCHAR (N) 這個(gè)數(shù)據(jù)類型的好處是比較直觀。比如 “canceled”，“finished”，“delivering” 的可讀性顯然比 1，2，3 要好，但是占用的存儲(chǔ)空間也相應(yīng)的變大。
但是話說(shuō)回來(lái)，從性能角度考慮 VARCHAR (N) 會(huì)比 TINYINT 略差，主要表現(xiàn)在 JOIN 和 ORDER BY 的操作上。
你可以根據(jù)具體情況選用。
最后再來(lái)說(shuō)說(shuō) TINYINT 這個(gè)數(shù)據(jù)類型。
首先 TINYINT 的字節(jié)數(shù)很小，只占 1 個(gè)字節(jié)；其次它的查詢和 DML 的性能表現(xiàn)都不錯(cuò)。
因此除了一些比較特殊的應(yīng)用場(chǎng)景，再綜合上面的一些問(wèn)題來(lái)看，在大多數(shù)的場(chǎng)景下，還是建議優(yōu)先選用 TINYINT。
4. 小結(jié)
今天，我給你介紹了 MySQL 里面最頻繁使用的整形數(shù)據(jù)類型，包括 INT 和 TINYINT。
INT 定義中的數(shù)字只是表示顯示寬度，它并不限制 MySQL 的實(shí)際存儲(chǔ)空間。因此在開(kāi)發(fā)工作中不要把 INT (1) 當(dāng)初 TINYINT 來(lái)用，也千萬(wàn)不要把 INT (20) 當(dāng)成 BIGINT 用。
對(duì)于 UNSIGNED 這個(gè)屬性，最適合的場(chǎng)景就是主鍵自增值，或者一些不允許出現(xiàn)負(fù)數(shù)的應(yīng)用中。在使用時(shí)要注意帶有 UNSIGNED 的字段，在做計(jì)算時(shí)如果出現(xiàn)負(fù)數(shù)可能會(huì)報(bào)錯(cuò)。
最后我還給你介紹了 TINYINT、ENUM、VARCHAR (N) 在存儲(chǔ)枚舉值的情況。鑒于前面描述的 ENUM 和 VARCHAR (N) 的一些情況，因此我的建議是首選 TINYINT，特殊場(chǎng)景適當(dāng)選用其他類型。
參考文獻(xiàn)：
- MySQL 官方文檔：整形數(shù)據(jù)類型，https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
- 《MySQL 技術(shù)內(nèi)幕：SQL 編程》