11、MySQL 也有精确精度的浮点数 ( 上 )

MySQL 支持 SQL 标准中提到的所有数值数据类型,这些类型包括精确的数值数据类型,例如 INTEGER、SMALLINT、DECIMAL 和 NUMERIC,也包括一些 近似 数值数据类型,例如 FLOAT、REAL 和 DOUBLE PRECISION 双精度浮点数。

在MySQL 中,关键字 INT 是 INTEGER 的同义词,而 DEC 和 FIXED 是 DECIMAL 的同义词。

MySQL 也会把 DOUBLE 视为 DOUBLE PRECISION 的同义词,当然了,这是在非标准扩展实现的。

MySQL 同样会把 REAL 视为 DOUBLE PRECISION 的同义词,这是一个非标准的变体,另外,这是可以改变的,如果启用了 REAL_AS_FLOAT SQL 模式,则会把 REAL 视为 FLAOT 的同义词。

所以,别看 MySQL 的数值数据类型好多,真正意义上也就那么几个而已。

另外要说的是,MySQL 还支持 BIT 这种位数据类型,但仅在 MyISAM 、 MEMORY 和 InnoDB 三种存储引擎中得到支持。

还有,对于这些数值类型的计算结果,取决于操作数的数据类型和哪些操作,如果要精准的数值类型,那么可以使用 DECIMAL

整型

MySQL 中,所有的整型 ( INTEGER、INT、SMALLINT、TINYINT、MEDIUMINT、BIGINT ) 都是精确精度数值类型。

如果我没记错的话,SQL 标准只支持两种整型,分别是 INTEGER (或同义词 INT ) 和 SMALLINT。但有在此之上添加了一些非标准扩展,以支持 TINYINT、MEDIUMINT 和 BIGINT 几种整型。

下表列出了每种整数类型所需的存储和范围

类型 存储字节 有符号最小值 无符号最小值 有符号最大值 无符号最大值
TINYINT 1 -128 0 127 255
SMALLINT 2 -32768 0 32767 65535
MEDIUMINT 3 -8388608 0 8388607 16777215
INT 4 -2147483648 0 2147483647 4294967295
BIGINT 8 -263 0 263-1 264-1

固定精度浮点类型 DECIMAL、NUMERIC

MySQL 也有固定精度 (精确精度) 浮点类型,就是 DECIMAL、NUMERIC,在保持精确精度很重要的场景中,可以使用这些类型,例如货币数据。

固定精度的意思就是,10 个 0.0000001 相加,得到的结果一定是 0.000001,而不是 0.0000009 或 0.0000011。

因为NUMERIC 的底层实现采用的是 DECIMAL ,所以,NUMERIC 应该算是 DECIMAL 的同义词了。

为了确保精度,MySQL 在保存 DECIMAL 类型的数据时采用的是二进制格式 ( binary )

千万不要和 FLOAT、DOUBLE 混淆,所有的数值类型在硬件层面,比如硬盘,存储的都是二进制,但 DECIMAL 在 MySQL 层面已经采用了二进制格式存储,所以,才能确保精度

将某列的数据类型声明为 DECIMAL 时,可以指定精度和刻度,所谓精度就是能保存的数据的位数长度(包括小数点和小数),而刻度,就是指小数位数,例如

salary DECIMAL(5,2)

在上面这个范例中,5 是精度而 2 是刻度,精度为 5 表示为值存储的有效位数是 5 ,刻度为 2 表示小数点后可存储的位数为 2 。

在SQL 标准下,DECIMAL(5,2) 能够存储五位数和两位小数的任何值,因此可存储在 salary 列中的值范围为 -999.99 到 999.99 。

在SQL 标准中, DECIMAL(M) 其实和 DECIMAL(M,0) 是相同的,类似的,DECIMAL 和 DECIMAL(M,0) 是相同的,只不过 m 值的大小由实现者自己定义,在 MySQL 中,这个 m 值的大小为 10。也就是说,在 MySQL 中,DECIMAL 和 DECIMAL(10,0) 是等价的。

如果刻度部分为 0,例如 DECIMAL(m,0) ,那么 DECIMAL 值不包含小数点或小数部分。

DECIMAL 的最大位数为 65,而具体的 DECIMAL 列的实际范围可能受给定列的精度或刻度限制。当为这样的列分配一个小数点后面的位数超过指定比例允许的值时,该值将转换为该比例,这种情况下会失真 (丢失精度),失真的行为是特定于操作系统的,但通常效果是截断到允许的位数。

近似精度浮点类型 FLOAT 和 DOUBLE

在MySQL 中,但其实不止 MySQL,在任何编程语言和数据库系统中,FLOAT 和 DOUBLE 都是一个近似精度浮点数。

对于单精度浮点数和双进度浮点数,MySQL 分别使用 个字节和个字节来存储。

对于FLOAT ,SQL 标准允许在关键字 FLOAT 后面的括号中的位中选择性地指定精度 ( 但不是指数的范围 ),MySQL 还支持此可选的精度,但精度值仅用于确定存储大小,精度在 0~23 之间会产生一个 4 字节的单精度 FLOAT 列;在 24-53 之间的精度产生 8 字节双精度 DOUBLE 列

MySQL 还支持非标准语法:FLOAT(M,D) 或 REAL(M,D) 或 DOUBLE PRECISION(M,D)。这里,(M,D) 表示总共可以存储最多 M 位的值,其中 D 位可以在小数点后面。

所以,千万不要混淆,以为 m 值是用于指定浮点数整数部分的位数。m 值是用于指定所有的位数。

例如,一个定义为 FLOAT(7,4) 的列在显示时将显示为 -999.9999 。 MySQL 在存储值时执行舍入,因此如果将 999.00009 插入FLOAT(7,4) 列,则近似结果为 999.0001。

由于浮点值是近似值而未存储为精确值,因此尝试在比较中将它们视为精确值可能会导致问题,而且还受平台或实现依赖性的影响。

为了获得最大的可移植性,需要存储近似数值数据值的代码应使用 FLOAT 或 DOUBLE PRECISION,但不指定精度或小数位数

位模式数据类型 BIT

一直不知道 MySQL 还支持 BIT 数据类型,以前还傻傻的一直使用一个 INT 自己实现位模式数据类型。

BIT 数据类型用于存储位 ( bit ) 值,对于声明为 BIT(M) 的列许存储 M 个位值。 M 的范围为 1~64。

要指定位值,可以使用 b'value' 语法。 value 是使用 0 和 1 写的二进制值。例如,b'111' 和 b'10000000' 分别代表 7 和 128

如果为一个定义为 BIT(M) 的列分配一个小于 M 位的值,则 MySQL 会自动在该值左侧用 0 填充。例如,将 b'101' 的值分配给 BIT(6) 列实际上与分配 b'000101' 相同。