MySQL 小数类型介绍
对于保证精度的数字,MySQL 也有对应的小数类型,下图是 MySQL 中小数类型概览。
浮点:小数点非固定的数,可表示数据范围较广,整数,小数都可表示。
定点:小数点固定,可表示整数,小数。int(整数)本质是小数点位于末尾的 32 位定点数而已。
-- 建表
create table test_1(
f1 float(5,2),
f2 decimal(5,2)
);
-- 写入
insert into test_1 value(100, 100);
select * from test_1;f1 f2 100 100.00
单精度:使用 4 个字节存储,有效数字为 8 位,MySQL 中的 float 类型为单精度。
双精度:使用 8 个字节存储,有效数字为 16 位,MySQL 中的 double 类型为双精度。
-- 建表,精度和标度,都是用 MySQL 中的极限值
create table test_13(
f1 float(255, 30) UNSIGNED,
f2 DOUBLE(255, 30) UNSIGNED
);
-- 写入圆周率
insert into test_13 value(3.14159265358979323846, 3.14159265358979323846);
select * from test_5;f1 f2 3.1415927 3.141592653589793
1. 浮点类型
MySQL 中的浮点类型有 float 和 double,其中 float 为单精度 double 为双精度。
1.1 数值精度说明
MySQL 允许使用非标准语法(其他数据库未必支持,因此如果涉及到数据迁移,则最好不要这么用)FLOAT(M,D) 或 DOUBLE(M,D)。这里,M 称为精度,D 称为标度。(M,D) 中 M = 整数位 + 小数位,D = 小数位。 D <= M <= 255,0 <= D <= 30。
例如,定义为 FLOAT(5,2) 的一个列可以显示为 -999.99 - 999.99 如果超过这个范围会报错。
浮点类型,也可以加 UNSIGNED,但是不会改变数据范围,例如:FLOAT(3,2) UNSIGNED 仍然只能表示 0 - 9.99 的范围。
PS:整数类型 UNSIGNED 和 SIGNED 的范围会有变化,例如 int 类型,UNSIGNED 的最大值是 4294967295 有 SIGNED 的最大值是 2147483647。
所以在 MySQL 8.0 版本,已经计划取消指定位数,及 UNSIGNED 小数类型,创建表时会有 warning:
Specifying number of digits for floating point data types is deprecated and will be removed in a future release. UNSIGNED for decimal and floating point data types is deprecated and support for it will be removed in a future release.
1.2 整数超出范围
如果存储时,整数部分超出了范围,MySQL就会报错,不允许存这样的值。
-- 创建测试表,范围是 -999.9 - 999.9
CREATE TABLE test1
(
f1 FLOAT(4, 1),
f2 double(4, 1)
);
-- 插入整数范围内的数字,可以正常插入
insert into test1 value (123.4, 123.4);
-- 插入范围外的数字,异常
insert into test1 value (1234.5, 1234.5);
-- [22003][1264] Out of range value for column 'f1' at row 1
1.3 小数超出范围
如果小数超出范围,会四舍五入,如果四舍五入计算后,导致整数超出范围,会报错。
-- 创建测试表,范围是 -999.9 - 999.9
CREATE TABLE test1
(
f1 FLOAT(4, 1),
f2 double(4, 1)
);
-- 插入整数范围内的数字,可以正常插入,小数超过范围
insert into test1 value (123.45, 123.45);
-- 小数超过范围,四舍五入后,整数也会超过范围,返回异常
insert into test1 value (999.95, 999.95);
-- [22003][1264] Out of range value for column 'f1' at row 1
f1 | f2 |
---|---|
123.4 | 123.4 |
123.5 | 123.5 |
1.4 精度误差说明
-- 创建一张表,DOUBLE 类型
CREATE TABLE test_double2
(
f1 DOUBLE
);
-- 插入数据
INSERT INTO test_double2
VALUES (0.47),
(0.44),
(0.19);
-- 计算
select sum(f1) from test_double2;
sum(f1) |
---|
1.0999999999999999 |
在计算时,可能会丢失精度,所以与钱相关的字段,建议使用 DECIMAL 类型。一般为 DECIMAL(16, 4) 精确到 角、分、厘、毫。
2. 定点类型
MySQL 中定点类型一般指的是 DECIMAL,列的声明语法是DECIMAL(M,D)。NUMERIC 与 DECIMAL 同义,如果字段类型定义为 NUMERIC,则将自动转成 DECIMAL。
2.1 数值精度说明
对于声明语法 DECIMAL(M,D),自变量的值范围如下:
M 是最大位数(精度),范围是 1 到 65。可不指定,默认值是 10。
D 是小数点右边的位数(小数位)。范围是 0 到 30,并且不能大于 M,可不指定,默认值是 0。
例如字段 salary DECIMAL(5,2),能够存储具有五位数字和两位小数的任何值,因此可以存储在 salary 列中的值的范围是从 -999.99 到 999.99
2.2 整数超出范围
如果存储时,整数部分超出了范围,MySQL就会报错,不允许存这样的值。
-- 创建测试表,范围是 -999.9 - 999.9
create table t2(
f1 decimal(3, 2)
);
-- 插入整数范围内的数字,可以正常插入
insert into test1 value (3.14, 3.14);
-- 插入范围外的数字,异常
insert into test1 value (1234.5, 1234.5);
-- Out of range value for column 'f1' at row 1
2.3 小数超出范围
如果小数超出范围,会四舍五入,如果四舍五入计算后,导致整数超出范围,会报错。
-- 创建测试表,范围是 -999.9 - 999.9
create table t2(
f1 decimal(3, 2)
);
-- 小数超过范围,会四舍五入自动截断
insert into t2 value(9.991);
-- 插入范围外的数字,异常
insert into t2 value(9.998);
-- Out of range value for column 'f1' at row 1
f1 |
---|
9.99 |