浮点类型
表结构
Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fl` float DEFAULT NULL, `do` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
浮点类型包括单精度类型float和双精度类型double,它俩的本质区别是字节数不同。float类型保存6位有效数,超出6位的被四舍五入,double类型保存15位有效数。
insert into t1 (fl) values (123456),(1234567);
执行结果:
+----+---------+------+ | id | fl | do | +----+---------+------+ | 1 | 123456 | NULL | | 2 | 1234570 | NULL | +----+---------+------+
1234567超出了6位,则第七位被四舍五入。第七位后面的位数全部置0
insert into t1 (fl) values (12345616234);
执行结果:
+----+-------------+------+ | id | fl | do | +----+-------------+------+ | 1 | 123456 | NULL | | 2 | 1234570 | NULL | | 3 | 12345600000 | NULL | +----+-------------+------+
insert into t1 (fl) values (12.345616234);
执行结果:
+----+-------------+------+ | id | fl | do | +----+-------------+------+ | 1 | 123456 | NULL | | 2 | 1234570 | NULL | | 3 | 12345600000 | NULL | | 4 | 12.3456 | NULL | +----+-------------+------+
12.345616234被四舍五入为12.345600000
为float类型指定精度时
alter table t1 modify fl float(20); show create table t1\G;
执行结果:
Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fl` float DEFAULT NULL, `do` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
可以看到fl的类型是float,并没有显示精度20。
insert into t1 (fl) values (123456),(1234567),(12345616234),(12.345616234);
执行结果:
+----+-------------+------+ | id | fl | do | +----+-------------+------+ | 1 | 123456 | NULL | | 2 | 1234570 | NULL | | 3 | 12345600000 | NULL | | 4 | 12.3456 | NULL | +----+-------------+------+
结果和没有设置float精度一样。
float(m),m <= 24时,字段类型是float, m > 24 时,字段类型会变成double类型
alter table t1 modify fl float(25);
执行结果:
Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fl` double DEFAULT NULL, `do` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
也就是说float(m)格式时,m的作用是用来判断类型的。但是double类型不存在double(m)这种语法
alter table t1 modify do double(10);
执行结果:报错
为float类型指定精度和标度
表结构
Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fl` float(7,4) DEFAULT NULL, `do` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
insert into t1 (fl) values (12345);
执行结果:
+----+----------+------+
| id | fl | do |
+----+----------+------+
| 1 | 999.9999 | NULL |
+----+----------+------+
float(m,d):m表示总长度,d表示小数位长度。m-d表示整数部分长度
12345以12345.0000形式存储,由于整数部分超出了长度范围,则实际保存的是(m,d)能表示的最大值
insert into t1 (fl) values (123.4567);
执行结果:
+----+----------+------+ | id | fl | do | +----+----------+------+ | 1 | 999.9999 | NULL | | 2 | 123.4567 | NULL | +----+----------+------+
刚好完整插入
insert into t1 (fl) values (123.45678);
执行结果:
+----+----------+------+ | id | fl | do | +----+----------+------+ | 1 | 999.9999 | NULL | | 2 | 123.4567 | NULL | | 3 | 123.4568 | NULL | +----+----------+------+
小数位保留4位,第五位被四舍五入。
float(4,4)时
insert into t1 (fl) values (12345);
执行结果:
+----+----------+------+ | id | fl | do | +----+----------+------+ | 1 | 999.9999 | NULL | | 2 | 123.4567 | NULL | | 3 | 123.4568 | NULL | | 4 | 0.9999 | NULL | +----+----------+------+
insert into t1 (fl) values (12.345);
执行结果:
+----+----------+------+ | id | fl | do | +----+----------+------+ | 1 | 999.9999 | NULL | | 2 | 123.4567 | NULL | | 3 | 123.4568 | NULL | | 4 | 0.9999 | NULL | | 5 | 0.9999 | NULL | +----+----------+------+
insert into t1 (fl) values (1.35);
执行结果:
+----+----------+------+ | id | fl | do | +----+----------+------+ | 1 | 999.9999 | NULL | | 2 | 123.4567 | NULL | | 3 | 123.4568 | NULL | | 4 | 0.9999 | NULL | | 5 | 0.9999 | NULL | | 6 | 0.9999 | NULL | +----+----------+------+
插入的都为0.9999,因为float(4,4)在插入数据时,整数位的长度必须为0,否则插入(4,4)所能表示的最大值
insert into t1 (fl) values (0.123456);
执行结果:
+----+----------+------+ | id | fl | do | +----+----------+------+ | 1 | 999.9999 | NULL | | 2 | 123.4567 | NULL | | 3 | 123.4568 | NULL | | 4 | 0.9999 | NULL | | 5 | 0.9999 | NULL | | 6 | 0.9999 | NULL | | 7 | 0.1235 | NULL | +----+----------+------+
alter table t1 modify fl float(10,4); insert into t1 (fl) values (23.23);
执行结果:
+----+----------+------+ | id | fl | do | +----+----------+------+ | 1 | 999.9999 | NULL | | 2 | 123.4567 | NULL | | 3 | 123.4568 | NULL | | 4 | 0.9999 | NULL | | 5 | 0.9999 | NULL | | 6 | 0.9999 | NULL | | 7 | 0.1235 | NULL | | 8 | 23.2300 | NULL | +----+----------+------+
可以看到,小数位不足时,会补0,整数位不足时,不需要补0
alter table t1 modify fl float(30,10);
Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fl` float(30,10) DEFAULT NULL, `do` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
float(m,d)当m>24时,字段的类型不会变成double类型。这和float(m)是有区别的。
表结构
Create Table: CREATE TABLE `t3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `dc` decimal(10,0) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
定点小数 decimal 以字符串形式保存,所以是精确存储数据
不指定精度和标度时,decimal默认0位小数位
insert into t3 (dc) values (1234.567);
select * from t3;
执行结果:
+----+------+ | id | dc | +----+------+ | 1 | 1235 | +----+------+
alter table t3 modify dc decimal(10,3);
insert into t3 (dc) values (1234.567);
select * from t3;
执行结果:
+----+----------+ | id | dc | +----+----------+ | 1 | 1235.000 | | 2 | 1234.567 | +----+----------+
insert into t3 (dc) values (1234.567891); select * from t3;
执行结果:
+----+----------+ | id | dc | +----+----------+ | 1 | 1235.000 | | 2 | 1234.567 | | 3 | 1234.568 | +----+----------+
保留3位小数位
表结构
Table: t3 Create Table: CREATE TABLE `t3` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fl` float DEFAULT NULL, `db` double DEFAULT NULL, `dc` decimal(10,0) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
insert into t3 (fl,db,dc) values (12345678,12345678,12345678); select * from t3;
执行结果:
+----+----------+----------+----------+ | id | fl | db | dc | +----+----------+----------+----------+ | 1 | 12345700 | 12345678 | 12345678 | +----+----------+----------+----------+
select * from t3 where fl = 12345700;
执行结果:
Empty set (0.00 sec)
select * from t3 where fl = 12345678;
执行结果:
+----+----------+----------+----------+ | id | fl | db | dc | +----+----------+----------+----------+ | 1 | 12345700 | 12345678 | 12345678 | +----+----------+----------+----------+
可以看到float类型,保存整数值时,where字句条件用insert时的值,反而能查询成功
insert into t3 (fl,db,dc) values (12.34,12.34,12.34); select * from t3;
执行结果:
+----+----------+----------+----------+ | id | fl | db | dc | +----+----------+----------+----------+ | 1 | 12345700 | 12345678 | 12345678 | | 2 | 12.34 | 12.34 | 12 | +----+----------+----------+----------+
decimal默认0小数位
select * from t3 where fl=12.34
执行结果:
Empty set (0.00 sec)
float保存小数值时,即使保存的数值是insert时的数值,没有发生四舍五入,但是在等值查询时,查找失败
设置float类型精度和标度时,能等值查找成功
alter table t3 modify fl float(10,4);
select * from t3;
+----+---------------+----------+----------+ | id | fl | db | dc | +----+---------------+----------+----------+ | 1 | 12345678.0000 | 12345678 | 12345678 | | 2 | 12.3400 | 12.34 | 12 | +----+---------------+----------+----------+
select * from t1 where fl = 12.34;
执行结果:
+----+---------+-------+------+ | id | fl | db | dc | +----+---------+-------+------+ | 2 | 12.3400 | 12.34 | 12 | +----+---------+-------+------+