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'
相同。