「MySQL」- 数据类型

  CREATED BY JENKINSBOT

在 MySQL 中,支持众多的数据类型,主要分为以下几类:
1)数值类型(Numeric)
2)日期、时间类型(Date and Time)
3)字符串类型(String)
4)空间数据类型(Spatial Data Types)

本文描述数据类型时用到了以下约定:

  • M – 表示整数类型的最大显示宽度。对于浮点(floating-point)定点(fixed-point)类型,M代表可以存储的总位数(精度(precision))。对于字符串类型,M是最大长度。因此,M允许的最大值取决于数据类型。
  • D – 用于浮点定点类型,代表小数点后面的数字位数(刻度(scale))。可能的最大值为30,但不应大于M-2。
  • fsp – 适用于TIME,DATETIME和TIMESTAMP类型,表示小数秒精度;也就是秒数后的小数点后的数字位数。fsp值(如果给定)必须在0到6之间。值为0表示没有小数部分。如果省略,默认精度为0。(与标准SQL默认值6不同,与以前的MySQL版本兼容)。
  • 方括号([和])表示类型定义中的可选部分。

数值类型(Numeric Types)

概述

如上所述,M表示整数类型的最大显示宽度。最大显示宽度为255。显示宽度与类型的范围无关。对于浮点和定点类型,M是可以存储的总数(即精读)。

如果为数字列指定ZEROFILL,MySQL会自动将UNSIGNED属性添加到该列。

数值类型允许UNSIGNED和SIGNED,这些数据类型是默认SIGNED的。

SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的别名。在整型列的定义中,SERIAL DEFAULT VALUE是NOT NULL AUTO_INCREMENT UNIQUE的别名。

当整数类型做减法的时候,如果其中一个类型为UNSIGNED,那么除非启用了NO_UNSIGNED_SUBTRACTION SQL模式,否则结果是无符号的。

整数类型

类型 别名 存储长度 有符号长度 无符号长度 描述
BIT(M)   M     一个位值类型。 M表示每个值的位数
TINYINT   1字节 -128 ~ 127 0 ~ 255 数值范围较小时使用,,比如 订单状态(状态值)、性别等
BOOL BOOLEAN 1字节     等价与TINYINT(1)。
SMALLINT   2字节 -32768 ~ 32767 0 ~ 65535  
MEDIUMINT   3字节 -8388608 ~ 8388607 0 ~ 16777215 相对较大的数据
INT INTEGER 4字节 -2147483648 ~ 2147483647 0 ~ 4294967295 正常大小的整数值
BIGINT   8字节 -9233372036854775808 ~ 9223372036854775807 0 ~ 18446744073709551615 极大整数值,比如身份证,或者手机号码
SERIAL          

关于BIGINT列应该注意的一些事情:

  • 所有算术运算使用带符号的BIGINT或DOUBLE值进行计算,因此除了位函数外,不应使用大于9223372036854775807(63位)的无符号大整数!如果这样做,结果中的最后一位数字可能会错误,因为将BIGINT值转换为DOUBLE时会出现舍入误差。MySQL能处理以下几种情况中的BIGINT:1)当使用整数在BIGINT列中存储大的无符号值时。。2)在MIN(col_name)或MAX(col_name)中,col_name指的是BIGINT列。3)当使用操作符(+, – ,*等等)时,其中两个操作数都是整数。
  • 通过使用字符串向BIGINT列中保存精确整数值。在这种情况下,MySQL执行字符串到数字的隐式转换,不涉及中间双精度表示。
  • 当两个操作数都是整数值时, – ,+和*运算符使用BIGINT算术。这意味着如果您乘以两个大整数(或返回整数的函数的结果),则当结果大于9223372036854775807时,可能会得到意想不到的结果。

关于BOOLEAN中应该注意的一些问题:

  • 在IF()中,0被视为FALSE,非0被视为TRUE。
  • TRUE和FALSE仅仅是1和0的别名,因此其他的数值(比如:2,3,4,5等等)即不为1,也不为0。

浮点数类型

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]
字节:4字节
有符号范围:(-3.402823466 E+38 ~ -1.175494351 E-38),0,(1.175 494 351 E-38 ~ 3.402 823 466 351 E+38)
无符号范围:0,(1.175 494 351 E-38,3.402 823 466 E+38)

单精度浮点数值,精确到大约7位小数位。用于存储小数。有符号和无符号的取值范围是基于IEEE标准的理论限制,实际的取值与硬件和操作系统有关系。M是数字的总数,D是小数点后面的位数。如果省略M和D,则将值存储到硬件允许的限制。

如果指定UNSIGNED,则不允许负值。

使用FLOAT可能发生一些意想不到的问题,因为MySQL中的所有计算都以双精度完成。详细内容可以查看Section B.5.4.7, “Solving Problems with No Matching Rows”

FLOAT(p) [UNSIGNED] [ZEROFILL]
浮点数。
p表示精度位,但MySQL仅使用此值来确定是否对结果数据类型使用FLOAT或DOUBLE:

  • 如果p从0到24,数据类型变为FLOAT,不带M或D值。
  • 如果p从25到53,数据类型变为DOUBLE,没有M或D值。

结果列的范围与本节描述的单精度FLOAT或双精度DOUBLE数据类型相同。

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]
字节:8字节
有符号范围:(1.797693134862315 7 E+308 ~ 2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
无符号范围:0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308)
双精度浮点数值。用于存储更大的小数,表示的范围更大。有符号和无符号的取值范围是基于IEEE标准的理论限制,实际的取值与硬件和操作系统有关系。M是数字的总数,D是小数点后面的位数。如果省略M和D,则将值存储到硬件允许的限制。

M是数字的总数(精度),D是小数点后面的位数(刻度)。如果省略M和D,则将值存储到硬件允许的限制。双精度浮点数精确到15位小数位。

DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL]
REAL[(M,D)] [UNSIGNED] [ZEROFILL]
这些类型是DOUBLE的同义词。例外:如果SQL模式中REAL_AS_FLOAT被启用,REAL是FLOAT而不是DOUBLE的同义词。

定点类型

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]
一个包装的“精确”的定点数。 M是数字的总数(精度),D是小数点后的数字(刻度)。小数点和符号(用于负数)不在M中计数。如果D为0,则值不具有小数点或小数部分。 DECIMAL的最大位数(M)为65.支持的小数(D)的最大数量为30.如果省略D,默认值为0.如果省略M,默认值为10。

如果指定UNSIGNED,则不允许负值。

使用DECIMAL列的所有基本计算(+, – ,*,/)的精度为65位数。

DEC[(M[,D])] [UNSIGNED] [ZEROFILL]
NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]
FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]
与DECIMAL是同义的。FIXED用于与其他数据库兼容。

日期、时间类型(Date and Time)

对空间数据的扩展(Extensions for Spatial Data)

JSON数据类型(The JSON Data Type)

从MySQL 5.7.8开始,表的列也支持JSON数据类型。

JSON类型的列不能被索引。可以通过从JSON列中提取的标量值,生成新列,在新列上创建索引来处理此限制

数据类型选择上的建议

更小的数据类型

简单的数据类型

减少NULL的使用

参考文献

MySQL 5.7 Reference Manual / Data Types:https://dev.mysql.com/doc/refman/5.7/en/data-types.html
https://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html
https://dev.mysql.com/doc/refman/5.7/en/json.html