在整型類型中，有 signed 和 unsigned 屬性，其表示的是整型的取值范圍，默認(rèn)為 signed。在設(shè)計(jì)時(shí)，我不建議你刻意去用 unsigned 屬性，因?yàn)樵谧鲆恍?shù)據(jù)分析時(shí)，SQL 可能返回的結(jié)果并不是想要得到的結(jié)果。

來(lái)看一個(gè)“銷售表 sale”的例子，其表結(jié)構(gòu)和數(shù)據(jù)如下。這里要特別注意，列 sale_count 用到的是 unsigned 屬性（即設(shè)計(jì)時(shí)希望列存儲(chǔ)的數(shù)值大于等于 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 表示每月的銷售數(shù)量。現(xiàn)在有一個(gè)需求，老板想要統(tǒng)計(jì)每個(gè)月銷售數(shù)量的變化，以此做商業(yè)決策。這條 SQL 語(yǔ)句需要應(yīng)用到非等值連接，但也并不是太難寫：

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;

然而，在執(zhí)行的過(guò)程中，由于列 sale_count 用到了 unsigned 屬性，會(huì)拋出這樣的結(jié)果：

ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`test`.`s2`.`sale_count` - `test`.`s1`.`sale_count`)'

可以看到，MySQL 提示用戶計(jì)算的結(jié)果超出了范圍。其實(shí)，這里 MySQL 要求 unsigned 數(shù)值相減之后依然為 unsigned，否則就會(huì)報(bào)錯(cuò)。

為了避免這個(gè)錯(cuò)誤，需要對(duì)數(shù)據(jù)庫(kù)參數(shù) sql_mode 設(shè)置為 NO_UNSIGNED_SUBTRACTION，允許相減的結(jié)果為 signed，這樣才能得到最終想要的結(jié)果：

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)

浮點(diǎn)類型和高精度型

除了整型類型，數(shù)字類型常用的還有浮點(diǎn)和高精度類型。
MySQL 之前的版本中存在浮點(diǎn)類型 Float 和 Double，但這些類型因?yàn)椴皇歉呔龋膊皇?SQL 標(biāo)準(zhǔn)的類型，所以在真實(shí)的生產(chǎn)環(huán)境中不推薦使用，否則在計(jì)算時(shí)，由于精度類型問題，會(huì)導(dǎo)致最終的計(jì)算結(jié)果出錯(cuò)。
更重要的是，從 MySQL 8.0.17 版本開始，當(dāng)創(chuàng)建表用到類型 Float 或 Double 時(shí)，會(huì)拋出下面的警告：MySQL 提醒用戶不該用上述浮點(diǎn)類型，甚至提醒將在之后版本中廢棄浮點(diǎn)類型

Specifying number of digits for floating point data types is deprecated and will be removed in a future release

而數(shù)字類型中的高精度 DECIMAL 類型可以使用，當(dāng)聲明該類型列時(shí)，可以（并且通常必須要）指定精度和標(biāo)度，例如：

salary DECIMAL(8,2)

其中，8 是精度（精度表示保存值的主要位數(shù)），2 是標(biāo)度（標(biāo)度表示小數(shù)點(diǎn)后面保存的位數(shù)）。通常在表結(jié)構(gòu)設(shè)計(jì)中，類型 DECIMAL 可以用來(lái)表示用戶的工資、賬戶的余額等精確到小數(shù)點(diǎn)后 2 位的業(yè)務(wù)。

然而，在海量并發(fā)的互聯(lián)網(wǎng)業(yè)務(wù)中使用，金額字段的設(shè)計(jì)并不推薦使用 DECIMAL 類型，而更推薦使用 INT 整型類型（下文就會(huì)分析原因）。

業(yè)務(wù)表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn)

整型類型與自增設(shè)計(jì)

在真實(shí)業(yè)務(wù)場(chǎng)景中，整型類型最常見的就是在業(yè)務(wù)中用來(lái)表示某件物品的數(shù)量。例如上述表的銷售數(shù)量，或電商中的庫(kù)存數(shù)量、購(gòu)買次數(shù)等。在業(yè)務(wù)中，整型類型的另一個(gè)常見且重要的使用用法是作為表的主鍵，即用來(lái)唯一標(biāo)識(shí)一行數(shù)據(jù)。
整型結(jié)合屬性 auto_increment，可以實(shí)現(xiàn)自增功能，但在表結(jié)構(gòu)設(shè)計(jì)時(shí)用自增做主鍵，希望你特別要注意以下兩點(diǎn)，若不注意，可能會(huì)對(duì)業(yè)務(wù)造成災(zāi)難性的打擊：

• 用 BIGINT 做主鍵，而不是 INT；
• 自增值并不持久化，可能會(huì)有回溯現(xiàn)象（MySQL 8.0 版本前）。

從表 1 可以發(fā)現(xiàn)，INT 的范圍最大在 42 億的級(jí)別，在真實(shí)的互聯(lián)網(wǎng)業(yè)務(wù)場(chǎng)景的應(yīng)用中，很容易達(dá)到最大值。例如一些流水表、日志表，每天 1000W 數(shù)據(jù)量，420 天后，INT 類型的上限即可達(dá)到。
因此，用自增整型做主鍵，一律使用 BIGINT，而不是 INT。不要為了節(jié)省 4 個(gè)字節(jié)使用 INT，當(dāng)達(dá)到上限時(shí)，再進(jìn)行表結(jié)構(gòu)的變更，將是巨大的負(fù)擔(dān)與痛苦。
那這里又引申出一個(gè)有意思的問題：如果達(dá)到了 INT 類型的上限，數(shù)據(jù)庫(kù)的表現(xiàn)又將如何呢？是會(huì)重新變?yōu)?1？我們可以通過(guò)下面的 SQL 語(yǔ)句驗(yàn)證一下：

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'

