SQL 資料型態 – INT (Integer)

測試環境為 CentOS 7 x86_64 虛擬機.

INT (Integer) 整數型態的種類如下:

型態	Byte(s)	預設長度	有號數 (Signed +,-) 範圍	無號數 (Unsigned) 範圍
TINYINT	1	4	-128~127	0~255
SMALLINT	2	6	-32768~32767	0~65535
MEDIUMINT	3	9	-8388608~8388607	0~16777215
INT	4	11	-2147683648~2147683647	0~4294967295
BIGINT	8	20	-9223372036854775808~9223372036854775807	0~18446744073709551615

那預設長度是做什麼? 輸入超過數值的範圍時候會發生什麼事情?

先建立一個測試用的資料庫 (Database).

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE intdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE intdb;
Database changed

預設長度是做什麼?

先建立一個 tinyint 整數數值欄位的資料表 (table) 並使用 unsigned 與 zerofill.

MariaDB [intdb]> CREATE TABLE num1 (t1 TINYINT(4) ,t2 TINYINT(5) UNSIGNED,t3 TINYINT(6) ZEROFILL);
Query OK, 0 rows affected (0.02 sec)

MariaDB [intdb]> DESCRIBE num1;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| t1    | tinyint(4)                   | YES  |     | NULL    |       |
| t2    | tinyint(5) unsigned          | YES  |     | NULL    |       |
| t3    | tinyint(6) unsigned zerofill | YES  |     | NULL    |       |
+-------+------------------------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

• 有號數範圍 (Signed 為 預設值,有包含 +,-)
  範圍要怎麼算 以 TINYINT 為範例,容量為 1 Byte(s) = 1×8 = 8 bit(s) , 2⁸ 可以儲存 256 種數值.
  數值可以從 -128 (2⁷) ~ 127(2⁷-1)
• 無號數範圍 (Unsigned)
  數值可以從 0 (2⁰-1) ~255(2⁸-1)
• ZEROFILL
  會在數字的前面補 0,補多少個 0 是依據你設定的預設長度.

MariaDB [intdb]> INSERT INTO num1 VALUES (100,100,100);
Query OK, 1 row affected (0.01 sec)

MariaDB [intdb]> SELECT * FROM num1;
+------+------+--------+
| t1   | t2   | t3     |
+------+------+--------+
|  100 |  100 | 000100 |
+------+------+--------+
1 row in set (0.00 sec)

雖然都是輸入 100 ,但可以看到 t3 的顯示不一樣.因為 t3 欄位格式為 tinyint(6) unsigned zerofill -會顯示 6格長度(前面補 0),t2 雖然為 5格長度但他沒有設定 zerofill ,所以數字前不會額外顯示 0 .

預設長度是做什麼? 當你有設定 zerofill 預設長度才有用途(數字前面補 0).

超過數值範圍的時候會發生什麼事情?

先建立一個 tinyint 整數數值欄位的資料表 (table) 並使用 signed 與 unsigned.

MariaDB [intdb]> CREATE TABLE num2 (t1 TINYINT(4) ,t2 TINYINT(5) UNSIGNED);
Query OK, 0 rows affected (0.02 sec)

MariaDB [intdb]> DESCRIBE num2;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| t1    | tinyint(4)          | YES  |     | NULL    |       |
| t2    | tinyint(5) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

沒設定時預設為 signed.

• 有號數範圍 (Signed 為 預設值,有包含 +,-)
  範圍要怎麼算 以 TINYINT 為範例,容量為 1 Byte(s) = 1×8 = 8 bit(s) , 2⁸ 可以儲存 256 種數值.
  數值可以從 -128 (2⁷) ~ 127(2⁷-1)
• 無號數範圍 (Unsigned)
  數值可以從 0 (2⁰-1) ~255(2⁸-1)

下面範例可以觀察到,雖然 t1 設定 200 已超出範圍 (-128~127) ,系統會幫忙設定到容許的最高值 (127)

MariaDB [intdb]> INSERT INTO num2 VALUES (200,200);
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [intdb]> SELECT * FROM num2;
+------+------+
| t1   | t2   |
+------+------+
|  127 |  200 |
+------+------+
1 row in set (0.00 sec)

我們可以透過 SHOW WARNINGS; 來看警告訊息

MariaDB [intdb]> INSERT INTO num2 VALUES (200,200);
Query OK, 1 row affected, 1 warning (0.00 sec)

MariaDB [intdb]> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 't1' at row 1 |
+---------+------+---------------------------------------------+
1 row in set (0.00 sec)

或是打開傳統模式,輸入的值不在容許的範圍時,會直接顯示錯誤.

MariaDB [intdb]> SET SQL_MODE = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

MariaDB [intdb]> INSERT INTO num2 VALUES (200,200);
ERROR 1264 (22003): Out of range value for column 't1' at row 1

如需取消傳統模式如下.

MariaDB [intdb]> SET SQL_MODE = '';
Query OK, 0 rows affected (0.00 sec)

超過數值範圍的時候會發生什麼事情? 如果是傳統模式時無法輸入,非傳統模式時系統會幫忙設定到容許的最高值.

Primary Key (Auto_Increment)

如果當你的整數型態欄位是 Primary Key (Auto_Increment) 超過上限值會發生什麼事情.

MariaDB [intdb]> CREATE TABLE num3(t1 TINYINT(4) NOT NULL auto_increment , Num1 INT(11) NOT NULL , PRIMARY KEY (t1));
Query OK, 0 rows affected (0.007 sec)

當資料到達 126 ( TINYINT 有號數範圍為 -128~127) 這時候你的資料表就無法再新建任何資料了,

MariaDB [intdb]> SELECT MAX(t1) FROM num3;
+---------+
| MAX(t1) |
+---------+
|     126 |
+---------+
1 row in set (0.001 sec)

MariaDB [intdb]> INSERT INTO intdb.num3 (Num1) VALUES(1);
ERROR 167 (22003): Out of range value for column 't1' at row 1

MariaDB [intdb]> SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message                                     |
+-------+------+---------------------------------------------+
| Error |  167 | Out of range value for column 't1' at row 1 |
+-------+------+---------------------------------------------+
1 row in set (0.000 sec)

需把 TINYINT 改成 INT 或是範圍更大的整數型態.

MariaDB [intdb]> ALTER TABLE num3 CHANGE COLUMN t1 t1 int(11) NOT NULL AUTO_INCREMENT;
Query OK, 127 rows affected (0.019 sec)            
Records: 127  Duplicates: 0  Warnings: 0

MariaDB [intdb]> DESCRIBE num3;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| t1    | int(11) | NO   | PRI | NULL    | auto_increment |
| Num1  | int(11) | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
2 rows in set (0.002 sec)

修改之後就可以繼續新增資料了.

MariaDB [intdb]> INSERT INTO intdb.num3 (Num1) VALUES(1);
Query OK, 1 row affected (0.001 sec)