结论
从下文的测试结果中得出结论
- 类型长度对于字段的取值范围毫无影响;
- 在设置ZEROFILL的情况下,类型长度决定显示结果值的长度;
- 在设置ZEROFILL的情况下,显示时,宽度不足左边补0,宽度超过限制按实际值显示;
举例说明:
int(11)和int(20)在取值范围上并无不同,都是-2147483648 ~ 2147483647,
在设置ZEROFILL情况下的显示方式不同,相当于打印结果时用printf("%011d\n",val)还是printf("%020d\n",val);
字段的取值范围和宽度
字段类型对应的取值范围:
类型 | 字节 | 有符号取值范围 | 无符号取值范围 |
---|---|---|---|
TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
INT | 4 | -2147483648 ~ 2147483647 | 0 ~ 4294967295 |
BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
类型默认的显示宽度:
类型 | 宽度 |
---|---|
TINYINT | 4 |
SMALLINT | 6 |
MEDIUMINT | 9 |
INT | 11 |
BIGINT | 20 |
MEDIUMINT类型用肉眼数, 按负值的宽度算,应该是8,但实际默认值是9。所以说默认值可能是按:“无符号最大值的宽度”+1 来计算的。
事实上设置了ZEROFILL也就不能插入负数了。详情看下面的验证结果。
验证及结果
创建表语句
CREATE TABLE `testtype` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`tint` TINYINT(2) ZEROFILL NOT NULL DEFAULT '0',
`sint` TINYINT(3) ZEROFILL NOT NULL DEFAULT '0',
`mint` TINYINT(4) ZEROFILL NOT NULL DEFAULT '0',
`bint` TINYINT(5) ZEROFILL NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2;
验证结果
mysql> insert into testtype values(1,250,250,250,250);
Query OK, 1 row affected (0.00 sec)
mysql> select * from testtype;
+----+------+------+------+-------+
| id | tint | sint | mint | bint |
+----+------+------+------+-------+
| 1 | 250 | 250 | 0250 | 00250 |
+----+------+------+------+-------+
1 row in set (0.00 sec)
mysql> insert into testtype values(2,-125,-125,-125,-125);
Query OK, 1 row affected, 4 warnings (0.00 sec)
Warning (Code 1264): Out of range value for column 'tint' at row 1
Warning (Code 1264): Out of range value for column 'sint' at row 1
Warning (Code 1264): Out of range value for column 'mint' at row 1
Warning (Code 1264): Out of range value for column 'bint' at row 1
mysql> select * from testtype;
+----+------+------+------+-------+
| id | tint | sint | mint | bint |
+----+------+------+------+-------+
| 1 | 250 | 250 | 0250 | 00250 |
| 2 | 00 | 000 | 0000 | 00000 |
+----+------+------+------+-------+
2 rows in set (0.01 sec)
mysql> insert into testtype values(3,257,257,257,257);
Query OK, 1 row affected, 4 warnings (0.00 sec)
Warning (Code 1264): Out of range value for column 'tint' at row 1
Warning (Code 1264): Out of range value for column 'sint' at row 1
Warning (Code 1264): Out of range value for column 'mint' at row 1
Warning (Code 1264): Out of range value for column 'bint' at row 1
mysql> select * from testtype;
+----+------+------+------+-------+
| id | tint | sint | mint | bint |
+----+------+------+------+-------+
| 1 | 250 | 250 | 0250 | 00250 |
| 2 | 00 | 000 | 0000 | 00000 |
| 3 | 255 | 255 | 0255 | 00255 |
+----+------+------+------+-------+
3 rows in set (0.00 sec)
参考网址
https://www.jianshu.com/p/febf5bd2bd37
https://blog.csdn.net/da_guo_li/article/details/79011718
转载请注明来源:https://www.cnblogs.com/bugutian/