可以看到，當(dāng)達(dá)到 INT 上限后，再次進(jìn)行自增插入時(shí)，會(huì)報(bào)重復(fù)錯(cuò)誤，MySQL 數(shù)據(jù)庫(kù)并不會(huì)自動(dòng)將其重置為 1。
第二個(gè)特別要注意的問題是，MySQL 8.0 版本前，自增不持久化，自增值可能會(huì)存在回溯問題！

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 的這條記錄后，下一個(gè)自增值依然為 4（AUTO_INCREMENT=4），這里并沒有錯(cuò)誤，自增并不會(huì)進(jìn)行回溯。但若這時(shí)數(shù)據(jù)庫(kù)發(fā)生重啟，那數(shù)據(jù)庫(kù)啟動(dòng)后，表 t 的自增起始值將再次變?yōu)?3，即自增值發(fā)生回溯。具體如下所示：

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

若要徹底解決這個(gè)問題，有以下 2 種方法：

• 升級(jí) MySQL 版本到 8.0 版本，每張表的自增值會(huì)持久化；
• 若無(wú)法升級(jí)數(shù)據(jù)庫(kù)版本，則強(qiáng)烈不推薦在核心業(yè)務(wù)表中使用自增數(shù)據(jù)類型做主鍵。

其實(shí)，在海量互聯(lián)網(wǎng)架構(gòu)設(shè)計(jì)過(guò)程中，為了之后更好的分布式架構(gòu)擴(kuò)展性，不建議使用整型類型做主鍵，更為推薦的是字符串類型。

資金字段設(shè)計(jì)

在用戶余額、基金賬戶余額、數(shù)字錢包、零錢等的業(yè)務(wù)設(shè)計(jì)中，由于字段都是資金字段，通常程序員習(xí)慣使用 DECIMAL 類型作為字段的選型，因?yàn)檫@樣可以精確到分，如：DECIMAL(8,2)。

CREATE TABLE User (

  userId BIGINT AUTO_INCREMENT,

  money DECIMAL(8,2) NOT NULL,

  ......

)

在海量互聯(lián)網(wǎng)業(yè)務(wù)的設(shè)計(jì)標(biāo)準(zhǔn)中，并不推薦用 DECIMAL 類型，而是更推薦將 DECIMAL 轉(zhuǎn)化為 整型類型。也就是說(shuō)，資金類型更推薦使用用分單位存儲(chǔ)，而不是用元單位存儲(chǔ)。如1元在數(shù)據(jù)庫(kù)中用整型類型 100 存儲(chǔ)。

金額字段的取值范圍如果用 DECIMAL 表示的，如何定義長(zhǎng)度呢？因?yàn)轭愋?DECIMAL 是個(gè)變長(zhǎng)字段，若要定義金額字段，則定義為 DECIMAL(8,2) 是遠(yuǎn)遠(yuǎn)不夠的。這樣只能表示存儲(chǔ)最大值為 999999.99，百萬(wàn)級(jí)的資金存儲(chǔ)。

用戶的金額至少要存儲(chǔ)百億的字段，而統(tǒng)計(jì)局的 GDP 金額字段則可能達(dá)到數(shù)十萬(wàn)億級(jí)別。用類型 DECIMAL 定義，不好統(tǒng)一。
另外重要的是，類型 DECIMAL 是通過(guò)二進(jìn)制實(shí)現(xiàn)的一種編碼方式，計(jì)算效率遠(yuǎn)不如整型來(lái)的高效。因此，推薦使用 BIG INT 來(lái)存儲(chǔ)金額相關(guān)的字段。

字段存儲(chǔ)時(shí)采用分存儲(chǔ)，即便這樣 BIG INT 也能存儲(chǔ)千兆級(jí)別的金額。這里，1兆 = 1萬(wàn)億。

這樣的好處是，所有金額相關(guān)字段都是定長(zhǎng)字段，占用 8 個(gè)字節(jié)，存儲(chǔ)高效。另一點(diǎn)，直接通過(guò)整型計(jì)算，效率更高。
注意，在數(shù)據(jù)庫(kù)設(shè)計(jì)中，我們非常強(qiáng)調(diào)定長(zhǎng)存儲(chǔ)，因?yàn)槎ㄩL(zhǎng)存儲(chǔ)的性能更好。

我們來(lái)看在數(shù)據(jù)庫(kù)中記錄的存儲(chǔ)方式，大致如下：

若發(fā)生更新，記錄 1 原先的空間無(wú)法容納更新后記錄 1 的存儲(chǔ)空間，因此，這時(shí)數(shù)據(jù)庫(kù)會(huì)將記錄 1 標(biāo)記為刪除，尋找新的空間給記錄1使用，如：

上圖中*記錄 1 表示的就是原先記錄 1 占用的空間，而這個(gè)空間后續(xù)將變成碎片空間，無(wú)法繼續(xù)使用，除非人為地進(jìn)行表空間的碎片整理。

那么，當(dāng)使用 BIG INT 存儲(chǔ)金額字段的時(shí)候，如何表示小數(shù)點(diǎn)中的數(shù)據(jù)呢？其實(shí)，這部分完全可以交由前端進(jìn)行處理并展示。作為數(shù)據(jù)庫(kù)本身，只要按分進(jìn)行存儲(chǔ)即可。

到此這篇關(guān)于MySQL數(shù)字類型自增的坑的文章就介紹到這了,更多相關(guān)MySQL數(shù)字類型自增內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！