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