原标题:理解 MySQL 中的数字类型
MySQL 中数据类型常用的就三大类:
数字类型/numeric types
日期和时间/date and time types
字符类型/string (character and byte) types
另外还包含两个没那么常用的大类:
特殊类型/spatial types
JSON
继续之前,先来看一些单位上的约定和概念,
M:根据具体不同的类型,其表示的意思不一样,见下方关于这个参数的讨论。
D 用于定点及浮点数,表示小数点后有多少位。最大可能取值为 30,但不应该超过 M-2。
fsp 适用于 TIME, DATETIME 及 TIMESTAMP。可理解秒后面的小数点位数。它应该是介于 0~6 之间的,0 表示没有小数部分(fractin part)。默认为 0。
[] 方括号表示类型中可选的部分。
存储字符串时指定的类型 VARCHAR(50) 中可接收一个数字作为长度,其实除了字符串类型,数字类型也是可指定该参数的,比如 INT(10),BIGINT(20)。假设后续讨论中这个参数使用字母 M 来表示,即上面提到的。该参数被用在不同类型上时,其表示的意思不一样。
对于整形,它表示 展示宽度/display width。
对于定点数(fixed point)或浮点数(floating point),表示能够存储的总位数,即精度。
对于字符串,表示能够存储的字符串长度。
展示宽度/Display Width
那么什么是展示宽度。展示宽度这个参数具有迷惑性,它不像 CHAR(M) 中有实际意义表示能够存储的字符串长度,在数字类型中,它指数字展示时需要的宽度,是 MySQL 格式化时使用的。即 INT(5),INT(15),INT(25) 能够存储的数字范围都是 INT 类型的范围 -2147483648 ~ 2147483647。如果指定了 ZEROFILL,MySQL 在返回该数字时,对于实际位数小于展示宽度的数字,将自动在左边补零。比如列的类型为 INT(5),实际存储了数字 5,返回时会得到 00005。对于没有指定 ZEROFILL 或实际存储的位数大于指定的展示宽度,则不会自动补零,此时看上去没有任何效果。
CREATE TABLE test_zero_fill
(
with_fill INT( 5) UNSIGNED ZEROFILL NOTNULLPRIMARY KEY,
without_fill INT( 5) UNSIGNED NOTNULL
);
mysql> INSERT INTO test_zero_fill (with_fill, without_fill) VALUES ( 5, 5),( 123456, 123456);
Query OK, 2rows affected ( 0.00sec)
Records: 2Duplicates: 0Warnings: 0
mysql> select* from test_zero_fill;
+-----------+--------------+
| with_fill | without_fill |
+-----------+--------------+
| 00005| 5|
| 123456| 123456|
+-----------+--------------+
2rows inset( 0.00sec)
另外,如果使用了 ZEROFILL,该列将自动设置为 UNSIGNED 类型。
mysql> ALTER TABLE test_zero_fill ADD signed_num INT( 5) signedZEROFILL NOT NULLafter without_fill;
mysql> describe test_zero_fill;
+--------------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------------------+------+-----+---------+-------+
| with_fill | int( 5) unsignedzerofill | NO| PRI | NULL| |
| without_fill | int( 5) unsigned| NO| | NULL| |
| signed_num | int( 5) unsignedzerofill | NO| | NULL| |
+--------------+--------------------------+------+-----+---------+-------+
3rows inset ( 0.00sec)
所以对于数据存储层面来说,展示宽度其实没什么用途。如果真的需要格式化,程序中能够请求 MySQL 的 meta 信息以获取到相应的展示宽度。
假如在 Node.js 中使用 mysqljs/mysql 作为数据库连接的模块,在执行请求时,其回调中返回的 fields 入参便包含了列相应的 meta 信息。
connection.query( "SELECT * from test_zero_fill", function(
error,
results,
fields
){
if( error) throw error;
console. log(fields);
});
fields 中包含列的 meta 信息
FieldPacket{
catalog: 'def',
db: 'data_type',
table: 'test_zero_fill',
orgTable: 'test_zero_fill',
name: 'with_fill',
orgName: 'with_fill',
charsetNr: 63,
length: 5,
type: 3,
flags: 20579,
decimals: 0,
default: undefined,
zeroFill: true,
protocol41: true
},
FieldPacket{
catalog: 'def',
db: 'data_type',
table: 'test_zero_fill',
orgTable: 'test_zero_fill',
name: 'without_fill',
orgName: 'without_fill',
charsetNr: 63,
length: 5,
type: 3,
flags: 4129,
decimals: 0,
default: undefined,
zeroFill: false,
protocol41: true
},
FieldPacket{
catalog: 'def',
db: 'data_type',
table: 'test_zero_fill',
orgTable: 'test_zero_fill',
name: 'signed_num',
orgName: 'signed_num',
charsetNr: 63,
length: 5,
type: 3,
flags: 4193,
decimals: 0,
default: undefined,
zeroFill: true,
protocol41: true
}
]
因此,在设计表时,应该关注使用哪种具体的数据类型能够满足数据存储的需要,而不要被展示宽度所迷惑。
数字类型
数字类型分为有符号 SIGNED 和无符号 UNSIGNED 的情况,有符号即最前面有一位符呈位,可表示正负数。默认情况下为 SIGNED 即有符号。
整型
MySQL 中支持标准的 SQL 整型,
INTEGER (INT)
SMALLINT
并且扩展了一些类型:
TINYINT
MEDIUMINT
BIGINT
以下是 MySQL 中支持的整型,及其对应所需存储空间和取值范围。
类型
空间 (字节)
有符号时最小取值
无符号时最小取值
有符号时最大取值
无符号时最大取值
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
具体到每种类型:
TINYINT[(M)] [UNSIGNED] [ZEROFILL]:微整型,取值范围 -128 ~ 127,无符号情况下为 0 ~ 255。
BOOL, BOOLEAN:效果等同 TINYINT(1),0 表示 FALSE,其他非 0 值处理成 TRUE。其中关键字 TRUE,FALSE 真实代表的是数字 1 和 0。
mysql> SELECT IF( 0, 'true', 'false');
+------------------------+
| IF( 0, 'true', 'false') |
+------------------------+
| false|
+------------------------+
mysql> SELECT IF( 1, 'true', 'false');
+------------------------+
| IF( 1, 'true', 'false') |
+------------------------+
| true|
+------------------------+
mysql> SELECT IF( 2, 'true', 'false');
+------------------------+
| IF( 2, 'true', 'false') |
+------------------------+
| true|
+------------------------+
mysql> SELECT IF( 0= FALSE, 'true', 'false');
+--------------------------------+
| IF( 0= FALSE, 'true', 'false') |
+--------------------------------+
| true|
+--------------------------------+
mysql> SELECT IF( 1= TRUE, 'true', 'false');
+-------------------------------+
| IF( 1= TRUE, 'true', 'false') |
+-------------------------------+
| true|
+-------------------------------+
mysql> SELECT IF( 2= TRUE, 'true', 'false');
+-------------------------------+
| IF( 2= TRUE, 'true', 'false') |
+-------------------------------+
| false|
+-------------------------------+
mysql> SELECT IF( 2= FALSE, 'true', 'false');
+--------------------------------+
| IF( 2= FALSE, 'true', 'false') |
+--------------------------------+
| false|
+--------------------------------+
SMALLINT[(M)] [UNSIGNED] [ZEROFILL]:小整型。取值范围 -32768 ~ 32767,无符号情况下为 0 ~ 65535。
MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]:中整型。取值范围 -8388608 ~ 8388607,无符号情况下为 0 ~ 16777215。
INT[(M)] [UNSIGNED] [ZEROFILL]: 整型。取值范围 -2147483648 ~ 2147483647,无符号情况下为 -2147483648 ~ 2147483647。
INTEGER[(M)] [UNSIGNED] [ZEROFILL]:同 INT。
BIGINT[(M)] [UNSIGNED] [ZEROFILL]:大整型。取值范围 -9223372036854775808 ~ 9223372036854775807,无符号情况下为 0 ~ 18446744073709551615。
关于大整型,关键字 SERIAL 等同于 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE。
还记得创建表时一般需要指定一个自增的整形 ID 字段么,CREATE TABLE table_name (id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT)
SERIAL 关键字其实是 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名,所以下次创建表时可直接使用该关键字,会省事很多。
CREATE TABLE table_name (id SERIAL PRIMARY KEY)
如果你不想要 BIGINT,SERIAL DEFAULT VALUE 是 NOT NULL AUTO_INCREMENT UNIQUE 的别名,那么可以这样来简写 ID 字段:
CREATE TABLE table_name (id INT SERIAL DEFAULT VALUE PRIMARY KEY)
定点型
DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]定点型数字,其中 M 表示总的位数(不包含正负号及小数点),D 表示小数位数。D 为 0 则表示没有小数部分。M 最大取值 65,默认 10;D 最大支持到 30,默认 0。所有的算术运算(+,-,*,/)都基于 65 位的 DECIMAL。
DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]同 DECIMAL。
定点型数字存储精确的数字,用于准确性要求高的场合,比如涉及金钱。底层实现上,MySQL 使用二进制形式存储该类型的值。
通常的用法如下:
salary DECIMAL(5,2)
上面示例中,salary 为一个 5 位精度两位小数的定点型。取值范围 -999.99 ~ 999.99。
因为 D 缺省时默认为 0,所以 DECIMAL(M) 表示 DECIMAL(M,0),现时,MySQL 中,M 缺省时默认为 10,所以 DECIMAL 表示 DECIMAL(10,0)。
当实际存储的值其小数大于指定的位数时,其精度会自动转换成所存储的值的精度。
浮点型
区别于 DECIMAL,浮点型存储的数字是个近似值。内部存储时,MySQL 为单精度使用 4 字节(bytes),双精度使用 8 字节。
浮点型包含以下这些类型:
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]:小型的单精度浮点型。根据 IEEE 标准理论取值范围 -3.402823466E+38 ~ -1.175494351E-38, 0, 1.175494351E-38 ~ 3.402823466E+38,实际的取值范围因硬件和操作系统而异,会比理论值要小。
M 表示总位数,D 表示小数位数。两者省略的情况下,其值为硬件允许的最大值。比如 FLOAT(7,4) 看起来会是这个样子: -999.9999。
FLOAT[(M,D) 这种形式的类型不是标准的 SQL 类型,后续会废弃掉。
FLOAT(p) [UNSIGNED] [ZEROFILL]:是标准的 SQL 类型,p 表示精度。但 MySQL 中,根据 p 取值的不同,底层实际将其处理成别的类型。比如 0 ~ 24 时,当成 4 字节单精度 FLOAT 类型来处理,25 ~ 53 时处理成 8 字节双精度的 DOUBLE 类型。
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]:双精度浮点型。取值范围 -1.7976931348623157E+308 ~ -2.2250738585072014E-308, 0, 2.2250738585072014E-308 ~ 1.7976931348623157E+308。同 FLOAT(M,D),DOUBLE(M,D) 这种形式的双精度类型也是非标准 SQL 类型,后续会废弃。
DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]:DOUBLE 的别名。
所以实际使用时,为了最大限度的兼容性,直接使用 FLOAT,DOUBLE,PRECISION 而不要指定精度及小数。
BIT 类型
BIT[(M)] 类型用于存储单个状态值,M 表示包含几位。默认为1,最大可取 64。
该类型的值可通过 b'value' 的形式书写,其中 value 部分以二进制的形式呈现,比如 b'111' 和 b'10000000' 分别表示 7 和 128。更加详细的信息可参考 9.1.5 Bit-Value Literals。
如果赋值到该类型上的值小于 M 指定的位数,值左边会补零,比如将 b'101' 存储到类型为 BIT(6) 的列,实际会是 b'000101'。
存储的值溢出的情况
将要存储的值超出数字类型的范围时,其表现跟当前设置的 SQL 模式有关。具体来说,
开启 SQL 严格模式时,超出范围的值会写入失败,MySQL 会中断操作并且直接抛错。
非严格模式下,MySQL 会将值裁剪到合适的大小进行写入。即超出的情况下存成该类型能够接收的最大值。
考察一个通过如下语句创建的表 t1:
CREATETABLEt1 (i1 TINYINT, i2 TINYINT UNSIGNED);
SQL 严格模式下,尝试写入一个超出范围的值时抛错:
mysql> SETsql_mode = 'TRADITIONAL';
mysql> INSERT INTOt1 (i1, i2) VALUES( 256, 256);
ERROR1264( 22003): Out ofrange value forcolumn 'i1' at row 1
mysql> SELECT* FROMt1;
Empty set( 0.00sec)
以下是非严格模式下进行裁剪存储的情况:
mysql> SET sql_mode = '';
mysql> INSERT INTO t1 (i1, i2) VALUES( 256, 256);
mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------+
| Level |Code | Message |
+---------+------+---------------------------------------------+
| Warning |1264| Out of range value forcolumn 'i1' at row 1 |
| Warning |1264| Out of range value forcolumn 'i2' at row 1 |
+---------+------+---------------------------------------------+
mysql> SELECT * FROM t1;
+------+------+
| i1 |i2 |
+------+------+
|127| 255 |
+------+------+
上述表现同样会出现在涉及到对列进行转换修改的一些操作上,比如 ALTER TABLE,LOAD DATA,UPDATE 以及使用 INSERT 同时插入多行数据时。严格模式下会抛错失败,非严格模式下值会进行裁剪。但失败的情况不尽相同,如果是事务类型的表,会整个全失败,其他情况根据具体的值会部分成功,部分失败。
进行数字计算时如果有溢出,也会抛错,比如对于 BIGINT 其最大值为 9223372036854775807,因为 MySQL 中默认对数字类型是有符号类型,如下操作会抛错,
mysql> SELECT9223372036854775807+ 1;
ERROR1690( 22003): BIGINT value isout ofrange in'(9223372036854775807 + 1)'
对于上述情况,可显式将 被操作数进行类型转换,转成无符号的 BIGINT:
mysql> SELECT CAST(9223372036854775807 AS UNSIGNED) + 1;
+-------------------------------------------+
| CAST(9223372036854775807 AS UNSIGNED) + 1 |
+-------------------------------------------+
| 9223372036854775808 |
+-------------------------------------------+
通过带上小数后,转成 DECIMAL 也能修正上面的错误,因为 DECIMAL 比整形要大,
mysql> SELECT 9223372036854775807.0 + 1;
+---------------------------+
| 9223372036854775807.0 + 1 |
+---------------------------+
| 9223372036854775808.0 |
+---------------------------+
两数相减时,其中一个为无符号数,得出的结果默认为也为无符号。所以如果想减之后结果是负数,则会抛错。
mysql> SETsql_mode = '';
Query OK, 0rows affected ( 0.00sec)
mysql> SELECTCAST( 0ASUNSIGNED) - 1;
ERROR1690( 22003): BIGINT UNSIGNED value isout ofrange in'(cast(0 as unsigned) - 1)'
除非开启了 NO_UNSIGNED_SUBTRACTION:
mysql> SETsql_mode = 'NO_UNSIGNED_SUBTRACTION';
mysql> SELECTCAST( 0ASUNSIGNED) - 1;
+-------------------------+
| CAST( 0ASUNSIGNED) - 1|
+-------------------------+
| -1|
+-------------------------+
总结
对于整型或浮点型,可指定 AUTO_INCREMTN 属性。指定该属性性,将不能接收负值。同时 CHECK 属性与该属性冲突,也不能同时使用。但对于 FLOAT 和 DOUBLE,AUTO_INCREMENT 属性的支持将逐渐废弃掉,实际使用时尽量避免。
对于需要精确数值的场合,使用 DECIMAL,比如涉及金钱的情况。
对于整形,展示宽度不是其存储的值范围,只用来格式化。
刘哇勇
https://www.linuxidc.com/Linux/2019-05/158428.htm返回搜狐,查看更多
责任编辑: