MySQL数据类型详解-优快云博客

本文链接：https://blog.youkuaiyun.com/iteye_15734/article/details/82172700

MySQL列类型分为3大类：数值类型、日期和时间类型、字符串类型。

一、数值类型

如无特别说明， M 表示最大显示宽度。最大有效显示宽度是255 。显示宽度与存储大小或类型包含的值的范围无关。

定义	存储需求	数据范围		说明
定义	存储需求	无符号	有符号	说明
BIT[(M )]	约( M +7)/8B			位字段类型。M 表示每个值的位数，范围为从1 到64； M 默认值为1 。
TINYINT[(M )] [UNSIGNED] [ZEROFILL]	1B	0 ~255	-128 ~127	很小的整数。
BOOL ，BOOLEAN				TINYINT(1) 的同义词。zero 值为false；非zero 值为true。
SMALLINT[(M )] [UNSIGNED] [ZEROFILL]	2B	0 ~65535	-32768 ~32767	小的整数。
MEDIUMINT[(M )] [UNSIGNED] [ZEROFILL]	3B	0 ~16777215	-8388608 ~8388607	中等大小的整数。
INT[(M )] [UNSIGNED] [ZEROFILL]	4B	0 ~4294967295	-2147483648 ~2147483647	普通大小的整数。
INTEGER[(M )] [UNSIGNED] [ZEROFILL]				INT 的同义词。
BIGINT[(M )] [UNSIGNED] [ZEROFILL]	8B	0 ~18446744073709551615	-9223372036854775808 ~9223372036854775807	大整数。
FLOAT[(M ,D )] [UNSIGNED] [ZEROFILL]	4B	1.175494351E-38 ~3.402823466E+38	-3.402823466E+38 ~-1.175494351E-38 、0	小( 单精度) 浮点数。 M 是小数纵位数，D 是小数点后面的位数。如果M 和D 被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7 位小数位。
DOUBLE[(M ,D )] [UNSIGNED] [ZEROFILL]	8B
DOUBLE PRECISION[(M ,D )] [UNSIGNED] [ZEROFILL] , REAL[(M ,D )] [UNSIGNED] [ZEROFILL]				DOUBLE 的同义词。
FLOAT(p ) [UNSIGNED] [ZEROFILL]	如果0 <= p <= 24 为4 B, 如果25 <= p <= 53 为8 B			浮点数。p 表示精度（以位数表示），但MySQL 只使用该值来确定是否结果列的数据类型为FLOAT 或DOUBLE 。如果p 为从0 到24 ，数据类型变为没有M 或D 值的FLOAT 。如果p 为从25 到53 ，数据类型变为没有M 或D 值的DOUBLE 。结果列范围与本节前面描述的单精度FLOAT 或双精度DOUBLE 数据类型相同。
DECIMAL[(M [,D ])] [UNSIGNED] [ZEROFILL]	变长
DEC[(M [,D ])] [UNSIGNED] [ZEROFILL] , NUMERIC[(M [,D ])] [UNSIGNED] [ZEROFILL] , FIXED[(M [,D ])] [UNSIGNED] [ZEROFILL]				DECIMAL 的同义词。

二、日期和时间类型

定义	存储需求	支持的范围	“ 零 ”值	说明
DATETIME	8B	'1000-01-01 00:00:00' ~ '9999-12-31 23:59:59'	'0000-00-00 00:00:00'	同时包含日期和时间信息；允许保存日/ 月和日是零的日期。
DATE	3B	'1000-01-01' ~ '9999-12-31'	'0000-00-00'	仅日期值；允许保存日/ 月和日是零的日期。
TIMESTAMP	4B	不能早于1970 或晚于2037	00000000000000	列类型的属性不固定，取决于MySQL 版本和服务器运行的SQL 模式。
TIME	3B	'-838:59:59' ~ '838:59:59'	'00:00:00'	以'HH:MM:SS' 格式检索和显示TIME 值。无效TIME 值被转换为'00:00:00'。
YEAR	1B	1901 ~ 2155	0000	表示年。单字节类型。以YYYY 格式检索和显示YEAR 值。非法YEAR 值被转换为0000 。

1. 每个时间类型有一个有效值范围和一个“零 ”值，当指定不合法的MySQL 不能表示的值时使用“零 ”值。
如果不想在日期中出现零，可以使用NO_ZERO_IN_DATE SQL 模式。
2. 如果试图插入一个不合法的日期，MySQL 将给出警告或错误。
3. 可以使用ALLOW_INVALID_DATES SQL 模式让MySQL 接受某些日期。
4. 允许将'0000-00-00' 保存为“伪日期”( 如果不使用NO_ZERO_DATE SQL 模式) ，这在某些情况下比使用NULL 值更方便( 并且数据和索引占用的空间更小) 。

