Mysql数据类型---FLOAT

 
标签（空格分隔）： MYSQL

---

MYSQL版本：MYSQL5.7

基本语法

FLOAT(浮点类型)用来存储很大或很小的值。这可能是由小数点前的30位、100位或者更多位组成的数字，或者小数点后拥有很多位的数字。考虑那些小数点后的位数为无限多的数值，例如众所周知的圆周率，1/3。然而对于一个浮点类型可用的存储空间有限，有时候无法存储真正的数值。如果一个数值非常大或非常小，将会存储一个近似值。这就是为什么有时把他们叫做估计值。
在DECIMALL类型中，小数点在每个值中位置相同。但是对于浮点类型，在每个值中，小数点可以在任何地方。换句话说，小数点是浮动的。这就是为什么我们称她位浮点类型。
MYSQL有两个浮点类型：单精度(FLOAT)，双精度(DOUBLE)。他们的区别在于使用的存储空间大小不同，因此值的范围也不同。都不能UNSIGNED。

取值范围

类型说明 正区间 负区间 单精度(FLOAT) [ –3.402823466E38 , –1.175494351E-38 ] [ 1.175494351E-38 , 3.402823466E38] 双精度(DOUBLE) [ –1.7976931348623157E308,–2.2250738585072014E-308 ] [ 2.2250738585072014E-308 , 1.7976931348623157E308 ]

声明语法是

FLOAT(M)FLOAT[(M,D)] [ZEROFILL] 

1. 指定一个参数
   如果M在[0,24] 是一个单精度浮点数，如果M在[25,53]则是双精度浮点数。
2. 指定两个参数。
   在这种情况下，两个参数被看作是浮点数的宽度和刻度。指定宽度和刻度将自动成为一个单精度浮点类型。

创建表fl1,使用默认类型FLOAT,并存储几个值

建表

CREATE TABLE `fl1` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `fl` float DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1mysql> insert into fl1(fl) values(12345);Query OK, 1 row affected (0.53 sec)mysql> insert into fl1(fl) values(123456);Query OK, 1 row affected (0.07 sec)mysql> insert into fl1(fl) values(1234567);Query OK, 1 row affected (0.06 sec)mysql> insert into fl1(fl) values(12345678);Query OK, 1 row affected (0.04 sec)mysql> insert into fl1(fl) values(1.2345);Query OK, 1 row affected (0.81 sec)mysql> insert into fl1(fl) values(1.23456);Query OK, 1 row affected (0.06 sec)mysql> insert into fl1(fl) values(1.234567);Query OK, 1 row affected (0.07 sec)mysql> insert into fl1(fl) values(1.2345678);Query OK, 1 row affected (0.06 sec)mysql> select * from fl1;+----+----------+| id | fl       |+----+----------+|  1 |    12345 ||  2 |   123456 ||  3 |  1234570 ||  4 | 12345700 ||  5 |   1.2345 ||  6 |  1.23456 ||  7 |  1.23457 ||  8 |  1.23457 |+----+----------+8 rows in set (0.00 sec)

小结：
默认的float类型只能存6个数字（包括小数部分和整数部分）。整数或者小数超过6位，超过的部分就会四舍五入

新建表fl2,指定M为20

CREATE TABLE `fl2` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `fl` float(20) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1mysql> insert into fl2(fl) values(12345);Query OK, 1 row affected (0.07 sec)mysql> insert into fl2(fl) values(123456);Query OK, 1 row affected (0.07 sec)mysql> insert into fl2(fl) values(1234567);Query OK, 1 row affected (0.07 sec)mysql> insert into fl2(fl) values(12345678);Query OK, 1 row affected (0.08 sec)mysql> insert into fl2(fl) values(1.2345);Query OK, 1 row affected (0.09 sec)mysql> insert into fl2(fl) values(1.23456);Query OK, 1 row affected (0.04 sec)mysql> insert into fl2(fl) values(1.234567);Query OK, 1 row affected (0.04 sec)mysql> insert into fl2(fl) values(1.2345678);Query OK, 1 row affected (0.08 sec)mysql> select * from fl2;+----+----------+| id | fl       |+----+----------+|  1 |    12345 ||  2 |   123456 ||  3 |  1234570 ||  4 | 12345700 ||  5 |   1.2345 ||  6 |  1.23456 ||  7 |  1.23457 ||  8 |  1.23457 |+----+----------+8 rows in set (0.00 sec)

