mysql中整數數據類型tinyint詳解

#### 格式
id       tinyint(M)                 [unsigned]     [zerofill]
字段名  數據類型(顯示長度,不指定指定)     無符號          無符號且前導零填充

#### 關於tinyint最大數值是怎樣得來的
tinyint占用1字節,1字節占用8位，經過換算(2的8次方減1)就是255;

#### 關於加上unsigned後的說明
加上unsigned屬性後就是無符號(范圍是0~255的整數,因為是整數,不會有符號"-",所以就是無符號)

#### 關於加上zerofill後的說明
zerofill屬性會把unsigned屬性也給帶上,這樣就是無符號（范圍是0~255，顯示長度就是3），同時還會
進行前導零填充（沒有達到顯示長度的數值，例如：你插入1，顯示的是001）。

#### 不加unsigned和zerofill的說明
字段後面不加上這兩個屬性中的任何一個,就表示是有符號(范圍是-128~127,因為有符號"-",所有是有符號)。

#### 數據庫版本和默認的存儲引擎
mysql> select @@version,@@default_storage_engine;
+------------+--------------------------+
| @@version  | @@default_storage_engine |
+------------+--------------------------+
| 5.7.28-log | InnoDB                   |
+------------+--------------------------+
1 row in set (0.00 sec)
 
 
#### 創建chenliang庫
mysql> create database if not exists chenliang;
Query OK, 1 row affected (0.03 sec)
 
mysql> show databases like "chenliang";
+----------------------+
| Database (chenliang) |
+----------------------+
| chenliang            |
+----------------------+
1 row in set (0.03 sec)
 
 
#### 進入chenliang庫,並查看是否成功進入到庫下面
mysql> use chenliang;
Database changed
 
mysql> select database();
+------------+
| database() |
+------------+
| chenliang  |
+------------+
1 row in set (0.01 sec)


#### 查看事務是否自動提交
mysql> select @@global.autocommit;
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

#### 設置會話模式下的sql_mode中包含strict_trans_tables
mysql> set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)


#### 創建test1測試表（這裡指定瞭UNSIGNED，也就是無符號）
mysql> CREATE TABLE IF NOT EXISTS test1(
    ->  id tinyint UNSIGNED
    -> )engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.06 sec)
 　 
 　 ## id字段類型是tinyint unsigned,范圍就是0~255,其長度是3;因為255的長度是3;


#### 查看test1表的表結構
mysql> desc test1;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.03 sec)


#### 測試插入范圍0~255范圍整數和不在該范圍內的整數
mysql> insert into test1 values(-1);   # 插入數值-1,錯誤(不在0~255范圍內)
ERROR 1264 (22003): Out of range value for column 'id' at row 1
 
mysql> insert into test1 values(0);    # 插入數值0,正常(在0~255范圍內)
Query OK, 1 row affected (0.06 sec)
 
mysql> insert into test1 values(255);  # 插入數值255,正常(在0~255范圍內)
Query OK, 1 row affected (0.05 sec)
 
mysql> insert into test1 values(256);  # 插入數值256,錯誤(不在0~255范圍內)
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> select * from test1;
+------+
| id   |
+------+
|    0 |
|  255 |
+------+
2 rows in set (0.00 sec)

#### 設置會話模式下sql_mode中不包含strit_trans_tables變量
mysql> set session sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)



#### 創建test11表（這裡指定瞭UNSIGNED，也就是無符號）
mysql> create table if not exists test11(
    -> id tinyint unsigned
    -> )engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
 　## id字段類型是tinyint unsigned,范圍就是0~255,其長度是3;因為255的長度是3;



#### 查看test11表的表結構
mysql> desc test11;
+-------+---------------------+------+-----+---------+-------+
| Field | Type                | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned | YES  |     | NULL    |       |
+-------+---------------------+------+-----+---------+-------+
1 row in set (0.00 sec)



#### 測試插入范圍0~255范圍整數和不在該范圍內的整數
mysql> insert into test11(id) values(-1);
Query OK, 1 row affected, 1 warning (0.00 sec)
　　 ## 不在范圍內,插入沒有報錯(因為sql_mode中沒有開啟嚴格模式)
　　 ## 插入到表中的數據不是-1,而是0
 
mysql> insert into test11(id) values(0);
Query OK, 1 row affected (0.01 sec)
    ## 沒報錯,因為在范圍內,插入到表中的也是0
 
mysql> insert into test11(id) values(255);
Query OK, 1 row affected (0.01 sec)
    ## 沒報錯,因為在范圍內,插入到表中的也是255
 
mysql> insert into test11(id) values(256);
Query OK, 1 row affected, 1 warning (0.00 sec)
　　 ## 不在范圍內,插入沒有報錯(因為sql_mode中沒有開啟嚴格模式)
　　 ## 插入到表中的數據不是256,而是255
  
mysql> select * from test11;
+------+
| id   |
+------+
|    0 |
|    0 |
|  255 |
|  255 |
+------+
4 rows in set (0.00 sec)

## 設置會話模式下的sql_mode中包含strict_trans_tables
mysql> set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
 
## 創建test2測試表（這裡指定瞭zerofill,會前導零填充,同時還會帶上unsigned）
mysql> CREATE TABLE IF NOT EXISTS test2(
    -> id tinyint zerofill
    -> )engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.01 sec)
     ## id字段類型是tinyint zerofill,范圍就是0~255,其長度是3;因為255的長度是3;

 ## 查看test2表的表結構
mysql> desc test2;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned zerofill | YES  |     | NULL    |       |
+-------+------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
 
