在上一節(jié)中我們了解了 VARCHAR 這個使用頻率很高的數據類型的用法，并介紹了 VARCHAR 如何優(yōu)化的一些知識點，比如要避免行溢出，避免設計了太多的 VARCHAR 字段導致無法擴展的尷尬等等，希望你能從中得到一些收獲。
這一節(jié)我們來學習整形數據類型，那么整形這個數據類型又該如何使用呢？
1. INT 1、INT 20 與顯示寬度
在我的日常工作當中，經常遇到一些程序員朋友發(fā)來一些建庫建表語句，其中的部分字段是這樣子的：
status int(1) not null default '1'
type int(1) not null default '1'
那么 INT (1) 就真的是 1 個字節(jié)了嗎，這個括號中間的數字究竟有什么意義？
我們還是做一個實驗來看一下。
下面我們創(chuàng)建一個數據類型都是 INT 的新表，這個表里面有 3 個字段，分別定義為 INT、INT (1)、和 INT (20)，并且插入一些數據：
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
查詢一下結果，看下輸出的值：
mysql> select * from int_test;
+----+-------+-------+
| id | col_1 | col_2 |
+----+-------+-------+
| 1 | 1 | 1 |
| 10 | 10 | 10 |
+----+-------+-------+
2 rows in set (0.01 sec)
在這個實驗中你可以看到，不管你定義 INT (1) 還是 INT (20)，MySQL 在存儲的時候都不會做出超出 INT 的限制范圍。
那這個括號里面的值是做什么用的呢？
其實它定義的是顯示寬度。
當你在程序中定義顯示寬度之后，如果你插入的值不夠這個寬度，在查詢時會在左邊用相應寬度的空格填充。
這個空格在通常情況下，我們是看不到的，因為在客戶端輸出時（MySQL Workbench、mysql-connector-java 等客戶端）默認會去掉左側的空格。
所以為什么說它是顯示寬度，是因為它并不限制存儲的值的范圍，不管是 INT (1) 還是 INT (20)，存儲范圍都是 INT：
- 有符號：-2147483648 ~ 2147483647
- 無符號：0 ~ 4294967295
這里再提及一點，整形數據類型中還有一個 ZEROFILL 的屬性，如果建表時指定了這個屬性，剛剛的空格就會變成 0，并且自動給這個列加上 UNSIGNED 屬性。
我們再來做實驗看下：
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)
在上面的實驗中我們可以看到，col_2 這一列已經在左側填充了一串無意義的 0。但是在一些客戶端仍然會自動去掉左側這個無意義的 0。
由于這個顯示寬度實在雞肋，MySQL 官方也表示會在未來的版本中去掉 ZEROFILL 和顯示寬度這兩個屬性。
在現在的版本中，我們只需要了解它，并且不要用錯數據類型即可。存儲的值的范圍不超過 128/256 的話就用 TINYINT，存儲值超出 INT 的范圍的話就用 BIGINT。
下圖是各種整形數據類型的數值范圍表：
2. 兩個 UNSIGNED 的值無法相減？
UNSIGNED 屬性就是無符號的數字類型，如果用做自增主鍵的話，相比有符號的整形數據類型能擴展 1 倍的空間。
有的大型互聯(lián)網公司會在內部的開發(fā)規(guī)范中要求使用 UNSIGNED 的自增值。
看起來還蠻不錯，但是在使用時有一個小問題你需要注意，那就是兩個數值相減得到負數的情形。
我們再來做一個實驗，建一個兩列都帶有 UNSIGNED 屬性的表，然后再插入一行數據：
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`)'
在這里你可以看到，本來該輸出 - 1 的結果卻報錯了。并且錯誤看起來還有點奇怪，提示 BIGINT UNSIGNED 超出了范圍。
那為什么會發(fā)生這樣的問題呢？
其實和編程語言中的問題類似，對于有符號的整形數來說，-1 的十六進制值是 0xFFFFFFFF；而對于無符號的整形數來說，4294967295 的十六進制值也是 0xFFFFFFFF。
在 MySQL 數據庫中，對于 UNSIGNED 數的操作，它的返回值都是 UNSIGNED 的，不能是負值，所以就導致了上面的錯誤產生。
那么如果非要獲得負值呢？只需要改一下 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)
但是不太建議這么做，如果需要避免這個情況還是老老實實的使用有符號的整形數據類型，如果擔心 INT 的數值范圍不夠用的話換成 BIGINT 基本也夠用了。
3. 選 TINYINT 還是 ENUM？
你在設計數據庫表結構的時候，可能會設計一些枚舉的列來存儲一些屬性值，比如用 (1,2,3) 代表蘋果、安卓、其他操作系統(tǒng)。
在數據類型的選擇上，可以存儲枚舉值的數據類型除了 TINYINT 和 ENUM，其實 VARCHAR (N) 也有很多人在用。那么這 3 種數據類型哪個最合適呢？
先放下 TINYINT 不說，我們來看 ENUM 這個數據類型，和前面一樣，我們先做個實驗，下面是一個建表語句：
mysql> CREATE TABLE smartphone (
-> vendor VARCHAR(40),
-> os_name ENUM('ios', 'android', 'other')
-> );
Query OK, 0 rows affected (0.03 sec)
MySQL 官方在實現 ENUM 這個數據類型時，實際上是用 TINYINT 來存儲的。
可讀性也比較好，不需要映射成類似 1,2,3 這樣的數值。
但是 ENUM 這個數據類型在 MySQL 數據庫中有一點不好的地方是，字符串列表是預定義的，以后如果要添加或刪除屬性必須使用 ALTER TABLE。
即便現在 MySQL 數據庫有了 online DDL，ALTER TABLE 時不會鎖表，在大表上這也是一個很重的操作。
另外 ENUM 的數據類型還有兩個問題需要注意：
- 如果給 ENUM 列插入了一個無效值，則會轉換為空字符串（ ’ ’ ）存儲（SQL_MODE 設置在嚴格模式下會直接拋出錯誤）。
示例如下：
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 的列進行排序操作時，是按照表定義中指定的順序，而不是根據字母表的順序。
示例如下：
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 實際存儲是用的 TINYINT，因此在存儲較小的數字時，建議直接使用 TINYINT 即可。
只有一些比較特殊的場景，例如星期、季節(jié)、顏色、性別等固定不變的枚舉值，才建議你選用 ENUM 這個數據類型。
那 VARCHAR (N) 能用嗎？
選擇 VARCHAR (N) 這個數據類型的好處是比較直觀。比如 “canceled”，“finished”，“delivering” 的可讀性顯然比 1，2，3 要好，但是占用的存儲空間也相應的變大。
但是話說回來，從性能角度考慮 VARCHAR (N) 會比 TINYINT 略差，主要表現在 JOIN 和 ORDER BY 的操作上。
你可以根據具體情況選用。
最后再來說說 TINYINT 這個數據類型。
首先 TINYINT 的字節(jié)數很小，只占 1 個字節(jié)；其次它的查詢和 DML 的性能表現都不錯。
因此除了一些比較特殊的應用場景，再綜合上面的一些問題來看，在大多數的場景下，還是建議優(yōu)先選用 TINYINT。
4. 小結
今天，我給你介紹了 MySQL 里面最頻繁使用的整形數據類型，包括 INT 和 TINYINT。
INT 定義中的數字只是表示顯示寬度，它并不限制 MySQL 的實際存儲空間。因此在開發(fā)工作中不要把 INT (1) 當初 TINYINT 來用，也千萬不要把 INT (20) 當成 BIGINT 用。
對于 UNSIGNED 這個屬性，最適合的場景就是主鍵自增值，或者一些不允許出現負數的應用中。在使用時要注意帶有 UNSIGNED 的字段，在做計算時如果出現負數可能會報錯。
最后我還給你介紹了 TINYINT、ENUM、VARCHAR (N) 在存儲枚舉值的情況。鑒于前面描述的 ENUM 和 VARCHAR (N) 的一些情況，因此我的建議是首選 TINYINT，特殊場景適當選用其他類型。
參考文獻：
- MySQL 官方文檔：整形數據類型，https://dev.mysql.com/doc/refman/8.0/en/integer-types.html
- 《MySQL 技術內幕：SQL 編程》