1.1 smallint说明
id smallint(M) [UNSIGNED] [ZEROFILL]
字段名 字段类型(长度) [无符号] [前导填充]
unsigned:
01:smallint(M)后面加上unsigned,就是无符号(smallint的范围就是0~65535)
02:smallint(M)后面不加上unsigned,并且不加ZEROFILL参数,就是有符号(smallint的范围就是
-32768~32767)
zerofill:
01:进行前导零填充
02:smallint(M)加上zerofile后,同时也会把unsigned参数也带上(smallint范围0~65535)
1.2 smallint实践
1.2.1 环境准备
##创建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)
1.2.2 加unsigned参数
##创建test1测试表(这里指定了UNSIGNED,也就是无符号)
mysql> CREATE TABLE IF NOT EXISTS test1(
->id smallint(5) UNSIGNED
->);
Query OK, 0 rows affected (0.03 sec)
^==test1表的id字段指定了unsigned参数,那么id字段的范围就是0~65535
mysql> desc test1;
+-------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------------------+------+-----+---------+-------+
| id | smallint(5) unsigned | YES | | NULL | |
+-------+----------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
**测试01:测试插入范围0~65535的整数以及超过65535的整数
mysql> insert into test1 values(0); #插入整数0,正确(没有超过范围0~65535)
Query OK, 1 row affected (0.03 sec)
mysql> insert into test1 values(65535); #插入整数65535,正确(没有超过范围0~65535)
Query OK, 1 row affected (0.03 sec)
mysql> insert into test1 values(65536); #插入整数65536,错误(超过范围0~65535)
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> select * from test1;
+-------+
| id |
+-------+
| 0 |
| 65535 |
+-------+
2 rows in set (0.00 sec)
**测试02:测试-1~-32768范围的负整数,以及小于-32768的负整数
mysql> insert into test1 values(-1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入负整数-1,报错(因为建表时,id字段加了unsigned参数,id字段的范围为0~65535)
mysql> INSERT INTO test1 values(-128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入负整数-128,报错(因为建表时,id字段加了unsigned参数,id字段的范围为0~65535)
mysql> insert into test1 values(-32769);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入负整数-32769,报错(因为建表时,id字段加了unsigned参数,id字段的范围为0~65535)
mysql> select * from test1;
+-------+
| id |
+-------+
| 0 |
| 65535 |
+-------+
2 rows in set (0.01 sec)
1.2.3 加zerofill参数
##创建test2表,(这里指定了zerofill,也就是前导零填充)
mysql> create table if not exists test2(
-> id smallint(5) zerofill
-> );
Query OK, 0 rows affected (0.12 sec)
^==test2表的id字段指定了zerofill参数,它会把unsigned参数也带上,那么id字段的范
围就是0~65535
mysql> desc test2;
+-------+-------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------------------------+------+-----+---------+-------+
| id | smallint(5) unsigned zerofill | YES | | NULL | |
+-------+-------------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
**测试01:测试插入范围0~65535的整数和超过65535的整数
mysql> insert into test2 values(0); #插入整数0,在0~65535范围内,正确
Query OK, 1 row affected (0.04 sec)
mysql> insert into test2 values(65535); #插入整数65535,在0~65535范围内,正确
Query OK, 1 row affected (0.05 sec)
mysql> insert into test2 values(65536); #插入整数65536,不在0~65535范围内,错误
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> select * from test2;
+-------+
| id |
+-------+
| 00000 |
| 65535 |
+-------+
2 rows in set (0.00 sec)
**测试02:测试-1~-32768范围的负整数,以及小于-32768的负整数
mysql> INSERT INTO test2 values(-1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入负整数-1,错误(因为在id字段加了zerofill参数,它会把unsigned也带上,所以
id字段的范围为0~65535)
mysql> INSERT INTO test2 values(-128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入负整数-128,错误(因为在id字段加了zerofill参数,它会把unsigned也带上,所
以id字段的范围为0~65535)
mysql> insert into test2 values(-32769);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入负整数-32769,错误(因为在id字段加了zerofill参数,它会把unsigned也带上,
所以id字段的范围为0~65535,并且有符号的范围也只是-32768~32767)
mysql> select * from test2;
+-------+
| id |
+-------+
| 00000 |
| 65535 |
+-------+
2 rows in set (0.00 sec)
1.2.4 不加unsigned和zerofill参数
##创建test3表(不加unsigned和zerofill)
mysql> create table if not exists test3(
-> id smallint(5)
-> );
Query OK, 0 rows affected (0.08 sec)
^==test3表的id字段没指定unsigned和zerofill参数,那么id字段的范围就
是 -32768~32767
mysql> desc test3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | smallint(5) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.01 sec)
**测试01:测试插入整数0~32767和超过32767的整数
mysql> insert into test3 values(0);
Query OK, 1 row affected (0.00 sec)
^==插入整数0,正确(在范围-32768~32767范围内)
mysql> insert into test3 values(32767);
Query OK, 1 row affected (0.04 sec)
^==插入整数32767,正确(在范围-32768~32767范围内)
mysql> insert into test3 values(32768);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入整数32768,错误(不在范围-32768~32767范围内)
mysql> select * from test3;
+-------+
| id |
+-------+
| 0 |
| 32767 |
+-------+
2 rows in set (0.00 sec)
**测试02:测试插入负数-1~-32768和小于-32768的负数
mysql> INSERT INTO test3 values(-1);
Query OK, 1 row affected (0.10 sec)
^==插入负整数-1,正确(在范围-32768~32767范围内)
mysql> INSERT INTO test3 values(-128);
Query OK, 1 row affected (0.02 sec)
^==插入负整数-128,正确(在范围-32768~32767范围内)
mysql> insert into test3 values(-32769);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入负整数-32769,错误(不在范围-32768~32767范围内)
mysql> select * from test3;
+-------+
| id |
+-------+
| 0 |
| 32767 |
| -1 |
| -128 |
+-------+
4 rows in set (0.00 sec)
1.3 smallint总结
格式:
id smallint(M) [UNSIGNED] [ZEROFILL]
字段名 字段类型(长度) [无符号] [前导填充]
unsigned:
01:smallint(M)后面加上unsigned后,就是无符号(smallint的范围就是0~65535)
02:smallint(M)后面不加上unsigned,并且不加zerofill参数,就是有符号(smallint的范围就是
-32768~32767)
zerofill:
01:进行前导零填充(插入数值1,表中显示的是00001,因为smallint的长度为5)
02:smallint(M)加上zerofile后,同时也会把unsigned参数也带上(smallint的范围为0~65535)