三：MySQL中的数据类型 - 系统的撸一遍MySQL

2019独角兽企业重金招聘Python工程师标准>>>

MySQL中但数据类型主要分为 数值类型、字符串类型、日期时间类型，随着MySQL版本升级，往往会对一些数据类型进行优化。

数值类型

整型

数据类型	字节	有符号	无符号
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT	4	-2147483648 ~ 2147483647	0 ~ 4294967295
BITINT	8	-9233372036854775808 ~ 9223372036854775807	0 ~ 8446744073709551615

数值宽度

整型的数据类型可以指定数值的宽度比如：

//默认的INT型 字宽为11
INT(11)
//指定宽度为4的时候 如果配合ZEROFILL使用 如果不足4位将会在前面补0
INT(4)

有符号和无符号

默认情况下如果没有指定是否有符号则默认为有符号（SIGNED），如果指定ZEROFILL会自动设置为无符号（UNSIGNED）。

AUTO_INCREMENT

整型可以设置 AUTO_INCREMENT 属性保持该字段可以自动增长，该字段应该设置为主键约束或增加唯一索引并且将其设置为NOT NULL。

浮点型

数据类型	字节
float	4
double	8

精度

浮点数可以指定 整数位和小数位，例如：

//如果没有设置精度 则按真实数值进行存储，如果指定精度则根据精度四舍五入
create table test(
`float_def` float,
`float_var` float(6, 2),
`double_var` double(6, 2)
)

定点数

数据类型	字节
DECIMAL（M，D）	M+2

精度

定点数也可以指定精度，例如：

//如果没有指定精度，默认定义为：DECIMAL(10,0)
//如果插入数值精度高于定义精度，则四舍五入同时出发一个Warning
create table decTable (
dec_def decimal,
dec_var decimal(5, 2)
)
//比如如下方法插入，插入结果 dec_var = 4.57
insert into decTable values(10, 4.567);

BIT型

数据类型	字节
BIT（M）	1 - 8

bit型以二进制位进行存储 BIT(1)表示1位，最大支持64位也就是8个字节长度。

查看BIT类型的字段可以使用 HEX函数 以十六进制的方式查看，或者BIN函数以二进制方式查看。

日期时间类型

数据类型	字节	最小值	最大值
DATE	4	1000-01-01	9999-12-31
DATETIME	8	1000-01-01 00:00:00	9999-12-31 23:59:59
TIME	3	-838:59:59	838:59:59
YEAR	1	1901	2155
TIMESTAMP	4	19700101080001	2038年

DATETIME 类型 为 DATE 和 TIME 类型的结合。

任何YYYYMMDDHHMMSS类型的数据只要数值符合合法日期，都可以进行插入无论用什么符号分隔。

TIMESTAMP 与 DATETIME 区别

TIMESTAMP 可以增加 CURRENT_TIMESTAMP 为默认值，当插入为NULL的时候自动设置为系统当前时间。

TIMESTAMP与时区相关，会根据MySQL设置的时区进行日期的转换。

TIMESTAMP 与 DATETIME 的取值范围也不同 TIMESTAMP 支持但范围较小。

字符串类型

数据类型	描述
CHAR(M)	M 为 0 － 255
VARCHAR(M)	M 为 0 － 65535，UTF8编码 为 0 － 65535/3
TINYBLOB	0 - 255
MEDIUMBLOB	0- 65535
BLOB	0 - 167772150
LONGBLOB	0 - 4294967295
TINYTEXT	0 - 255
TEXT	0 - 65535
MEDIUMTEXT	0 - 167772150
LONGTEXT	0 - 4294967295
VARBINARY(M)	0 - M
BINARY(M)	0 - M

CHAR 与 VARCHAR 区别

首先如下例子：

//表示 长度为4，如果插入小于4个字符的将在结尾进行空格补齐，并在查询时去掉结尾空格
CHAR(4)
//表示 最大长度为4，如果插入小于4个字符，则长度为插入的字符书，查询时不会去掉结尾空格
VARCHAR(4)

其次，VARCHAR会根据字段的编码类型决定其长度范围。

不同存储引擎下CHAR于VARCHAR是用方式的不同

	MyISAM	InnoDB	Memory
CHAR	全部CHAR为静态表，效率高	浪费空间
VARCHAR	包含VARCHAR为动态表，有碎片	节省空间，最小化IO	VARCHAR作为CHAR处理

TEXT于BLOB

用于存储大文本，TEXT保存字符串，BLOB保存二进制数据，频繁删改会导致空洞纪录影响性能。

BINARY类型

BINARY 与 VARBINARY的区别 与CHAR 与 VARCHAR的区别相同。

但是BINARY类型当插入长度小于定义长度会在结尾补齐0x00，这是与CHAR类型的一个区别

其他类型

ENUM类型

枚举类型需要在定义的时候指定取值范围，并且插入的内容必须在定义的范围之内，否则会默认为枚举范围的第一个。

枚举类型 占用 空间为 1 － 2个字节，分别对应 0 － 255 和 0 － 65535个枚举值的范围。

SET类型

SET类型与枚举类型类似，都需要在定义字段的时候指定取值范围，区别是SET类型可以在插入的时候同时插入多个取值范围内的值。

SET类型 占用空间为 1 － 8个字节，分别对应 0 － 64 位，其中每一个二进制位表示一个成员值，也就是说最多可以支持64个成员值。

转载于:https://my.oschina.net/fuckphp/blog/733739