zerofill就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。以下几个例子分别描述了填充前后的区别。
mysql> create table t1(id1 int zerofill,id2 int(5) zerofill);
Query OK, 0 rows affected (0.11 sec)
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.11 sec)
Query OK, 1 row affected (0.11 sec)
mysql> select * from t1;
+------------+-------+
| id1 | id2 |
+------------+-------+
| 0000000001 | 00001 |
+------------+-------+
1 row in set (0.02 sec)
+------------+-------+
| id1 | id2 |
+------------+-------+
| 0000000001 | 00001 |
+------------+-------+
1 row in set (0.02 sec)
mysql> insert into t1 values(1,1111111);
Query OK, 1 row affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
mysql> select * from t1;
+------------+---------+
| id1 | id2 |
+------------+---------+
| 0000000001 | 00001 |
| 0000000001 | 1111111 |
+------------+---------+
2 rows in set (0.00 sec)
+------------+---------+
| id1 | id2 |
+------------+---------+
| 0000000001 | 00001 |
| 0000000001 | 1111111 |
+------------+---------+
2 rows in set (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.05 sec)
重新创建表t1,讨论精度和标度对数值插入的影响;
mysql> create table t1( id1 float(5,2) default null,id2 double(5,2) default null
,id3 decimal(5,2) default null);
Query OK, 0 rows affected (0.11 sec)
mysql> create table t1( id1 float(5,2) default null,id2 double(5,2) default null
,id3 decimal(5,2) default null);
Query OK, 0 rows affected (0.11 sec)
mysql> insert into t1 values(1.23,1.23,1.23);
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.06 sec)
mysql> insert into t1 values(1.234,1.234,1.23);
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.05 sec)
mysql> select * from t1;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.03 sec)
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
2 rows in set (0.03 sec)
mysql> insert into t1 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.06 sec)
Query OK, 1 row affected, 1 warning (0.06 sec)
mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
3 rows in set (0.00 sec)
+------+------+------+
| id1 | id2 | id3 |
+------+------+------+
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
| 1.23 | 1.23 | 1.23 |
+------+------+------+
3 rows in set (0.00 sec)
mysql> alter table t1 modify id1 float;
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 3 rows affected (0.19 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify id2 double;
Query OK, 3 rows affected (0.23 sec)
Records: 3 Duplicates: 0 Warnings: 0
Query OK, 3 rows affected (0.23 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> alter table t1 modify id3 decimal;
Query OK, 3 rows affected, 3 warnings (0.23 sec)
Records: 3 Duplicates: 0 Warnings: 3
Query OK, 3 rows affected, 3 warnings (0.23 sec)
Records: 3 Duplicates: 0 Warnings: 3
mysql> desc t1;
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id1 | float | YES | | NULL | |
| id2 | double | YES | | NULL | |
| id3 | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.09 sec)
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| id1 | float | YES | | NULL | |
| id2 | double | YES | | NULL | |
| id3 | decimal(10,0) | YES | | NULL | |
+-------+---------------+------+-----+---------+-------+
3 rows in set (0.09 sec)
mysql> insert into t1 values(1.234,1.234,1.234);
Query OK, 1 row affected, 1 warning (0.05 sec)
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> show warnings;
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
+-------+------+------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------+
| Note | 1265 | Data truncated for column 'id3' at row 1 |
+-------+------+------------------------------------------+
1 row in set (0.00 sec)
mysql> select * from t1;
+-------+-------+------+
| id1 | id2 | id3 |
+-------+-------+------+
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.234 | 1.234 | 1 |
+-------+-------+------+
4 rows in set (0.00 sec)
+-------+-------+------+
| id1 | id2 | id3 |
+-------+-------+------+
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.23 | 1.23 | 1 |
| 1.234 | 1.234 | 1 |
+-------+-------+------+
4 rows in set (0.00 sec)
mysql>
上面这个例子验证了上面提到的浮点数如果不写精度和标度，则会按照实际精度值显示，如果有精度和标度，则会自动将四舍五入后的结果插入，系统不会报错；定点数如果写精度和标度，则按照默认值decimal(10,0)来进行操作，并且如果数据超越了精度和标度值，系统则会报错。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25281640/viewspace-684480/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
请登录后发表评论
登录
全部评论
