mysql 中的 int(m)与zerofill使用
阅读完该文档后,你将了解到:
- 整数类型的基本知识
- int(m)中m的含义 与 zerofill的使用
i整数类型的基本知识
整数类型 | 字节 | 有符号范围 | 无符号范围 |
---|---|---|---|
TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 1677215 |
INT/INTEGER | 4 | -2147483648 ~ 2147483647 | 0 ~ 4294967295 |
BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
int(m)中m的含义 与 zerofill的使用
int(m)的含义
NOTE:
INT(M) ZEROFILL,加上ZEROFILL后M才表现出不同,比如 INT(3) ZEROFILL,你插入到数据库里的是10,则实际插入为010,也就是在前面补充加了一个0.如果INT(3)和INT(10)不加ZEROFILL,则它们没有什么区别.M不是用来限制INT列内保存值的范围的.int(M)的最大值和最小值与UNSIGNED有关。
zerofill 的使用说明
NOTE:
zerofill 使用说明,例如 int(5)表示当数值宽度小于 5 位的时候在数字前面加’0’填满宽度,如果不显示指定宽度则默认为 int(11),zerofill默认为int(10)。注:当使用zerofill 时,默认会自动加unsigned(无符号)属性,使用unsigned属性后,数值范围是原值的2倍,例如,有符号为-128~+127,无符号为0~256。
演示
- 数据库默认int,生成int(11), 且存储显示、存储范围与int(m)的m无关。
mysql> create table tables (id int, tid int(10));
Query OK, 0 rows affected (0.02 sec)
mysql> show create table tables;
+--------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------+
| tables | CREATE TABLE `tables` (
`id` int(11) DEFAULT NULL,
`tid` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> insert into tables values(1,2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tables;
+------+------+
| id | tid |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.00 sec)
- 使用zerofill,int(m)的m不同,使得数据显示不同。
NOTE:
int zerofill 默认生成int(10)类型;
mysql> alter table tables add column t1 int(10) zerofill;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tables;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tables | CREATE TABLE `tables` (
`id` int(11) DEFAULT NULL,
`tid` int(10) DEFAULT NULL,
`t1` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table tables add column t2 int zerofill;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tables;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tables | CREATE TABLE `tables` (
`id` int(11) DEFAULT NULL,
`tid` int(10) DEFAULT NULL,
`t1` int(10) unsigned zerofill DEFAULT NULL,
`t2` int(10) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
NOTE:
m为显示宽度,如类型为int(5)的字段,存入数据5, 则数据库中显示0005;若存入 在存储范围内且比显示宽度大的数,如123456,则按正常显示为123456
mysql> alter table tables add column t3 int(5) zerofill;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table tables;
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tables | CREATE TABLE `tables` (
`id` int(11) DEFAULT NULL,
`tid` int(10) DEFAULT NULL,
`t1` int(10) unsigned zerofill DEFAULT NULL,
`t2` int(10) unsigned zerofill DEFAULT NULL,
`t3` int(5) unsigned zerofill DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from tables;
+------+------+------+------+------+
| id | tid | t1 | t2 | t3 |
+------+------+------+------+------+
| 1 | 2 | NULL | NULL | NULL |
+------+------+------+------+------+
1 row in set (0.00 sec)
mysql> insert into tables values(1,2,3,4,5)
-> ;
Query OK, 1 row affected (0.01 sec)
mysql> select * from tables;
+------+------+------------+------------+-------+
| id | tid | t1 | t2 | t3 |
+------+------+------------+------------+-------+
| 1 | 2 | NULL | NULL | NULL |
| 1 | 2 | 0000000003 | 0000000004 | 00005 |
+------+------+------------+------------+-------+
2 rows in set (0.00 sec)
mysql> insert into tables values(1,2,3,4,123456);
Query OK, 1 row affected (0.01 sec)
mysql> select * from tables;
+------+------+------------+------------+--------+
| id | tid | t1 | t2 | t3 |
+------+------+------------+------------+--------+
| 1 | 2 | NULL | NULL | NULL |
| 1 | 2 | 0000000003 | 0000000004 | 00005 |
| 1 | 2 | 0000000003 | 0000000004 | 123456 |
+------+------+------------+------------+--------+
3 rows in set (0.00 sec)