ITPub博客

首页 > Linux操作系统 > Linux操作系统 > [MySql学习]数值数型

[MySql学习]数值数型

原创 Linux操作系统 作者:hquxiaoqi 时间:2011-01-23 09:21:29 0 删除 编辑
zerofill就是用“0”填充的意思,也就是在数字位数不够的空间用字符“0”填满。以下几个例子分别描述了填充前后的区别。
mysql> create table t1(id1 int zerofill,id2 int(5) zerofill);
Query OK, 0 rows affected (0.11 sec)
 
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.11 sec)
 
mysql> select * from t1;
+------------+-------+
| 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)
 
mysql> select * from t1;
+------------+---------+
| 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)
 
重新创建表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> insert into t1 values(1.23,1.23,1.23);
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)
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)
mysql> insert into t1 values(1.234,1.234,1.234);
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)
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)
mysql> alter table t1 modify id1 float;
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
 
mysql> alter table t1 modify id3 decimal;
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)
mysql> insert into t1 values(1.234,1.234,1.234);
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)
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)
mysql>
上面这个例子验证了上面提到的浮点数如果不写精度和标度,则会按照实际精度值显示,如果有精度和标度,则会自动将四舍五入后的结果插入,系统不会报错;定点数如果写精度和标度,则按照默认值decimal(10,0)来进行操作,并且如果数据超越了精度和标度值,系统则会报错。
 
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25281640/viewspace-684480/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2011-01-22

  • 博文量
    22
  • 访问量
    82330