MySQL 数据类型

 

常用数据类型

常用数据类型：数值型、日期时间型、字符串型（二进制）

数值类型

MySQL 的数值类型可以大致划分为两个类别，一个整数，另一个是浮点数或小数。许多不同的子类型对这些类别中的每一个都是可用的，每个子类型支持不同大小的数据，并且 MySQL 允许我们指定数值字段中的值是否有正负之分或者用零填补。

类型	大小（byte）	范围（有符号）	范围（无符号）	用途	应用场景
TNYINT	1	(-128,127)	(0,255)	小整数值	ENUMBOOL
SMALLINT	2	(-32768,32767)	(0,65535)	大整数值
MEDIUMINT	3	（-8388608，8388607）	（0，16777215）	大整数值
INT 或 INTEGER	4	(-2147483648,2147483647)	(0,4294967295)	大整数值	IP/TIMESTAMP
BIGINT	8	(-9233372036854775808,9233372036854775807)	(0,18446744073709551615)	极大整数值	autoinc_id
FLOAT	4			单精度、浮点数值	一般精度金额
DOUBLE	8			双精度、浮点数值	高精度金额
DECIMAL	对 DECIMAL(M,D),如果 M>D,为 M+2 否则为 D+2	依赖于 M 和 D 的值	小数值

INT 类型

• 在 MySQL 中支持的5个主要整数类型是 TINYINT、SMALLINT、MEDIUMINT，INT 和 BIGINT。这些类型在很大程度上都是相同的，只有它们存储的值大小是不相同。
• MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定长度。例如，指定一个字段的类型为 INT(6) ，就可以保证所包含数值少于 6 个值从数据库检索出来时能够自动地用空格填充。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。
• 万一我们需要对一个字段存储一个超出许可范围的数字，MySQL 会根据允许范围最接近它的一端截断后再进行存储。还有一个比较特别的地方是，MySQL 会不在不符合规定的值插入表前自动修改为0.
• UNSIGNED 修饰符规定字段只保存正值，因为不需要保存数值的正、负符号，可以在存储时节约一个『位』的空间。从而增大这个字段可以存储的值的范围。
• ZEROFILL 修饰符规定0（不是空格）可以用来填补输出的值，使用这个修饰符可以阻止 MySQL 数据库存储负值。 > 其中 TINYINT(3), 3 代表是数值的宽度。

FLOAT DOUBLE & DECIMAL 类型

• MySQL 支持三个浮点类型是 FLOAT 、DOUBLE 和 DECIMAL 类型。FLOAT 数值类型用于表示单精度浮点数值，而 DOUBLE 数值类型用于表示双精度浮点数值。
• 与整数一样，这些类型也带有附加参数：一个显示宽度指示器和一个小数点指示器，比如语句 FLOAT(7.3)规定显示的值不会超过7位数字，小数点后带有3位数字。
• 对于小数点后面的位数超过允许范围的值，MySQL 会自动将它四舍五入为最接近它的值，再插入。
• DECIMAL 数据类型用于进度要求非常高的计算中，这种类型允许指定数值的精度和计算方法作为选择参数。精度在这里指为这个值保存有效的数字的总个数，而计算方法表示小数点后数字的位数。比如语句 DECIMAL(7,3)规定了存储的值不会超过7位数字，并且小数点后不会超过3位。
• 忽略 DECIMAL 数据类型的精度和技术方法修饰符将会使 MySQL 数据库把所有标识为这个数据类型的字段进度设置为10，计算方法设置为0.
• UNSIGNED 和 ZEROFILL 修饰符也可以被 FLOAT 、DOUBLE 和 DECIMAL 数据类型使用。并且效果与 INT 数据类型相同。

字符类型

MySQL 提供了8个基本的字符串类型，可以存储的范围从简单的一个字符到巨大的文本或二进制字符串数据。

类型	大小(byte)	用途
CHAR	0-255	定长字符串
VARCHAR	0-255(65535)	变长字符串
TINTBLOB	0-255	不超过255个字符串的二进制字符串
TINYTEXT	0-255字节	短文本字符串
BLOB	0-65535	二进制形式的长文本数据
text	0-65535	长文本数据
MEDIUMTEXT	0-16777215	二进制形式的中等长度文本数据
LONGBLOB	0-4294967295	二进制形式的极大文本数据
LONGTEXT	0-4294967295	极大文本数据

CHAR 和 VARCHAR 类型

• CHAR 类型用于定长字符串，并且必须在圆括号内用一个大小修饰符来定义。这个大小修饰符的范围从0-255.比指定长度的值将被截断，而比指定长度小的值将会用空格做填补。
• CHAR 类型可以使用 BINARY 修饰符。当用于比较运算时，这个修饰符使 CHAR 以二进制形式参与运算，而不是以传统的区分大小写的方式。
• CHAR 类型是一个变体的 VARCHAR 类型。它是一种可变长度的字符串类型，并且也必须带有一个范围在0-255之间的指示器。CHAR 和 VARCHAR 不同之处在于 MySQL 数据库处理这个指示器的方式：CHAR 把这个大小视为值的大小，在长度不足的情况下就用空格不足。而 VARCHAR 类型把它视为最大值并且只使用存储字符串实际需要的长度（增加一个额外字节来存储字符串本身的长度）来存储。所以短于指示器长度的 VARCHAR 类型不会被空格填补，但长于指示器的值仍会被截断。
• 因为 VARCHAR 类型可以根据实际内容动态改变存储值的长度，所以在不能确定字符需要多少字符时使用 VARCHAR 类型可以大大地节约磁盘空间、提高存储效率。
• VARCAHR 类型在使用 BINARY 修饰符时与 CHAR 类型完全相同。

1. 限制规则

字段的限制在字段定义的时候有以下规则：

• 存储限制

VARCHAR 字段是将实际数据单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字段），因此最大长度不能超过65535.

• 编码长度限制

字符类型若为 gbk，每个字符最多占2个字节，最大长度不能超过32766；
字符类型若为 utf8，每个字符最多占3个字节，最大长度不能超过21845；
若定义的时候超过上述限制，这 varchar 字段会被强制转为 text 类型，并产生 warning。

• 行长度限制

导致实际应用中varcahr长度限制的是一个行定义的长度。MySQL 要求一个行的定义长度不能超过65535.若定义的表长度超过了这个值，则提示 ERROR 1118（42000） Row size too large.The maximum row size for the used table type,not counting BLOBs,is 65535 .You have t change some cloumns to TEXT or BLOB.

TEXT 和 BLOB 类型

• 对于字段长度要求超过255个的情况下，MySQL 提供了 TEXT 和 BLOB 两种类型。根据存储数据的大小，它们都有不同的子类型。这些大型的数据用于存储文本块或图像、声音文件等二进制数据类型。
• TEXT 和 BLOB 类型在分类和比较山存在区别。BLOB 类型区分大小写，而 TEXT 不区分大小写。大小修饰符不用于各种 BLOB 和 TEXT 子类型。比指定类型支持的最大范围大的值将被自动截短。

日期和时间类型

• 处理日期和时间类型的值时，MySQL 带有5个不同的数据类型可供选择。它们可以被分成简单的日期、时间类型、和混合日期、时间类型。根据要求的精度，子类型在每个分类中都可以使用，并且 MySQL 带有内置功能可以把多样化的输入格式变成一个标准格式。

类型	大小（字节）	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59/838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	8	1970-01-01 00:00:00/2037 年某时	YYYYMMDD HHMMSS	混合日期和时间值或时间戳