0 |
| 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)
1.2.4 不加unsigned和zerofill参数
##创建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(128);
ERROR 1264 (22003): Out of range value for column 'id' at row 1
mysql> select * from test3;
+------+
| id |
+------+
| 0 |
+------+
1 row in set (0.01 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 |
| -1 |
| -128 |
+------+
3 rows in set (0.00 sec)
1.3 tinyint总结
格式:
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)