萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL UNSIGNED 類型詳解:範圍擴展、遷移陷阱與減法溢出終極解決方案

什麼是 UNSIGNED?

UNSIGNED 表示**無符號(非負數)**,僅適用於整數類型,是 MySQL 非標準擴展(非 ANSI SQL)。

雙重作用:

  1. 業務約束:數據庫層面強制非負,防止負數污染
  2. 範圍擴展:將符號位用於數值,正數上限翻倍

整數類型範圍對比表

類型存儲有符號範圍無符號範圍無符號上限增益
TINYINT1 字節-128 ~ 1270 ~ 255+128
SMALLINT2 字節-32,768 ~ 32,7670 ~ 65,535+32,768
MEDIUMINT3 字節-8,388,608 ~ 8,388,6070 ~ 16,777,215+8,388,608
INT4 字節-2,147,483,648 ~ 2,147,483,6470 ~ 4,294,967,295+2,147,483,648
BIGINT8 字節-9,223,372,036,854,775,808 ~ 9,223,372,036,854,775,8070 ~ 18,446,744,073,709,551,615+9,223,372,036,854,775,808

浮點/定點類型 不支持 UNSIGNED

  • FLOAT、DOUBLE、DECIMAL 僅有正負範圍與精度區分,無 UNSIGNED 屬性
  • 最小非零值反映精度:FLOAT ~1.17e-38,DOUBLE ~2.22e-308

遷移與兼容性警示

  • 非標準:遷移至 PostgreSQL、Oracle、SQL Server 時需手動處理(Check Constraint 或應用層校驗)
  • 驅動程序映射:部分 ORM/驅動可能將 UNSIGNED INT 映射為語言端的長整型(如 Java Long),需注意類型匹配
  • 建議:除非極度需要擴展範圍,否則**優先使用有符號類型 + 應用層校驗**,提升可移植性

核心坑點:UNSIGNED 減法溢出(補碼回滾)

現象復現

CREATE TABLE t (id INT UNSIGNED);
INSERT INTO t VALUES (0);
UPDATE t SET id = id - 1;  -- 報錯或數據變為 4294967295 (MAX_UINT)

MySQL 5.x 為了速度,直接進行二進制補碼運算。0 - 1 在無符號下回滾為最大值,**不報錯、不截斷、靜默產生髒數據**。

場景:扣減庫存、扣減餘額、倒計時

-- 極度危險:餘額不足時直接變成 42 億
UPDATE account SET balance = balance - 100 WHERE user_id = 1;

終極解決方案:CONVERT(... AS SIGNED) 強制有符號運算

語法

UPDATE `table` 
SET `unsigned_col` = CONVERT(`unsigned_col` + (負數值) AS SIGNED) 
WHERE ...;

實戰示例

-- 扣減 8,安全版本
UPDATE `user` 
SET `tk` = CONVERT(`tk` + (-8), SIGNED) 
WHERE `id` = '1330';

-- 通用安全扣減模板(防止負數)
UPDATE `account`
SET `balance` = CONVERT(`balance` - 100, SIGNED)
WHERE `user_id` = 123
  AND CONVERT(`balance` - 100, SIGNED) >= 0;  -- 應用層再判一次更稳妥

原理

CONVERT(expr, SIGNED) 強制將表達式在**有符號整數域**計算,結果可為負數。MySQL 階段性地將無符號列值提升為有符號上下文參與運算,避免補碼回滾。最終寫入時若結果為負數,會報錯 Out of range value(嚴格模式)或截斷為 0(非嚴格模式),**至少不會產生巨大正數髒數據**。

進階最佳實踐

  1. 嚴格 SQL 模式:SET sql_mode = 'STRICT_ALL_TABLES,NO_UNSIGNED_SUBTRACTION'。NO_UNSIGNED_SUBTRACTION 使減法結果為負時直接報錯,而非回滾。
  2. 應用層雙重守護:Service 層先 if (balance < amount) throw,再執行 SQL
  3. 存儲過程封裝:將扣減邏輯封裝為原子存儲過程,統一使用 CONVERT(... AS SIGNED) + 判斷
  4. 遷移時清洗:導出數據前檢查是否存在接近 MAX_UINT 的異常值,疑似溢出殘留

總結

UNSIGNED 是把雙刃劍:範圍翻倍的誘惑背後,隱藏著**減法溢出靜默產生髒數據**的致命陷阱。掌握 CONVERT(... AS SIGNED) 這一「護身符」,配合嚴格 SQL 模式與應用層校驗,才能在享受範圍紅利的同時守住數據正確性底線。遷移異構數據庫時,請毫不猶豫地拋棄 UNSIGNED,擁抱標準與可移植性。

copyright © 萬盛學電腦網 all rights reserved