一些数字的属性

M, 宽度， 在0填充时有效
unsigned 无符号型， 也就是非负
zerofill , 0填充时有效， 默认非负， 用于对齐用
not null default '100', 设置字段默认值

 

float(m,d)
m 总位数
d 小数位

 

 

mysql> create table i(id int, scor int zerofill);
Query OK, 0 rows affected (0.04 sec)

mysql> desc i;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(11)                   | YES  |     | NULL    |       |
| scor  | int(10) unsigned zerofill | YES  |     | NULL    |       |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


mysql> insert into i values(1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from i;
+------+------------+
| id   | scor       |
+------+------------+
|    1 | 0000000001 |
+------+------------+
1 row in set (0.00 sec)

mysql>

 

mysql> alter table i add end int not null default 100;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc i;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(11)                   | YES  |     | NULL    |       |
| scor  | int(10) unsigned zerofill | YES  |     | NULL    |       |
| end   | int(11)                   | NO   |     | 100     |       |
+-------+---------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> select * from i;
+------+------------+-----+
| id   | scor       | end |
+------+------------+-----+
|    1 | 0000000001 | 100 |
+------+------------+-----+
1 row in set (0.00 sec)

mysql>

 

mysql> desc i;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type                      | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id    | int(11)                   | YES  |     | NULL    |       |
| scor  | int(10) unsigned zerofill | YES  |     | NULL    |       |
| end   | int(11)                   | NO   |     | 100     |       |
+-------+---------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table i add f float(3,1) not null default 222.1;
ERROR 1067 (42000): Invalid default value for 'f'
mysql> alter table i add f float(3,1) not null default 22.1;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql>