MySQL数据类型(float)的注意事项
摘要:
今天左哥问起⼀个float浮点数类型的问题,这个类型⽤的不多,所以也不太了解,现在打算测试下。
知识点:
float:浮点数,单精度,占4字节。
测试
root@localhost : test 05:49:32>create table fl(id int,fl float);
Query OK, 0 rows affected (0.05 sec)
root@localhost : test 05:49:40>insert into fl values(1,123456);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:49:51>insert into fl values(2,123.456);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:49:57>insert into fl values(3,1234.567);#7位了
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:50:13>insert into fl values(4,1234567);#7位了
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:50:26>select*from fl;
+------+-------------+
| id | fl |
+------+-------------+
|1|123456|
|2|123.456|
|3|1234.57|
|4|1.23457e+06|
+------+-------------+
4 rows in set (0.00 sec)
从上⾯看出:默认的float类型都只能存6个数字(包括⼩数点前后的位数),整数超过6位就被科学计数表⽰(id=4),⼩数位超出则需要四舍五⼊。那指定float(m)的精度呢?
root@localhost : test 05:59:08>create table fl(id int,fl float(20));
Query OK, 0 rows affected (0.04 sec)
root@localhost : test 05:59:28>show create table fl\G;
***************************1. row ***************************
Table: fl
Create Table: CREATE TABLE `fl` (
`id` int(11) DEFAULT NULL,
`fl` float DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
root@localhost : test 05:59:35>insert into fl values(1,123456);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:59:44>insert into fl values(2,123.456);
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:59:46>insert into fl values(3,1234.567);#7位了
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:59:50>insert into fl values(4,1234567);#7位了
mysql创建表数据类型
Query OK, 1 row affected (0.00 sec)
root@localhost : test 05:59:53>select*from fl;
+------+-------------+
| id | fl |
+------+-------------+
|1|123456|
|2|123.456|
|3|1234.57|
|4|1.23457e+06|
+------+-------------+
4 rows in set (0.01 sec)
从上⾯看出:和默认的float⼀样。所以:默认float和float(m)⼀样,m<=24;都是默认float类型,都只能存6个数字(包括⼩数点前后的位数),整数超过6位就被科学计数表⽰(id=4);⼩数位超出则需要四舍五⼊。那指定他的标度float(m,d) 呢?
mysql> show create table fl\G;
***************************1. row ***************************
Table: fl
Create Table: CREATE TABLE `fl` (
`id` int(11) DEFAULT NULL,
`fl` float(7,4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.0
2 sec)
ERROR:
No query specified
mysql>insert into fl values(1,123456);#整数是3位,超出了
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>insert into fl values(3,123.4567);
Query OK, 1 row affected (0.00 sec)
mysql>insert into fl values(2,123.45678);#⼩数位超出,四舍五⼊
Query OK, 1 row affected (0.00 sec)
mysql>insert into fl values(4,12.456789);#⼩数位超出,四舍五⼊
Query OK, 1 row affected (0.00 sec)
mysql>select*from fl;
+------+----------+
| id | fl |
+------+----------+
|1|999.9999|
|3|123.4567|
|2|123.4568|
|4|12.4568|
+------+----------+
4 rows in set (0.00 sec)
从上⾯看出:float(m,d):⼩数点后位数为d,即整数位数为(m-d),整数位超出则整数为(m-d)个999,⼩数点后位数为(d)个9999,不⽤科学计算了;若⼩数位超出,则需要四舍五⼊。要是float(m,d)遇到m和d⼀样呢?
mysql>CREATE TABLE `fl` (
-> `id` int(11) DEFAULT NULL,
-> `fl` float(4,4) DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.04 sec)
mysql>insert into fl values(1,123456);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>insert into fl values(2,123.45678);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>insert into fl values(3,123.4567);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>insert into fl values(4,12.456789);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>select*from fl;
+------+--------+
| id | fl |
+------+--------+
|1|0.9999|
|2|0.9999|
|3|0.9999|
|4|0.9999|
+------+--------+
4 rows in set (0.00 sec)
从上⾯看出:float(m,d):若m和d⼀样,则先把⼩数位数先满⾜了,再分配整数位数。所以只有⼩数点位数,⽽整数位是0。
之前提过的m<=24,要是⼤于24呢?
mysql>CREATE TABLE `fl` (
-> `id` int(11) DEFAULT NULL,
-> `fl` float(25) DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table fl\G;
***************************1. row ***************************
Table: fl
Create Table: CREATE TABLE `fl` (
`id` int(11) DEFAULT NULL,
`fl` double DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
从上⾯看出:在没有标度的float中,从float转换成了double。⽽有标度的 float(m,d)当中,m>24呢?
mysql>CREATE TABLE `fl` (
-> `id` int(11) DEFAULT NULL,
-> `fl` float(100,3) DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table fl\G;
***************************1. row ***************************
Table: fl
Create Table: CREATE TABLE `fl` (
`id` int(11) DEFAULT NULL,
`fl` float(100,3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
从上⾯看出:从float还是float,没有受到影响。不过设置这么⼤,在整数会变的不准;⼩数的位数还是m-d,位数不⾜⽤0填充(zerofill)。如下测试:
mysql> show create table fl\G;
***************************1. row ***************************
Table: fl
Create Table: CREATE TABLE `fl` (
`id` int(11) DEFAULT NULL,
`fl` float(100,3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into fl values(5,777777777777777777777777777.5555);
Query OK, 1 row affected (0.00 sec)
mysql> select * from fl;
+------+---------------------------------+
| id | fl |
+------+---------------------------------+
| 5 | 777777744225350500000000000.000 | #不准了
+------+---------------------------------+
1 row in set (0.00 sec)
mysql>drop table fl;
Query OK, 0 rows affected (0.00 sec)
mysql>CREATE TABLE `fl` (
-> `id` int(11) DEFAULT NULL,
-> `fl` float(25,23) DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)
mysql> show create table fl\G;
***************************1. row ***************************
Table: fl
Create Table: CREATE TABLE `fl` (
`id` int(11) DEFAULT NULL,
`fl` float(25,23) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql>insert into fl values(5,99.555555555555555);
Query OK, 1 row affected (0.00 sec)
mysql>select*from fl;
+------+----------------------------+
| id | fl |
+------+----------------------------+
|5|99.55555725097656000000000| #⼩数位被0填充
+------+----------------------------+
1 row in set (0.00 sec)
注意:当float(m,d) 设置了标度之后,⼩数位⼀定会存在。当整数位没有被超过,需要⽤0填充;整数位被超过了,需要⽤9填充,⼩数位数不能⼤于30否则报错:
mysql>CREATE TABLE `fl` (
-> `id` int(11) DEFAULT NULL,
-> `fl` float(5,3) DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
-> ;
Query OK, 0 rows affected (0.03 sec)
mysql>insert into fl values(1,123);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql>select*from fl;
+------+--------+
| id | fl |
+------+--------+
|1|99.999|
+------+--------+
1 row in set (0.00 sec)
mysql>insert into fl values(2,13);
Query OK, 1 row affected (0.00 sec)
mysql>insert into fl values(3,3);
Query OK, 1 row affected (0.00 sec)
mysql>select*from fl;
+------+--------+
| id | fl |
+------+--------+
|1|99.999| #9填充
|2|13.000| #0填充
|3|3.000|
+------+--------+
3 rows in set (0.00 sec)
mysql>CREATE TABLE `fl` (
-> `id` int(11) DEFAULT NULL,
-> `fl` float(102,100) DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
ERROR 1425 (42000): Too big scale 100 specified for column'fl'. Maximum is30. #标度不能⼤于30,等于30可以建⽴mysql>CREATE TABLE `fl` (
-> `id` int(11) DEFAULT NULL,
-> `fl` float(52,50) DEFAULT NULL
-> ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
ERROR 1425 (42000): Too big scale 50 specified for column'fl'. Maximum is30.
总结:float存在这么多潜在因素,⽤的时候需要注意,能避免就避免⽤。
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论