MySQL數(shù)值類型溢出應(yīng)該如何處理

下文主要給大家?guī)?lái)MySQL數(shù)值類型溢出應(yīng)該如何處理，希望這些內(nèi)容能夠帶給大家實(shí)際用處，這也是我編輯MySQL數(shù)值類型溢出應(yīng)該如何處理這篇文章的主要目的。好了，廢話不多說(shuō)，大家直接看下文吧。

為了演示這個(gè)問(wèn)題，我們先要?jiǎng)?chuàng)建一個(gè)表

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

然后我們使用下面的語(yǔ)句往 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

最后我們讀取出來(lái)看看

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

對(duì)的，好像什么都不會(huì)發(fā)生，沒(méi)什么問(wèn)題才是對(duì)的，我就怕有什么問(wèn)題…哈哈

我們這一章節(jié)來(lái)講講整型溢出問(wèn)題。

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

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

• 如果啟用了嚴(yán)格的 SQL 模式，則 MySQL 會(huì)根據(jù) SQL 標(biāo)準(zhǔn)拒絕帶有錯(cuò)誤的超出范圍的值，并且插入失敗

• 如果沒(méi)有啟用任何限制模式，那么 MySQL 會(huì)將值裁剪到列數(shù)據(jù)類型范圍的上下限值并存儲(chǔ)

  1. 當(dāng)超出范圍的值分配給整數(shù)列時(shí)，MySQL 會(huì)存儲(chǔ)表示列數(shù)據(jù)類型范圍的相應(yīng)端點(diǎn)的值

  2. 當(dāng)為浮點(diǎn)或定點(diǎn)列分配的值超出指定（或默認(rèn)）精度和比例所隱含的范圍時(shí)，MySQL 會(huì)存儲(chǔ)表示該范圍的相應(yīng)端點(diǎn)的值

這個(gè)，應(yīng)該很好理解吧？

我們舉一個(gè)例子，假設(shè) t1 表的結(jié)構(gòu)如下

 CREATE TABLE t1 (
    i1 TINYINT,
    i2 TINYINT UNSIGNED
);

如果啟用了嚴(yán)格的 SQL 模式，超出范圍會(huì)發(fā)生一個(gè)錯(cuò)誤

 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)格模式被禁用，值可以插入，但會(huì)被裁剪，并且引發(fā)一個(gè)警告

 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 模式，對(duì)于 ALTER TABLE，LOAD DATA INFILE，UPDATE 和多行 INSERT等語(yǔ)句會(huì)由于裁剪而發(fā)生的列分配轉(zhuǎn)換并且引發(fā)一個(gè)警告。

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

數(shù)值表達(dá)式求值過(guò)程中的溢出會(huì)導(dǎo)致錯(cuò)誤，例如，因?yàn)樽畲蟮挠蟹?hào) BIGINT 值是 9223372036854775807，因此以下表達(dá)式會(huì)產(chǎn)生錯(cuò)誤

 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 |
+-------------------------------------------+

從另一方面說(shuō)，是否發(fā)生溢出取決于操作數(shù)的范圍，因此處理前一個(gè)表達(dá)式的另一種方法是使用精確值算術(shù)，因?yàn)?nbsp;DECIMAL 值的范圍大于整數(shù)

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

整數(shù)數(shù)值之間的減去，如果其中一個(gè)類型為 UNSIGNED ，默認(rèn)情況下會(huì)生成無(wú)符號(hào)結(jié)果。如果為負(fù)，則會(huì)引發(fā)錯(cuò)誤

 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é)果為負(fù)

 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 模式，則會(huì)發(fā)生錯(cuò)誤并且列保持不變。

對(duì)于以上關(guān)于MySQL數(shù)值類型溢出應(yīng)該如何處理，大家是不是覺(jué)得非常有幫助。如果需要了解更多內(nèi)容，請(qǐng)繼續(xù)關(guān)注我們的行業(yè)資訊，相信你會(huì)喜歡上這些內(nèi)容的。