1.1 int说明
id int(M) [UNSIGNED] [ZEROFILL]
字段名 字段类型(长度) [无符号] [前导填充]
unsigned:
01:int(M)后面加上unsigned,就是无符号(int的范围就是0~4294967295)
02:int(M)后面不加上unsigned,并且不加ZEROFILL参数,就是有符号(int的范围就是
-2147483648~2147483647)
zerofill:
01:进行前导零填充
02:int(M)加上zerofile后,同时也会把unsigned参数也带上(int范围0~4294967295)
1.2 int实践
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 int(10) unsigned
-> );
Query OK, 0 rows affected (0.02 sec)
^==表test1的id字段加了unsigned参数,那么id字段的范围就是0~4294967295
mysql> desc test1;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | YES | | NULL | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
**测试01:测试插入范围0~4294967295的整数以及超过4294967295的整数
mysql> insert into test1 values(0);
Query OK, 1 row affected (0.05 sec)
^==插入整数0,正确(没有超过id字段的范围0~4294967295)
mysql> insert into test1 values(4294967295);
Query OK, 1 row affected (0.01 sec)
^==插入数值4294967295,正确(没有超过id字段的范围0~4294967295)
mysql> insert into test1 values(4294967296);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入数值4294967296,错误(超过id字段的范围0~4294967295)
mysql> select * from test1;
+------------+
| id |
+------------+
| 0 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)
**测试02:测试-1~-2147483648范围的数据是否能够正常插入
mysql> insert into test1 values(-1);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入负数报错(因为建表时,id字段加了unsigned参数,id字段的范围为0~4294967295)
mysql> insert into test1 values(-2147483648);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
^==插入负数报错(因为建表时,在id字段加了unsigned参数,id字段的范围为0~4294967295)
mysql> select * from test1;
+------------+
| id |
+------------+
| 0 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)
1.2.3 加zerofill参数
##创建test2表,(这里指定了zerofill,也就是前导零填充)
mysql> create table if not exists test2(
-> id int(10) zerofill
-> );
Query OK, 0 rows affected (0.03 sec)
^==表test2的id字段加了zerofill参数,它会把unsigned参数也带上,所以id字段的范围就是0~4294967295
mysql> desc test2;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id | int(10) unsigned zerofill | YES | | NULL | |
+-------+---------------------------+------+-----+---------+-------+
1 row in set (0.03 sec)
**测试01:测试插入范围0~4294967295的整数和超过4294967295的整数
mysql> insert into test2 values(0);
Query OK, 1 row affected (0.04 sec)
^==插入整数0,正确(没有超过id字段的范围0~4294967295)
mysql> insert into test2 values(4294967295);
Query OK, 1 row affected (0.02 sec)
^==插入整数4294967295,正确(没有超过id字段的范围0~4294967