摘要:
      今天左哥问起一个float浮点数类型的问题,这个类型用的不多,所以也不太了解,现在打算测试下。

知识点:
      float:浮点数,单精度,占4字节。

测试

MySQL 数据类型(float)的注意事项
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)
MySQL 数据类型(float)的注意事项

从上面看出:默认的float类型都只能存6个数字(包括小数点前后的位数),整数超过6位就被科学计数表示(id=4),小数位超出则需要四舍五入。那指定float(m)的精度呢?

MySQL 数据类型(float)的注意事项
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位了
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)
MySQL 数据类型(float)的注意事项

从上面看出:和默认的float一样。所以:默认float和float(m)一样 ,m<=24; 都是默认float类型,都只能存6个数字(包括小数点前后的位数),整数超过6位就被科学计数表示(id=4);小数位超出则需要四舍五入。那指定他的标度float(m,d) 呢?

MySQL 数据类型(float)的注意事项
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.02 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)
MySQL 数据类型(float)的注意事项

从上面看出:float(m,d):小数点后位数为d,即整数位数为(m-d),整数位超出则整数为(m-d)个999,小数点后位数为(d)个9999,不用科学计算了;若小数位超出,则需要四舍五入。要是float(m,d)遇到m和d一样呢?

MySQL 数据类型(float)的注意事项
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)
MySQL 数据类型(float)的注意事项

从上面看出:float(m,d):若m和d一样,则先把小数位数先满足了,再分配整数位数。所以只有小数点位数,而整数位是0。

之前提过的m<=24,要是大于24呢?

MySQL 数据类型(float)的注意事项
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)
MySQL 数据类型(float)的注意事项

从上面看出:在没有标度的float中,从float转换成了double。而有标度的 float(m,d)当中,m>24呢?

MySQL 数据类型(float)的注意事项
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
MySQL 数据类型(float)的注意事项

从上面看出:从float还是float,没有受到影响。不过设置这么大,在整数会变的不准;小数的位数还是m-d,位数不足用0填充(zerofill)。如下测试:

MySQL 数据类型(float)的注意事项
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)
MySQL 数据类型(float)的注意事项

注意:当float(m,d) 设置了标度之后,小数位一定会存在。当整数位没有被超过,需要用0填充;整数位被超过了,需要用9填充,小数位数不能大于30否则报错:

MySQL 数据类型(float)的注意事项
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 数据类型(float)的注意事项
MySQL 数据类型(float)的注意事项
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 is 30.   #标度不能大于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 is 30.