5. 可以使用任何常见格式指定DATETIME 、DATE 和TIMESTAMP 值：

'YYYY-MM-DD HH:MM:SS' 或'YY-MM-DD HH:MM:SS' 格式的字符串。

'YYYY-MM-DD' 或'YY-MM-DD' 格式的字符串。

'YYYYMMDDHHMMSS' 或'YYMMDDHHMMSS' 格式的没有间割符的字符串。

'YYYYMMDD' 或'YYMMDD' 格式的没有间割符的字符串。

YYYYMMDDHHMMSS 或YYMMDDHHMMSS 格式的数字。

YYYYMMDD 或YYMMDD 格式的数字。

无效DATETIME 、DATE 或者TIMESTAMP 值被转换为相应类型的“零 ”值。

6. 可以用各种格式指定TIME 值：

'D HH:MM:SS.fraction' 格式的字符串。

'HHMMSS' 格式的没有间割符的字符串。

HHMMSS 格式的数值。

超出TIME 范围但合法的值被裁为范围最接近的端点。

三、字符串类型：

定义	存储需求	长度	存储（值）	说明
CHAR(M )	M 个字节，0 <= M <= 255	列长度固定为创建表时声明的长度。长度可以为从0 到255 的任何值。	当保存CHAR 值时，在它们的右边填充空格以达到指定的长度。	声明的长度表示你想要保存的最大字符数。当检索到CHAR 值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。
VARCHAR(M )	L +1 个字节，其中L <= M 且0 <= M <= 65535	列中的值为可变长字符串。长度可以指定为0 到65,535 之间的值。	值保存时不进行填充，只保存需要的字符数，另加一个字节来记录长度( 如果列声明的长度超过255 ，则使用两个字节) 。	声明的长度表示你想要保存的最大字符数。
BINARY(M )	M 个字节，0 <= M <= 255	长度是字节长度。	保存BINARY 值时，在它们右边填充值以达到指定长度。	存储二进制字符串。插入值时在右侧添加0x00 on ，并且选择时不删除尾部的字节。比较时所有字节很重要，包括ORDER BY 和DISTINCT 操作。
VARBINARY(M )	L +1 个字节，其中L <= M 且0 <= M <= 255	长度是字节长度。		存储二进制字符串。插入时不填充字符，选择时不裁剪字节。比较时所有字节很重要，包括ORDER BY 和DISTINCT 操作。
BLOB	L +2 个字节，其中L < 2¹⁶	有4 种BLOB 类型：TINYBLOB 、BLOB 、MEDIUMBLOB 和LONGBLOB 。它们只是可容纳值的最大长度不同。	二进制大对象，可以容纳可变数量的数据。	没有字符集，并且排序和比较基于列值字节的数值值。
TEXT	L +2 个字节，其中L < 2¹⁶	有4 种TEXT 类型：TINYTEXT 、TEXT 、MEDIUMTEXT 和LONGTEXT 。这些对应4 种BLOB 类型，有相同的最大长度和存储需求。	非二进制字符串( 字符字符串) 。	有一个字符集，并且根据字符集的校对规则对值进行排序和比较。
TINYBLOB , TINYTEXT	L +1 个字节，其中L < 2⁸
MEDIUMBLOB , MEDIUMTEXT	L +3 个字节，其中L < 2²⁴
LONGBLOB , LONGTEXT	L +4 个字节，其中L < 2³²
ENUM('value1 ','value2 ',...)	1 或2 个字节，取决于枚举值的个数( 最多65,535 个值)		是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。	枚举最多可以有65,535 个元素。
SET('value1 ','value2 ',...)	1 、2 、3 、4 或者8 个字节，取决于set 成员的数目( 最多64 个成员)		是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。	最多可以有64 个不同的成员。

其它数据库与MySQL的类型对应关系：

其它数据库类型	MySQL 类型
BOOL ,	TINYINT
BOOLEAN	TINYINT
CHAR VARYING(M )	VARCHAR(M )
DEC	DECIMAL
FIXED	DECIMAL
FLOAT4	FLOAT
FLOAT8	DOUBLE
INT1	TINYINT
INT2	SMALLINT
INT3	MEDIUMINT
INT4	INT
INT8	BIGINT
LONG VARBINARY	MEDIUMBLOB
LONG VARCHAR	MEDIUMTEXT
LONG	MEDIUMTEXT
MIDDLEINT	MEDIUMINT
NUMERIC	DECIMAL