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) , 28 可以儲存 256 種數值.
    數值可以從 -128 (27) ~ 127(27-1)
  • 無號數範圍 (Unsigned)
    數值可以從 0 (20-1) ~255(28-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) , 28 可以儲存 256 種數值.
    數值可以從 -128 (27) ~ 127(27-1)
  • 無號數範圍 (Unsigned)
    數值可以從 0 (20-1) ~255(28-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)

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

沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料