MySQL 五種整型資料型別的範圍與區別 tinyint smallint mediumint int bigint

神諭丶發表於2015-09-18
MySql資料型別
之前在論壇上看到一個有意思的問題，如果有一個欄位的值超過bigint，會發生什麼。
然後就看到有人噴，說0.1秒插入一個值，將bigint設定為無符號，插入到極限需要多久，根本不可能遇到如何如何。
（接下來會做個測試）

好了，進入正文：
首先需要知道一個概念，如果在定義的時候，設定UNSIGNED可以將範圍擴大一倍，該值意味著此欄位無符號（即不包含負數）。

根據文件可知，資料型別有五種，分別為
TINYINT，SMALLINT，MEDIUMINT，INT(INTEGER)，BIGINT。


其值的範圍，同樣查閱官方文件可知
TINYINT為 -128~127 無符號數則為127-(-128)=255(2^8-1)，因為非負，所以從0開始即0~255，以此類推：
SMALLINT為 -32768~32767 無符號數則為0~65535(2^16-1)
MEDIUMINT為 -8388608~8388607 無符號數則為0~16777215(2^24-1)
INT為-2147483648~2147483647 無符號數則為0~4294967295(2^32-1)
BIGINT為-9223372036854775808~9223372036854775807 無符號數則為0~18446744073709551615(2^64-1)
當然光是INT就已經達到了20億以上的數量級，十分夠用。
下表來自MySQL 5.6官方手冊
Type Storage Minimum Value Maximum Value
  (Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128 127
    0 255
SMALLINT 2 -32768 32767
    0 65535
MEDIUMINT 3 -8388608 8388607
    0 16777215
INT 4 -2147483648 2147483647
    0 4294967295
BIGINT 8 -9223372036854775808 9223372036854775807
    0 18446744073709551615
其儲存所需容量也可以看出，由小到大分別為
TINYINT            1位元組
SMALLINT            2位元組
MEDIUMINT            3位元組
INT                4位元組
BIGINT            8位元組

當然上述資料型別及範圍好像在sql server中也是適用的，同時也包括一些程式設計語言。

回到開始的話題，如果超過BIGINT的範圍怎麼辦？
其實mysql的處理方法和超過TINYINT一樣：
此處測試均為MySQL 5.6版本（社群版），儲存引擎為預設的InnoDB。

  1. mysql> CREATE TABLE a (id TINYINT UNSIGNED PRIMARY KEY AUTO_INCREMENT);
  2. Query OK, 0 rows affected (0.25 sec)

  4. mysql> INSERT INTO a SELECT 255;
  5. Query OK, 1 row affected (0.04 sec)
  6. Records: 1 Duplicates: 0 Warnings: 0

  8. mysql> INSERT INTO a SELECT 256;
  9. ERROR 1264 (22003): Out of range value for column 'id' at row 1

  11. mysql> INSERT INTO a SELECT NULL;
  12. ERROR 1062 (23000): Duplicate entry '255' for key 'PRIMARY'

  13.
  14. mysql> SELECT * FROM a;
  15. +-----+
  16. | id |
  17. +-----+
  18. | 255 |
  19. +-----+
  20. 1 row in set (0.00 sec)
首先插入255上限值，然後我做了如下兩個操作：
1、插入256，報錯ERROR 1264，即超過ID列的上限值。
2、插入NULL，此處應該自增，報錯ERROR 1062，即由於主鍵約束，故無法重複存在。

其報錯結果可以輕易看出，256是不能夠被插入的（廢話）
處理AUTO_INCREMENT約束時，即便遇到數值型別的上限值，仍然會嘗試插入，此時插入的值是上限值，即255。

此處可以看出此處的AUTO_INCREMENT=255，而不是256（即便255已經存在）。
注，此處（ENGINE=InnoDB後面）的AUTO_INCREMENT為MySQL認為該表的下一個自增欄位的值。
才
  1. mysql> SHOW CREATE TABLE a\G
  2. *************************** 1. row ***************************
  3.        Table: a
  4. Create Table: CREATE TABLE `a` (
  5.   `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  6.   PRIMARY KEY (`id`)
  7. ) ENGINE=InnoDB AUTO_INCREMENT=255 DEFAULT CHARSET=latin1
  8. 1 row in set (0.00 sec)

當然既然開始了，還是要做一下BIGINT的實驗。
  1. mysql> CREATE TABLE b (id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT);
  2. Query OK, 0 rows affected (0.26 sec)

  4. mysql> INSERT INTO b SELECT 18446744073709551615;
  5. Query OK, 1 row affected (0.05 sec)
  6. Records: 1 Duplicates: 0 Warnings: 0

  8. mysql> INSERT INTO b SELECT 18446744073709551616;
  9. ERROR 1264 (22003): Out of range value for column 'id' at row 1

  11. mysql> INSERT INTO b SELECT NULL;
  12. ERROR 1467 (HY000): Failed to read auto-increment value from storage engine

  14. mysql> SHOW CREATE TABLE b\G
  15. *************************** 1. row ***************************
  16.        Table: b
  17. Create Table: CREATE TABLE `b` (
  18.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  19.   PRIMARY KEY (`id`)
  20. ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1
  21. 1 row in set (0.00 sec)
同樣插入BIGINT UGSIGNED的上限值，然後繼續做如下兩個操作：
1、插入18446744073709551616，報錯ERROR 1264，即超過ID列的上限值。（與實驗1的報錯結果一致）
2、插入NULL，此處報錯與之前不同，為ERROR 1467。
ERROR 1467官方解釋為：

Error: 1467 SQLSTATE: HY000 ()

Message: Failed to read auto-increment value from storage engine

字面意思為“從儲存引擎讀取自增欄位失敗”。

搜了一下google，好像還真有人在實際運用中遇到這種問題，有個小哥提出瞭如下的方法：

After some searching i found the answer and it solved my problem.
run this sql query it will fix the problem

ALTER TABLE `YOUR_TABLE` AUTO_INCREMENT = 1
我也做了如下操作，但是…… 發現並未生效。

  1. mysql> ALTER TABLE b AUTO_INCREMENT = 1;
  2. Query OK, 0 rows affected (0.04 sec)
  3. Records: 0 Duplicates: 0 Warnings: 0

  5. mysql> INSERT INTO b SELECT NULL;
  6. ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
  7. mysql> SHOW CREATE TABLE b\G
  8. *************************** 1. row ***************************
  9.        Table: b
  10. Create Table: CREATE TABLE `b` (
  11.   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  12.   PRIMARY KEY (`id`)
  13. ) ENGINE=InnoDB AUTO_INCREMENT=18446744073709551615 DEFAULT CHARSET=latin1
  14. 1 row in set (0.00 sec)



額外兩個tips：
【1】由於TIMESTAMP用的其實是INT來儲存，故從1970年1月1日開始往後順延2147483647s，約24855天，即68年（到2038年）。
關於TIMESTAMP，官方給出的範圍是：
 '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC.


【2】
MySQL提供了一個叫SERIAL的”型別“
實際上是unsigned NOT NULL AUTO_INCREMENT UNIQUE的別名：

  1. mysql> CREATE TABLE test(a SERIAL);
  2. Query OK, 0 rows affected (0.41 sec)

  4. mysql> show create table test\G
  5. *************************** 1. row ***************************
  6.        Table: test
  7. Create Table: CREATE TABLE `test` (
  8.   `a` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  9.   UNIQUE KEY `a` (`a`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
  11. 1 row in set (0.00 sec)




關於整型資料型別寬度int(11)的解釋可參考
http://blog.itpub.net/29773961/viewspace-1804920/


作者公眾號（持續更新）

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29773961/viewspace-1803302/，如需轉載，請註明出處，否則將追究法律責任。

相關文章