主頁 > 知識庫 > MySQL數(shù)字類型自增的坑

MySQL數(shù)字類型自增的坑

熱門標(biāo)簽:大豐地圖標(biāo)注app 呂梁外呼系統(tǒng) html地圖標(biāo)注并導(dǎo)航 400電話辦理服務(wù)價格最實惠 南太平洋地圖標(biāo)注 北京金倫外呼系統(tǒng) 400電話變更申請 武漢電銷機(jī)器人電話 催天下外呼系統(tǒng)

在進(jìn)行表結(jié)構(gòu)設(shè)計時,數(shù)字類型是最為常見的類型之一,但要用好數(shù)字類型并不如想象得那么簡單,比如:

  • 怎么設(shè)計一個互聯(lián)網(wǎng)海量并發(fā)業(yè)務(wù)的自增主鍵?用 INT 就夠了?
  • 怎么設(shè)計賬戶的余額?用 DECIMAL 類型就萬無一失了嗎?

以上全錯!

數(shù)字類型看似簡單,但在表結(jié)構(gòu)架構(gòu)設(shè)計中很容易出現(xiàn)上述“設(shè)計上思考不全面”的問題(特別是在海量并發(fā)的互聯(lián)網(wǎng)場景下)

數(shù)字類型

整數(shù)類型

MySQL 數(shù)據(jù)庫支持 SQL 標(biāo)準(zhǔn)支持的整型類型:INT、SMALLINT。此外,MySQL 數(shù)據(jù)庫也支持諸如 TINYINT、MEDIUMINT 和 BIGINT 整型類型(表 1 顯示了各種整型所占用的存儲空間及取值范圍):

MySQL數(shù)據(jù)類型 含義(有符號)
tinyint(m) 1個字節(jié) 范圍(-128~127)
smallint(m) 2個字節(jié) 范圍(-32768~32767)
mediumint(m) 3個字節(jié) 范圍(-8388608~8388607)
int(m) 4個字節(jié) 范圍(-2147483648~2147483647)
bigint(m) 8個字節(jié) 范圍(+-9.22*10的18次方)

在整型類型中,有 signed 和 unsigned 屬性,其表示的是整型的取值范圍,默認(rèn)為 signed。在設(shè)計時,我不建議你刻意去用 unsigned 屬性,因為在做一些數(shù)據(jù)分析時,SQL 可能返回的結(jié)果并不是想要得到的結(jié)果。

來看一個“銷售表 sale”的例子,其表結(jié)構(gòu)和數(shù)據(jù)如下。這里要特別注意,列 sale_count 用到的是 unsigned 屬性(即設(shè)計時希望列存儲的數(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ù)量?,F(xiàn)在有一個需求,老板想要統(tǒng)計每個月銷售數(shù)量的變化,以此做商業(yè)決策。這條 SQL 語句需要應(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í)行的過程中,由于列 sale_count 用到了 unsigned 屬性,會拋出這樣的結(jié)果:

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

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

為了避免這個錯誤,需要對數(shù)據(jù)庫參數(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)

浮點類型和高精度型

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

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

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

salary DECIMAL(8,2)

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

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

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

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

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

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

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

可以看到,當(dāng)達(dá)到 INT 上限后,再次進(jìn)行自增插入時,會報重復(fù)錯誤,MySQL 數(shù)據(jù)庫并不會自動將其重置為 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),這里并沒有錯誤,自增并不會進(jìn)行回溯。但若這時數(shù)據(jù)庫發(fā)生重啟,那數(shù)據(jù)庫啟動后,表 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

若要徹底解決這個問題,有以下 2 種方法:

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

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

資金字段設(shè)計

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

CREATE TABLE User (

  userId BIGINT AUTO_INCREMENT,

  money DECIMAL(8,2) NOT NULL,

  ......

)

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

金額字段的取值范圍如果用 DECIMAL 表示的,如何定義長度呢?因為類型 DECIMAL 是個變長字段,若要定義金額字段,則定義為 DECIMAL(8,2) 是遠(yuǎn)遠(yuǎn)不夠的。這樣只能表示存儲最大值為 999999.99,百萬級的資金存儲。

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

字段存儲時采用分存儲,即便這樣 BIG INT 也能存儲千兆級別的金額。這里,1兆 = 1萬億。

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

我們來看在數(shù)據(jù)庫中記錄的存儲方式,大致如下:

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

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

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

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

您可能感興趣的文章:
  • MySQL中日期型單行函數(shù)代碼詳解
  • Mysql 日期格式化及復(fù)雜日期區(qū)間查詢
  • MySQL 生成隨機(jī)數(shù)字、字符串、日期、驗證碼及 UUID的方法
  • MySQL關(guān)于字符串中數(shù)字排序的問題分析
  • MySQL實例精講單行函數(shù)以及字符數(shù)學(xué)日期流程控制

標(biāo)簽:徐州 迪慶 無錫 自貢 西寧 麗水 南充 龍巖

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL數(shù)字類型自增的坑》,本文關(guān)鍵詞  MySQL,數(shù)字,類型,自增,的,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL數(shù)字類型自增的坑》相關(guān)的同類信息!
  • 本頁收集關(guān)于MySQL數(shù)字類型自增的坑的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章