MySQL數(shù)值類型溢出的處理方法

130次閱讀

共計 2862 個字符，預(yù)計需要花費 8 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

來，考考大家一個問題，在 MySQL 中當(dāng)某一列設(shè)置為 int(0) 時會發(fā)生什么？

為了演示這個問題，我們先要創(chuàng)建一個表

DROP TABLE IF EXISTS `na`;
CREATE TABLE `na` (n1 INT(0) NOT NULL DEFAULT '0',
n2 INT(11) NOT NULL DEFAULT '0'
);

然后我們使用下面的語句往 na 表中插入一些數(shù)據(jù)

mysql INSERT INTO `na` VALUES(520,520),(5201314,5201314);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

最后我們讀取出來看看

mysql SELECT * FROM na;
+---------+---------+
| n1 | n2 |
+---------+---------+
| 520 | 520 |
| 5201314 | 5201314 |
+---------+---------+
2 rows in set (0.00 sec)

對的，好像什么都不會發(fā)生，沒什么問題才是對的，我就怕有什么問題…哈哈

我們這一章節(jié)來講講整型溢出問題。

MySQL 數(shù)值類型溢出處理

當(dāng) MySQL 在某個數(shù)值列上存儲超出列數(shù)據(jù)類型允許范圍的值時，結(jié)果取決于當(dāng)時生效的 SQL 模式

如果啟用了嚴(yán)格的 SQL 模式，則 MySQL 會根據(jù) SQL 標(biāo)準(zhǔn)拒絕帶有錯誤的超出范圍的值，并且插入失敗
如果沒有啟用任何限制模式，那么 MySQL 會將值裁剪到列數(shù)據(jù)類型范圍的上下限值并存儲
當(dāng)超出范圍的值分配給整數(shù)列時，MySQL 會存儲表示列數(shù)據(jù)類型范圍的相應(yīng)端點的值
當(dāng)為浮點或定點列分配的值超出指定（或默認）精度和比例所隱含的范圍時，MySQL 會存儲表示該范圍的相應(yīng)端點的值

mysql SET sql_mode = ‘TRADITIONAL’; — 首先設(shè)置嚴(yán)格模式
mysql INSERT INTO t1 (i1, i2) VALUES(256, 256);
ERROR 1264 (22003): Out of range value for column ‘i1’ at row 1
mysql SELECT * FROM t1;
Empty set (0.00 sec)

當(dāng)嚴(yán)格模式被禁用，值可以插入，但會被裁剪，并且引發(fā)一個警告

mysql SET sql_mode = ''; -- 禁用所有模式
mysql INSERT INTO t1 (i1, i2) VALUES(256, 256);
mysql SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 1 |
| Warning | 1264 | Out of range value for column 'i2' at row 1 |
+---------+------+---------------------------------------------+
mysql SELECT * FROM t1;
+------+------+
| i1 | i2 |
+------+------+
| 127 | 255 |
+------+------+

如果未啟用嚴(yán)格 SQL 模式，對于 ALTER TABLE，LOAD DATA INFILE，UPDATE 和多行 INSERT 等語句會由于裁剪而發(fā)生的列分配轉(zhuǎn)換并且引發(fā)一個警告。

而如果啟用了嚴(yán)格模式，這些語句會直接失敗，并且未插入或更改部分或全部值，具體取決于表是否為事務(wù)表和其他因素。

數(shù)值表達式求值過程中的溢出會導(dǎo)致錯誤，例如，因為最大的有符號 BIGINT 值是 9223372036854775807，因此以下表達式會產(chǎn)生錯誤

mysql SELECT 9223372036854775807 + 1;
ERROR 1690 (22003): BIGINT value is out of range in '(9223372036854775807 + 1)'

為了在這種情況下使操作成功，需要將值轉(zhuǎn)換為 unsigned

mysql SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
| 9223372036854775808 |
+-------------------------------------------+

從另一方面說，是否發(fā)生溢出取決于操作數(shù)的范圍，因此處理前一個表達式的另一種方法是使用精確值算術(shù)，因為 DECIMAL 值的范圍大于整數(shù)

mysql SELECT 9223372036854775807.0 + 1;
+---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
| 9223372036854775808.0 |
+---------------------------+

整數(shù)數(shù)值之間的減去，如果其中一個類型為 UNSIGNED，默認情況下會生成無符號結(jié)果。如果為負，則會引發(fā)錯誤

mysql SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql SELECT CAST(0 AS UNSIGNED) - 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'

這種情況下，如果啟用了 NO_UNSIGNED_SUBTRACTION SQL 模式，則結(jié)果為負

mysql SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql SELECT CAST(0 AS UNSIGNED) - 1;
+-------------------------+
| CAST(0 AS UNSIGNED) - 1 |
+-------------------------+
| -1 |
+-------------------------+

如果此類操作的結(jié)果用于更新 UNSIGNED 整數(shù)列，則結(jié)果將裁剪為列類型的最大值，如果啟用了 NO_UNSIGNED_SUBTRACTION 則裁剪為 0。但如果啟用了嚴(yán)格的 SQL 模式，則會發(fā)生錯誤并且列保持不變。

后記

一切都是套路，套路…. 基本都和 SQL 模式有關(guān)…

總結(jié)

以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，謝謝大家對丸趣 TV 的支持。

向 AI 問一下細節(jié)

丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！

正文完