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 NOT NULL PRIMARY KEY,
- without_fill INT(5) UNSIGNED NOT NULL
- );
- MySQL> INSERT INTO test_zero_fill (with_fill, without_fill) VALUES (5, 5),(123456, 123456);
- Query OK, 2 rows affected (0.00 sec)
- Records: 2 Duplicates: 0 Warnings: 0
- MySQL> select * from test_zero_fill;
- +-----------+--------------+
- | with_fill | without_fill |
- +-----------+--------------+
- | 00005 | 5 |
- | 123456 | 123456 |
- +-----------+--------------+
- 2 rows in set (0.00 sec)
另外, 如果使用了 ZEROFILL, 该列将自动设置为 UNSIGNED 类型.
- MySQL> ALTER TABLE test_zero_fill ADD signed_num INT(5) signed ZEROFILL NOT NULL after without_fill;
- MySQL> describe test_zero_fill;
- +--------------+--------------------------+------+-----+---------+-------+
- | Field | Type | Null | Key | Default | Extra |
- +--------------+--------------------------+------+-----+---------+-------+
- | with_fill | int(5) unsigned zerofill | NO | PRI | NULL | |
- | without_fill | int(5) unsigned | NO | | NULL | |
- | signed_num | int(5) unsigned zerofill | NO | | NULL | |
- +--------------+--------------------------+------+-----+---------+-------+
- 3 rows in set (0.00 sec)
所以对于数据存储层面来说, 展示宽度其实没什么用途. 如果真的需要格式化, 程序中能够请求 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 信息
因此, 在设计表时, 应该关注使用哪种具体的数据类型能够满足数据存储的需要, 而不要被展示宽度所迷惑.
数字类型
数字类型分为有符号 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:
CREATE TABLE t1 (i1 TINYINT, i2 TINYINT UNSIGNED);
SQL 严格模式下, 尝试写入一个超出范围的值时抛错:
- MySQL> SET sql_mode = 'TRADITIONAL';
- MySQL> INSERT INTO t1 (i1, i2) VALUES(256, 256);
- ERROR 1264 (22003): Out of range value for column 'i1' at row 1
- MySQL> SELECT * FROM t1;
- Empty set (0.00 sec)
以下是非严格模式下进行裁剪存储的情况:
- 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 for column 'i1' at row 1 |
- | Warning | 1264 | Out of range value for column 'i2' at row 1 |
- +---------+------+---------------------------------------------+
- MySQL> SELECT * FROM t1;
- +------+------+
- | i1 | i2 |
- +------+------+
- | 127 | 255 |
- +------+------+
上述表现同样会出现在涉及到对列进行转换修改的一些操作上, 比如 ALTER TABLE,LOAD DATA,UPDATE 以及使用 INSERT 同时插入多行数据时. 严格模式下会抛错失败, 非严格模式下值会进行裁剪. 但失败的情况不尽相同, 如果是事务类型的表, 会整个全失败, 其他情况根据具体的值会部分成功, 部分失败.
进行数字计算时如果有溢出, 也会抛错, 比如对于 BIGINT 其最大值为 9223372036854775807, 因为 MySQL 中默认对数字类型是有符号类型, 如下操作会抛错,
- MySQL> SELECT 9223372036854775807 + 1;
- ERROR 1690 (22003): BIGINT value is out of range 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> SET sql_mode = '';
- Query OK, 0 rows affected (0.00 sec)
- MySQL> SELECT CAST(0 AS UNSIGNED) - 1;
- ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(cast(0 as unsigned) - 1)'
除非开启了 NO_UNSIGNED_SUBTRACTION:
- MySQL> SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
- MySQL> SELECT CAST(0 AS UNSIGNED) - 1;
- +-------------------------+
- | CAST(0 AS UNSIGNED) - 1 |
- +-------------------------+
- | -1 |
- +-------------------------+
总结
对于整型或浮点型, 可指定 AUTO_INCREMTN 属性. 指定该属性性, 将不能接收负值. 同时 CHECK 属性与该属性冲突, 也不能同时使用. 但对于 FLOAT 和 DOUBLE,AUTO_INCREMENT 属性的支持将逐渐废弃掉, 实际使用时尽量避免.
对于需要精确数值的场合, 使用 DECIMAL, 比如涉及金钱的情况.
对于整形, 展示宽度不是其存储的值范围, 只用来格式化.
- Chapter 11 Data Types
- Difference between numeric, float and decimal in SQL Server
- Floating Point Numbers
- What does INT(5) in MySQL mean?
- MySQL: Why specify display width without using zerofill
- PHP & MySQL: Year 2038 Bug: What is it? How to solve it?
来源: https://www.cnblogs.com/Wayou/p/mysql_data_type_number.html