mysql数据类型--[整数类型]--tinyint类型

tinyint类型说明

类   型：tinyint
长   度：3
占用字节：1字节
有 符 号：-128~127
无 符 号：0~255
不指定长度时的长度为：4(实际长度还是3)

id     tinyint(M)   [UNSIGNED] [ZEROFILL] 
字段名  字段类型(长度)   [无符号]   [前导填充]

unsigned：
01：tinyint(M)后面加上unsigned后,就是无符号（tinyint的范围就是0~255）
02：tinyint(M)后面不加上unsigned,且不加zerofill参数,就是有符号（tinyint的范围就是-128~127）

zerofill：
01：进行前导零填充
02：tinyint(M)加上zerofile后，同时也会把unsigned参数也带上(tinyint范围0~255)

实践环境准备

-- 创建chenliang库,字符集为utf8
mysql> create database if not exists chenliang character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)

mysql> show databases like "chenliang";
+----------------------+
| Database (chenliang) |
+----------------------+
| chenliang            |
+----------------------+
1 row in set (0.00 sec)

-- 进入chenliang库，并查看是否成功进入到了chenliang库
mysql> use chenliang;
Database changed

mysql> select database();
+------------+
| database() |
+------------+
| chenliang  |
+------------+
1 row in set (0.00 sec)

测试1【加unsigned参数】

-- 创建test1测试表（这里指定了UNSIGNED，也就是无符号）
mysql> CREATE TABLE IF NOT EXISTS test1(
    -> id tinyint(3) UNSIGNED
    -> );
Query OK, 0 rows affected (0.06 sec)
^==表test1的id字段加了unsigned参数，那么id字段的范围就是0~255

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

-- 测试01：测试插入范围0~255的正整数和超过255的正整数
mysql> INSERT INTO test1 values(0);    #插入数值0,正常(没有超过范围)
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO test1 values(255);  #插入数值255,正常(没有超过范围)
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO test1 values(256);  #插入数值256,错误(超过了tinyint的范围)
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)

-- 测试02：测试插入范围-1~-128范围的负整数
mysql> INSERT INTO test1 values(-1);   #插入负整数-1,报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> INSERT INTO test1 values(-128); #插入负整数-128,报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> INSERT INTO test1 values(-129); #插入负整数-129,报错
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)

测试2【加zerofill参数】

-- 创建test2表（这里指定了zerofill,也就是前导零填充）
mysql> CREATE TABLE IF NOT EXISTS test2(
    -> id tinyint(3) ZEROFILL
    -> );
Query OK, 0 rows affected (0.07 sec)
^==表test2的id字段加了zerofill参数,那么id字段的范围为0~255，因为加上了zerofill参数后，会同时把unsigned参数也带上

mysql> desc test2;
+-------+------------------------------+------+-----+---------+-------+
| Field | Type                         | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+-------+
| id    | tinyint(3) unsigned zerofill | YES  |     | NULL    |       |
+-------+------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec) 

-- 测试01：测试插入范围0~255的正整数和超过255的整数
mysql> INSERT INTO test2 values(0);   #插入数值0,正常(没有超过范围)
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO test2 values(255); #插入数值255,正常(没有超过范围)
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO test2 values(256); #插入数值256,错误(超过范围)
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)

-- 测试02：测试插入-1~-128范围的负整数
mysql> INSERT INTO test2 values(-1);   #插入负整数-1,报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> INSERT INTO test2 values(-128); #插入负整数-128,报错
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> INSERT INTO test2 values(-129); #插入负整数-129,报错
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)

测试3【不加zerofill和unsigned参数】

-- 创建test3表（不加unsigned和zerofill）
mysql> CREATE TABLE test3(
    -> id tinyint(3)
    -> );
Query OK, 0 rows affected (0.06 sec)
^==表test3的id字段没有加unsigned和zerofill参数，那么id字段的范围为-128~127

mysql> desc test3;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | tinyint(3) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)

-- 测试01：测试插入0~127的正整数和超过127的正整数
mysql> INSERT INTO test3 values(0);
Query OK, 1 row affected (0.05 sec)

mysql> insert into test3 values(127);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO test3 values(128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> select * from test3;
+------+
| id   |
+------+
|    0 |
|  127 |
+------+
2 rows in set (0.00 sec)

-- 测试02：测试插入-1~-128的负整和小于-128的负整数
mysql> INSERT INTO test3 values(-1);
Query OK, 1 row affected (0.10 sec)

mysql> INSERT INTO test3 values(-128);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO test3 values(-129);
ERROR 1264 (22003): Out of range value for column 'id' at row 1

mysql> select * from test3;
+------+
| id   |
+------+
|     0|
|   127|
|    -1|
|  -128|
+------+
4 rows in set (0.00 sec)

总结

格式：

id       tinyint(M)      [UNSIGNED]  [ZEROFILL]

字段名   字段类型(长度)      [无符号]    [前导填充]

unsigned：

01：tinyint(M)后面加上unsigned参数后,就是无符号（tinyint的范围就是0~255）

02：tinyint(M)后面不加上unsigned参数，且不加zerofill参数,就是有符号（tinyint的范围就是-128~127）

zerofill：

01：进行前导零填充（插入数值1，表中显示的是001，因为tinyint的长度为3）

02：tinyint(M)加上zerofile后，同时也会把unsigned参数也带上(tinyint范围0~255)

转载于:https://www.cnblogs.com/chenliangc/articles/11107091.html