| MySQL數據類型 | 含義（有符號） |
|---|---|
| tinyint(m) | 1個字節 范圍(-128~127) |
| smallint(m) | 2個字節 范圍(-32768~32767) |
| mediumint(m) | 3個字節 范圍(-8388608~8388607) |
| int(m) | 4個字節 范圍(-2147483648~2147483647) |
| bigint(m) | 8個字節 范圍(+-9.22*10的18次方) |
在整型類型中，有 signed 和 unsigned 屬性，其表示的是整型的取值范圍，默認為 signed。在設計時，我不建議你刻意去用 unsigned 屬性，因為在做一些數據分析時，SQL 可能返回的結果并不是想要得到的結果。
來看一個“銷售表 sale”的例子，其表結構和數據如下。這里要特別注意，列 sale_count 用到的是 unsigned 屬性（即設計時希望列存儲的數值大于等于 0）：
mysql> SHOW CREATE TABLE sale\G
*************************** 1. row ***************************
Table: sale
Create Table: CREATE TABLE `sale` (
`sale_date` date NOT NULL,
`sale_count` int unsigned DEFAULT NULL,
PRIMARY KEY (`sale_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec)
mysql> SELECT * FROM sale;
+------------+------------+
| sale_date | sale_count |
+------------+------------+
| 2020-01-01 | 10000 |
| 2020-02-01 | 8000 |
| 2020-03-01 | 12000 |
| 2020-04-01 | 9000 |
| 2020-05-01 | 10000 |
| 2020-06-01 | 18000 |
+------------+------------+
6 rows in set (0.00 sec)
其中，sale_date 表示銷售的日期，sale_count 表示每月的銷售數量。現在有一個需求，老板想要統計每個月銷售數量的變化，以此做商業決策。這條 SQL 語句需要應用到非等值連接，但也并不是太難寫：
SELECT
s1.sale_date, s2.sale_count - s1.sale_count AS diff
FROM
sale s1
LEFT JOIN
sale s2 ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date
ORDER BY sale_date;
然而，在執行的過程中，由于列 sale_count 用到了 unsigned 屬性，會拋出這樣的結果：
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`s2`.`sale_count` - `test`.`s1`.`sale_count`)'
可以看到，MySQL 提示用戶計算的結果超出了范圍。其實，這里 MySQL 要求 unsigned 數值相減之后依然為 unsigned，否則就會報錯。
為了避免這個錯誤，需要對數據庫參數 sql_mode 設置為 NO_UNSIGNED_SUBTRACTION，允許相減的結果為 signed，這樣才能得到最終想要的結果：
mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION';
Query OK, 0 rows affected (0.00 sec)
SELECT
s1.sale_date,
IFNULL(s2.sale_count - s1.sale_count,'') AS diff
FROM
sale s1
LEFT JOIN sale s2
ON DATE_ADD(s2.sale_date, INTERVAL 1 MONTH) = s1.sale_date
ORDER BY sale_date;
+------------+-------+
| sale_date | diff |
+------------+-------+
| 2020-01-01 | |
| 2020-02-01 | 2000 |
| 2020-03-01 | -4000 |
| 2020-04-01 | 3000 |
| 2020-05-01 | -1000 |
| 2020-06-01 | -8000 |
+------------+-------+
6 rows in set (0.00 sec)
除了整型類型，數字類型常用的還有浮點和高精度類型。
MySQL 之前的版本中存在浮點類型 Float 和 Double，但這些類型因為不是高精度，也不是 SQL 標準的類型，所以在真實的生產環境中不推薦使用，否則在計算時，由于精度類型問題，會導致最終的計算結果出錯。
更重要的是，從 MySQL 8.0.17 版本開始，當創建表用到類型 Float 或 Double 時，會拋出下面的警告：MySQL 提醒用戶不該用上述浮點類型，甚至提醒將在之后版本中廢棄浮點類型
Specifying number of digits for floating point data types is deprecated and will be removed in a future release
而數字類型中的高精度 DECIMAL 類型可以使用，當聲明該類型列時，可以（并且通常必須要）指定精度和標度，例如：
salary DECIMAL(8,2)
其中，8 是精度（精度表示保存值的主要位數），2 是標度（標度表示小數點后面保存的位數）。通常在表結構設計中，類型 DECIMAL 可以用來表示用戶的工資、賬戶的余額等精確到小數點后 2 位的業務。
然而，在海量并發的互聯網業務中使用，金額字段的設計并不推薦使用 DECIMAL 類型，而更推薦使用 INT 整型類型（下文就會分析原因）。
在真實業務場景中，整型類型最常見的就是在業務中用來表示某件物品的數量。例如上述表的銷售數量，或電商中的庫存數量、購買次數等。在業務中，整型類型的另一個常見且重要的使用用法是作為表的主鍵，即用來唯一標識一行數據。
整型結合屬性 auto_increment，可以實現自增功能，但在表結構設計時用自增做主鍵，希望你特別要注意以下兩點，若不注意，可能會對業務造成災難性的打擊：
從表 1 可以發現，INT 的范圍最大在 42 億的級別，在真實的互聯網業務場景的應用中，很容易達到最大值。例如一些流水表、日志表，每天 1000W 數據量，420 天后，INT 類型的上限即可達到。
因此，用自增整型做主鍵，一律使用 BIGINT，而不是 INT。不要為了節省 4 個字節使用 INT，當達到上限時，再進行表結構的變更，將是巨大的負擔與痛苦。
那這里又引申出一個有意思的問題：如果達到了 INT 類型的上限，數據庫的表現又將如何呢？是會重新變為 1？我們可以通過下面的 SQL 語句驗證一下：
mysql> CREATE TABLE t (
-> a INT AUTO_INCREMENT PRIMARY KEY
-> );
mysql> INSERT INTO t VALUES (2147483647);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t VALUES (NULL);
ERROR 1062 (23000): Duplicate entry '2147483647' for key 't.PRIMARY'
可以看到，當達到 INT 上限后，再次進行自增插入時，會報重復錯誤，MySQL 數據庫并不會自動將其重置為 1。
第二個特別要注意的問題是，MySQL 8.0 版本前，自增不持久化，自增值可能會存在回溯問題！
mysql> SELECT * FROM t;
+---+
| a |
+---+
| 1 |
| 2 |
| 3 |
+---+
3 rows in set (0.01 sec)
mysql> DELETE FROM t WHERE a = 3;
Query OK, 1 row affected (0.02 sec)
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 sec
可以看到，在刪除自增為 3 的這條記錄后，下一個自增值依然為 4（AUTO_INCREMENT=4），這里并沒有錯誤，自增并不會進行回溯。但若這時數據庫發生重啟，那數據庫啟動后，表 t 的自增起始值將再次變為 3，即自增值發生回溯。具體如下所示：
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
1 row in set (0.00 s
若要徹底解決這個問題，有以下 2 種方法：
其實，在海量互聯網架構設計過程中，為了之后更好的分布式架構擴展性，不建議使用整型類型做主鍵，更為推薦的是字符串類型。
在用戶余額、基金賬戶余額、數字錢包、零錢等的業務設計中，由于字段都是資金字段，通常程序員習慣使用 DECIMAL 類型作為字段的選型，因為這樣可以精確到分，如：DECIMAL(8,2)。
CREATE TABLE User ( userId BIGINT AUTO_INCREMENT, money DECIMAL(8,2) NOT NULL, ...... )
在海量互聯網業務的設計標準中，并不推薦用 DECIMAL 類型，而是更推薦將 DECIMAL 轉化為 整型類型。也就是說，資金類型更推薦使用用分單位存儲，而不是用元單位存儲。如1元在數據庫中用整型類型 100 存儲。
金額字段的取值范圍如果用 DECIMAL 表示的，如何定義長度呢？因為類型 DECIMAL 是個變長字段，若要定義金額字段，則定義為 DECIMAL(8,2) 是遠遠不夠的。這樣只能表示存儲最大值為 999999.99，百萬級的資金存儲。
用戶的金額至少要存儲百億的字段，而統計局的 GDP 金額字段則可能達到數十萬億級別。用類型 DECIMAL 定義，不好統一。
另外重要的是，類型 DECIMAL 是通過二進制實現的一種編碼方式，計算效率遠不如整型來的高效。因此，推薦使用 BIG INT 來存儲金額相關的字段。
字段存儲時采用分存儲，即便這樣 BIG INT 也能存儲千兆級別的金額。這里，1兆 = 1萬億。
這樣的好處是，所有金額相關字段都是定長字段，占用 8 個字節，存儲高效。另一點，直接通過整型計算，效率更高。
注意，在數據庫設計中，我們非常強調定長存儲，因為定長存儲的性能更好。
我們來看在數據庫中記錄的存儲方式，大致如下：
若發生更新，記錄 1 原先的空間無法容納更新后記錄 1 的存儲空間，因此，這時數據庫會將記錄 1 標記為刪除，尋找新的空間給記錄1使用，如：
上圖中*記錄 1 表示的就是原先記錄 1 占用的空間，而這個空間后續將變成碎片空間，無法繼續使用，除非人為地進行表空間的碎片整理。
那么，當使用 BIG INT 存儲金額字段的時候，如何表示小數點中的數據呢？其實，這部分完全可以交由前端進行處理并展示。作為數據庫本身，只要按分進行存儲即可。
到此這篇關于MySQL數字類型自增的坑的文章就介紹到這了,更多相關MySQL數字類型自增內容請搜索腳本之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持腳本之家！
