作用

若不指定显示宽度，则默认为该类型的最大位数(int=>11)
作用相同于printf宽度控制，当数值位数小于指定值时，默认填充空格，大于指定值时，也不会截断！
该显示宽度是可通过结果集(Result Set)中的元数据(Metadata)获取，具体采不采用取决于应用程序!
宽度和zerofill对数据存储(存储形式，占用空间)没有任何影响
当列定义指定zerofill时，若该值小于指定宽度，select查询输出会填充相应个数的0

测试

指定宽度和zerofill

create table t(num int(5) zerofill);

MariaDB [test]> desc t;
+-------+--------------------------+------+-----+---------+-------+
| Field | Type                     | Null | Key | Default | Extra |
+-------+--------------------------+------+-----+---------+-------+
| num   | int(5) unsigned zerofill | YES  |     | NULL    |       |
+-------+--------------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [test]> insert into t values(1);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from t;
+-------+
| num   |
+-------+
| 00001 |
+-------+

不指定zerofill

MariaDB [test]> create table t1(num int(5));
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

MariaDB [test]> select * from t1;
+------+
| num  |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

不指定宽度，指定zerofill

MariaDB [test]> create table t2(num int zerofill);
Query OK, 0 rows affected (0.02 sec)

MariaDB [test]> insert into t2 values(1);
Query OK, 1 row affected (0.02 sec)

MariaDB [test]> select * from t2;
+------------+
| num        |
+------------+
| 0000000001 |
+------------+
1 row in set (0.00 sec)

细节

对于int来说，取值范围-2^(32-1) to 0 to 2^(32-1)-1 = -2147483648 to 0 to 2147483647，最大显示宽度为11
对于unsigned int，0~4294967295，最大显示宽度为10
当指定zerofill时，mysql会自动追加unsigned属性，！

+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| num   | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+

参考

留言

▲