## 測試插入范圍0~255范圍整數和不在該范圍內的整數
mysql> insert into test2 values(-1);   # 插入數值-1,錯誤(不在0~255范圍內)
ERROR 1264 (22003): Out of range value for column 'id' at row 1
 
mysql> insert into test2 values(0);    # 插入數值0,正常(在0~255范圍內)
Query OK, 1 row affected (0.06 sec)
 
mysql> insert into test2 values(255);  # 插入數值255,正常(在0~255范圍內)
Query OK, 1 row affected (0.05 sec)
 
mysql> insert into test2 values(256);  # 插入數值256,錯誤(不在0~255范圍內)
ERROR 1264 (22003): Out of range value for column 'id' at row 1
 
mysql> select * from test2;
+------+
| id   |
+------+
|  000 |
|  255 |
+------+
2 rows in set (0.00 sec)

## 設置會話模式下sql_mode中不包含strit_trans_tables變量
mysql> set session sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

## 創建test22表（這裡指定瞭zerofill,會前導零填充,同時還會帶上unsigned）
mysql> create table if not exists test22(
    -> id tinyint zerofill
    -> )engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
 　## id字段類型是tinyint unsigned,范圍就是0~255,其長度是3;因為255的長度是3;
 
## 查看test22表的表結構
mysql> desc test22;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned zerofill | YES  |     | NULL    |       |
+-------+------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
 
## 測試插入范圍0~255范圍整數和不在該范圍內的整數
mysql> insert into test22(id) values(-1);
Query OK, 1 row affected, 1 warning (0.00 sec)
　　## 不在范圍,插入沒報錯(因為sql_mode中沒有開啟嚴格模式);
　　## 但到表中的不是-1,而是0,但因為有zerofill參數,所以顯示時會前導零序填充;
 
mysql> insert into test22(id) values(0);
Query OK, 1 row affected (0.01 sec)
　　## 沒報錯,因為在范圍內,插入到表中的也是0
 
mysql> insert into test22(id) values(255);
Query OK, 1 row affected (0.01 sec)
　　## 沒報錯,因為在范圍內,插入到表中的也是255
 
mysql> insert into test22(id) values(256); 
Query OK, 1 row affected, 1 warning (0.00 sec)
　　## 不在范圍,插入沒報錯（sql_mode中沒有開啟嚴格模式）
　　## 但到表中的數據不是256,而是255
 
mysql> select * from test22;
+------+
| id   |
+------+
|  000 |
|  000 |
|  255 |
|  255 |
+------+
4 rows in set (0.00 sec)

## 設置會話模式下的sql_mode中包含strict_trans_tables
mysql> set session sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
 
## 創建test3表（不加unsigned和zerofill）
mysql> CREATE TABLE test3(
    -> id tinyint
    -> )engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.06 sec)
 　　## id字段的類型為tinyint,其范圍是-128至127,其顯示長度是4,因為要顯示符號("-")
 
## 查看test3表的表結構
mysql> desc test3;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)
 
## 測試插入-128~127范圍的整數和不在該范圍內的整數
mysql> insert into test3(id) values(-129);  # 插入數值-129,錯誤,不在范圍內
ERROR 1264 (22003): Out of range value for column 'id' at row 1
 
mysql> insert into test3(id) values(-128);  # 插入數值-128,正確,在范圍內
Query OK, 1 row affected (0.00 sec)

mysql> insert into test3(id) values(127);   # 插入數值127,正確,在范圍內
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into test3(id) values(128);   # 插入數值128,錯誤,不在范圍內
ERROR 1264 (22003): Out of range value for column 'id' at row 1
 
mysql> select * from test3;
+------+
| id   |
+------+
| -128 |
|  127 |
+------+
2 rows in set (0.00 sec)

## 設置會話模式下sql_mode中不包含strit_trans_tables變量
mysql> set session sql_mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";
Query OK, 0 rows affected, 1 warning (0.00 sec)
 
mysql> select @@sql_mode\G
*************************** 1. row ***************************
@@sql_mode: NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)
 
## 創建test33表（不加unsigned和zerofill）
mysql> CREATE TABLE test33(
    -> id tinyint
    -> )engine=innodb character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.00 sec)
　　## id字段的類型為tinyint,其范圍是-128至127,其顯示長度是4,因為要顯示符號("-")
 
## 查看test33表的表結構
mysql> desc test33;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(4) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
 

## 測試插入-128~127范圍的整數和不在該范圍內的整數
mysql> insert into test33(id) values(-129);
Query OK, 1 row affected, 1 warning (0.00 sec)
　　## 不在范圍內,插入未報錯,因為sql_mode中沒有開啟嚴格模式
　　## 插入到表中的不是-129,而是-128;
 
mysql> insert into test33(id) values(-128);
Query OK, 1 row affected (0.01 sec)
　　## 在范圍內,插入不報錯,插入的是多少就是多少
 
mysql> insert into test33(id) values(127);
Query OK, 1 row affected (0.00 sec)
　　## 在范圍內,插入不報錯,插入的是多少就是多少
 
mysql> insert into test33(id) values(128);
Query OK, 1 row affected, 1 warning (0.01 sec)
　　## 不在范圍內,插入未報錯,因為sql_mode中沒有開啟嚴格模式
　　## 插入到表中的不是128,而是127;
 
mysql> select * from test33;
+------+
| id   |
+------+
| -128 |
| -128 |
|  127 |
|  127 |
+------+
4 rows in set (0.00 sec)