小结：
默认float和float(m)一样 ，m<=24时都是默认float类型，都只能存6个数字（包括小数点前后的位数），整数超过6位就被四舍五入

新建表fl3,使用FLOAT(M,D)指定字段

CREATE TABLE `fl3` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `fl` float(7,4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1mysql> insert into fl3(fl) values(123);Query OK, 1 row affected (0.74 sec)mysql> insert into fl3(fl) values(1234);ERROR 1264 (22003): Out of range value for column 'fl' at row 1mysql> truncate fl3;Query OK, 0 rows affected (0.92 sec)mysql> insert into fl3(fl) values(123);Query OK, 1 row affected (0.06 sec)mysql> insert into fl3(fl) values(1234);ERROR 1264 (22003): Out of range value for column 'fl' at row 1mysql> insert into fl3(fl) values(123.4567);Query OK, 1 row affected (0.06 sec)mysql> insert into fl3(fl) values(123.45678);Query OK, 1 row affected (0.06 sec)mysql> insert into fl3(fl) values(123.45679);Query OK, 1 row affected (0.05 sec)mysql> select * from fl3;+----+----------+| id | fl       |+----+----------+|  1 | 123.0000 ||  2 | 123.4567 ||  3 | 123.4568 ||  4 | 123.4568 |+----+----------+4 rows in set (0.00 sec)

小结：
FLOAT(M,D) 整数部分为 (M-D)个数，超出个数则报错，小数部分为D个数，超出个数则四舍去五入

新建表fl4,FLOAT字段中M和D相等

mysql> show create table fl4 \G*************************** 1. row ***************************       Table: fl4Create Table: CREATE TABLE `fl4` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `fl` float(4,4) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> insert into fl4(fl) values(1);ERROR 1264 (22003): Out of range value for column 'fl' at row 1mysql> insert into fl4(fl) values(0.1);Query OK, 1 row affected (0.08 sec)mysql> insert into fl4(fl) values(0.12);Query OK, 1 row affected (0.07 sec)mysql> insert into fl4(fl) values(0.123);Query OK, 1 row affected (0.04 sec)mysql> insert into fl4(fl) values(0.1234);Query OK, 1 row affected (0.08 sec)mysql> insert into fl4(fl) values(0.12345);Query OK, 1 row affected (0.08 sec)mysql> select * from fl4;+----+--------+| id | fl     |+----+--------+|  5 | 0.1000 ||  6 | 0.1200 ||  7 | 0.1230 ||  8 | 0.1234 ||  9 | 0.1234 |+----+--------+5 rows in set (0.00 sec)

小结：
当M=D时，依然遵循FLOAT(M,D)的规则，只是整数部分只能为0.

新建表fl5,FLOAT字段中M > 24

CREATE TABLE `fl5` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `fl` float(30) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;mysql> show create table fl5 \G*************************** 1. row ***************************       Table: fl5Create Table: CREATE TABLE `fl5` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `fl` double DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin11 row in set (0.00 sec)

小结：
在FLOAT(M)中，M > 25,自动转为DOUBLE类型

新建表fl6,FLOAT字段中M = 100,D = 3

mysql> show create table fl6 \G*************************** 1. row ***************************       Table: fl6Create Table: CREATE TABLE `fl6` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `fl` float(100,3) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql> insert into fl6 values(5,777777777777777777777777777.5555);Query OK, 1 row affected (0.35 sec)mysql> select * from fl6;+----+---------------------------------+| id | fl                              |+----+---------------------------------+|  5 | 777777744225350500000000000.000 |+----+---------------------------------+1 row in set (0.00 sec)

小结：
在FLOAT(M,D)中，M设置很大不会影响数据类型，还是FLOAT，但是数据会变得不准。