MySQL 列数据类型(1)

原创

Professor哥 2014-09-05 00:06:30 博主文章分类：P哥一技 & Secure ©著作权

文章标签 mysql sql 数据库数据类型 文章分类 服务器

©著作权归作者所有：来自51CTO博客作者Professor哥的原创作品，请联系作者获取转载授权，否则将追究法律责任

第11章：列类型

11.1. 列类型概述

11.1.1. 数值类型概述

11.1.2. 日期和时间类型概述

11.1.3. 字符串类型概述

11.2. 数值类型

11.3. 日期和时间类型

11.3.1. DATETIME、DATE和TIMESTAMP类型

11.3.2. TIME类型

11.3.3. YEAR类型

11.3.4. Y2K事宜和日期类型

11.4. String类型

11.4.1. CHAR和VARCHAR类型

11.4.2. BINARY和VARBINARY类型

11.4.3. BLOB和TEXT类型

11.4.4. ENUM类型

11.4.5. SET类型

11.5. 列类型存储需求

11.6. 选择正确的列类型

11.7. 使用来自其他数据库引擎的列类型

MySQL支持多种列类型：数值类型、日期/时间类型和字符串(字符)类型。本章首先对这些列类型进行了概述，然后更加详细地描述了各种列的类型，以及列类型存储需求的总结。概述很简单。关于具体列类型的详细信息应查阅详细的描述，例如指定值时允许使用的格式。

MySQL支持处理空间数据的扩展名。关于空间类型的信息参见第19章：MySQL中的空间扩展。

几种列类型描述使用了下述惯例：

· M

表示最大显示宽度。最大有效显示宽度是255。

· D

适用于浮点和定点类型，并表示小数点后面的位数。最大可能的值是30，但不应大于M-2。

· 方括号(‘[’和‘]’)表示可选部分。

11.1. 列类型概述

11.1.1. 数值类型概述

11.1.2. 日期和时间类型概述

11.1.3. 字符串类型概述

11.1.1. 数值类型概述

下面为数值列类型的概述。详细信息参见11.2节，“数值类型”。列存储需求参见11.5节，“列类型存储需求”。

M指示最大显示宽度。最大有效显示宽度是255。显示宽度与存储大小或类型包含的值的范围无关，相关描述见11.2节，“数值类型”。

如果为一个数值列指定ZEROFILL，MySQL自动为该列添加UNSIGNED属性。

SERIAL是BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE的一个别名。

在整数列定义中，SERIAL DEFAULT VALUE是NOT NULL AUTO_INCREMENT UNIQUE的一个别名。

警告：应当清楚，当使用在整数值（其中一个是UNSIGNED类型）之间使用减号时，结果是无符号。参见12.8节，“Cast函数和操作符”。

· BIT[(M)]

位字段类型。M表示每个值的位数，范围为从1到64。如果M被省略，默认为1。

· TINYINT[(M)] [UNSIGNED] [ZEROFILL]

很小的整数。带符号的范围是-128到127。无符号的范围是0到255。

· BOOL，BOOLEAN

是TINYINT(1)的同义词。zero值被视为假。非zero值视为真。

在将来，将根据标准SQL引入完全布尔类型的处理。

· SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

小的整数。带符号的范围是-32768到32767。无符号的范围是0到65535。

· MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

中等大小的整数。带符号的范围是-8388608到8388607。无符号的范围是0到16777215。

· INT[(M)] [UNSIGNED] [ZEROFILL]

普通大小的整数。带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295。

· INTEGER[(M)] [UNSIGNED] [ZEROFILL]

这是INT的同义词。

· BIGINT[(M)] [UNSIGNED] [ZEROFILL]

大整数。带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615。

应清楚BIGINT列的下述内容：

o 使用带符号的BIGINT或DOUBLE值进行所有算法，因此除了位函数，不应使用大于9223372036854775807(63位)的无符号的大整数! 如果这样做，结果中的最后几位可能出错，这是由于将BIGINT值转换为DOUBLE进行四舍五入时造成的错误。

MySQL可以在以下情况下处理BIGINT：

§ 当使用整数在一个BIGINT列保存大的无符号的值时。

§ 在MIN(col_name)或MAX(col_name)中，其中col_name指BIGINT列。

§ 使用操作符(+，-，*等等)并且两个操作数均为整数时。

o 总是可以使用一个字符串在BIGINT列中保存严格整数值。在这种情况下，MySQL执行字符串-数字转换，其间不存在双精度表示。

o 当两个操作数均为整数值时，-、+和* 操作符使用BIGINT算法。这说明如果乘两个大整数(或来自返回整数的函数)，当结果大于9223372036854775807时，会得到意想不到的结果。

· FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

小(单精度)浮点数。允许的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38。这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些。

M是小数纵位数，D是小数点后面的位数。如果M和D被省略，根据硬件允许的限制来保存值。单精度浮点数精确到大约7位小数位。

如果指定UNSIGNED，不允许负值。

使用浮点数可能会遇到意想不到的问题，因为在MySQL中的所有计算用双精度完成。参见A.5.7节，“解决与不匹配行有关的问题”。

· DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

普通大小(双精度)浮点数。允许的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到 1.7976931348623157E+308。这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些。

M是小数总位数，D是小数点后面的位数。如果M和D被省略，根据硬件允许的限制来保存值。双精度浮点数精确到大约15位小数位。

如果指定UNSIGNED，不允许负值。

· DOUBLE PRECISION[(M,D)] [UNSIGNED] [ZEROFILL], REAL[(M,D)] [UNSIGNED] [ZEROFILL]

为DOUBLE的同义词。除了：如果SQL服务器模式包括REAL_AS_FLOAT选项，REAL是FLOAT的同义词而不是DOUBLE的同义词。

· FLOAT(p) [UNSIGNED] [ZEROFILL]

浮点数。p表示精度（以位数表示），但MySQL只使用该值来确定是否结果列的数据类型为FLOAT或DOUBLE。如果p为从0到24，数据类型变为没有M或D值的FLOAT。如果p为从25到53，数据类型变为没有M或D值的DOUBLE。结果列范围与本节前面描述的单精度FLOAT或双精度DOUBLE数据类型相同。

FLOAT(p)语法与ODBC兼容。

· 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同义词适用于与其它服务器的兼容性。

11.1.2. 日期和时间类型概述

本节综合讨论了临时列类型。详细信息，参见11.3节，“日期和时间类型”。列存储需求参见11.5节，“列类型存储需求”。

· DATE

日期。支持的范围为'1000-01-01'到'9999-12-31'。MySQL以'YYYY-MM-DD'格式显示DATE值，但允许使用字符串或数字为DATE列分配值。

· DATETIME

日期和时间的组合。支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。MySQL以'YYYY-MM-DD HH:MM:SS'格式显示DATETIME值，但允许使用字符串或数字为DATETIME列分配值。

· TIMESTAMP[(M)]

时间戳。范围是'1970-01-01 00:00:00'到2037年。

TIMESTAMP列用于INSERT或UPDATE操作时记录日期和时间。如果你不分配一个值，表中的第一个TIMESTAMP列自动设置为最近操作的日期和时间。也可以通过分配一个NULL值，将TIMESTAMP列设置为当前的日期和时间。

TIMESTAMP值返回后显示为'YYYY-MM-DD HH:MM:SS'格式的字符串，显示宽度固定为19个字符。如果想要获得数字值，应在TIMESTAMP 列添加+0。

注释：MySQL 4.1以前使用的TIMESTAMP格式在MySQL 5.1中不支持；关于旧格式的信息参见MySQL 4.1 参考手册。

· TIME

时间。范围是'-838:59:59'到'838:59:59'。MySQL以'HH:MM:SS'格式显示TIME值，但允许使用字符串或数字为TIME列分配值。

· YEAR[(2|4)]

两位或四位格式的年。默认是四位格式。在四位格式中，允许的值是1901到2155和0000。在两位格式中，允许的值是70到69，表示从1970年到2069年。MySQL以YYYY 格式显示YEAR值，但允许使用字符串或数字为YEAR列分配值。

11.1.3. 字符串类型概述

本节综合讨论了字符串列类型。详细信息参见11.4节，“String类型”。列存储需求参见11.5节，“列类型存储需求”。

在某些情况中，MySQL可以将一个字符串列更改为不同于CREATE TABLE或ALTER TABLE语句中所给出的类型。参见13.1.5.1节，“沉寂的列规格变更”。

MySQL 5.1字符串数据类型包括部分在MySQL 4.1之前的版本中没有的特性：

· 许多字符串数据类型的列定义可以包括指定字符集的CHARACTER SET属性，也可能包括校对规则。(CHARSET是CHARACTER SET的一个同义词）。这些属性适用于CHAR、VARCHAR、TEXT类型、ENUM和SET。例如：

· CREATE TABLE t

· (

· c1 CHAR(20) CHARACTER SET utf8,

· c2 CHAR(20) CHARACTER SET latin1 COLLATE latin1_bin

· );

该表定义创建了一个名为c1的列，具有一个utf8字符集和该字符集的默认校对规则，和一个名为c2的列以及latin1字符集和该字符集的二元校对规则。二元校对规则对大小写不敏感。

· MySQL 5.1用字符单位解释在字符列定义中的长度规范。(以前的一些MySQL版本以字节解释长度）。

· 对于CHAR、VARCHAR和TEXT类型，BINARY属性可以为列分配该列字符集的校对规则。

· 字符列的排序和比较基于分配给列的字符集。在以前的版本中，排序和比较基于服务器字符集的校对规则。对于CHAR和VARCHAR 列，你可以用BINARY属性声明列让排序和校对规则使用当前的字符代码值而不是词汇顺序。

关于MySQL 5.1中字符集的支持，参见第10章：字符集支持。

· [NATIONAL] CHAR(M) [BINARY| ASCII | UNICODE]

固定长度字符串，当保存时在右侧填充空格以达到指定的长度。M表示列长度。M的范围是0到255个字符。

注释：当检索CHAR值时尾部空格被删除。

如果想要将某个CHAR的长度设为大于255，执行的CREATE TABLE或ALTER TABLE语句将失败并提示错误：

mysql> CREATE TABLE c1 (col1 INT, col2 CHAR(500));

ERROR 1074 (42000): Column length too big for column 'col' (max = 255); use BLOB or TEXT instead

mysql> SHOW CREATE TABLE c1;

ERROR 1146 (42S02): Table 'test.c1' doesn't exist

CHAR是CHARACTER的简写。NATIONAL CHAR(或其等效短形式NCHAR)是标准的定义CHAR列应使用默认字符集的SQL方法。这在MySQL中为默认值。

BINARY属性是指定列字符集的二元校对规则的简写。排序和比较基于数值字符值。

列类型CHAR BYTE是CHAR BINARY的一个别名。这是为了保证兼容性。

可以为CHAR指定ASCII属性。它分配latin1字符集。

可以为CHAR指定UNICODE属性。它分配ucs2字符集。

MySQL允许创建类型CHAR(0)的列。这主要用于必须有一个列但实际上不使用值的旧版本的应用程序相兼容。当你需要只能取两个值的列时也很好：没有定义为NOT NULL的一个CHAR(0)列只占用一位，只可以取值NULL和''(空字符串)。

· CHAR

这是CHAR(1)的同义词。

· [NATIONAL] VARCHAR(M) [BINARY]

变长字符串。M 表示最大列长度。M的范围是0到65,535。(VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定。最大有效长度是65,532字节）。

注释：MySQL 5.1遵从标准SQL规范，并且不删除VARCHAR值的尾部空格。

VARCHAR是字符VARYING的简写。

BINARY属性是指定列的字符集的二元校对规则的简写。排序和比较基于数值字符值。

VARCHAR保存时用一个字节或两个字节长的前缀+数据。如果VARCHAR列声明的长度大于255，长度前缀是两个字节。

· BINARY(M)

BINARY类型类似于CHAR类型，但保存二进制字节字符串而不是非二进制字符串。

· VARBINARY(M)

VARBINARY类型类似于VARCHAR类型，但保存二进制字节字符串而不是非二进制字符串。

· TINYBLOB

最大长度为255(28–1)字节的BLOB列。

· TINYTEXT

最大长度为255(28–1)字符的TEXT列。

· BLOB[(M)]

最大长度为65,535(216–1)字节的BLOB列。

可以给出该类型的可选长度M。如果给出，则MySQL将列创建为最小的但足以容纳M字节长的值的BLOB类型。

· TEXT[(M)]

最大长度为65,535(216–1)字符的TEXT列。

可以给出可选长度M。则MySQL将列创建为最小的但足以容纳M字符长的值的TEXT类型。

· MEDIUMBLOB

最大长度为16,777,215(224–1)字节的BLOB列。

· MEDIUMTEXT

最大长度为16,777,215(224–1)字符的TEXT列。

· LONGBLOB

最大长度为4,294,967,295或4GB(232–1)字节的BLOB列。LONGBLOB列的最大有效(允许的)长度取决于客户端/服务器协议中配置最大包大小和可用的内存。

· LONGTEXT

最大长度为4,294,967,295或4GB(232–1)字符的TEXT列。LONGTEXT列的最大有效(允许的)长度取决于客户端/服务器协议中配置最大包大小和可用的内存。

· ENUM('value1','value2',...)

枚举类型。只能有一个值的字符串，从值列'value1'，'value2'，...，NULL中或特殊 ''错误值中选出。ENUM列最多可以有65,535个截然不同的值。ENUM值在内部用整数表示。

· SET('value1','value2',...)

一个设置。字符串对象可以有零个或多个值，每个值必须来自列值'value1'，'value2'，...SET列最多可以有64个成员。SET值在内部用整数表示。

11.2. 数值类型

MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。

BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。

作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型

字节

最小值

最大值

(带符号的/无符号的)

TINYINT

-128

127

255

SMALLINT

-32768

32767

65535

MEDIUMINT

-8388608

8388607

16777215

INT

-2147483648

2147483647

4294967295

BIGINT

-9223372036854775808

9223372036854775807

18446744073709551615

MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度。

显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。

当结合可选扩展属性ZEROFILL使用时，默认补充的空格用零代替。例如，对于声明为INT(5) ZEROFILL的列，值4检索为00004。请注意如果在整数列保存超过显示宽度的一个值，当MySQL为复杂联接生成临时表时会遇到问题，因为在这些情况下MySQL相信数据适合原列宽度。

所有整数类型可以有一个可选(非标准)属性UNSIGNED。当你想要在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值。

浮点和定点类型也可以为UNSIGNED。同数类型，该属性防止负值保存到列中。然而，与整数类型不同的是，列值的上范围保持不变。

如果为一个数值列指定ZEROFILL，MySQL自动为该列添加UNSIGNED属性。

对于浮点列类型，在MySQL中单精度值使用4个字节，双精度值使用8个字节。

FLOAT类型用于表示近似数值数据类型。SQL标准允许在关键字FLOAT后面的括号内选择用位指定精度(但不能为指数范围)。MySQL还支持可选的只用于确定存储大小的精度规定。0到23的精度对应FLOAT列的4字节单精度。24到53的精度对应DOUBLE列的8字节双精度。

MySQL允许使用非标准语法：FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)。这里，“(M,D)”表示该值一共显示M位整数，其中D位位于小数点后面。例如，定义为FLOAT(7,4)的一个列可以显示为-999.9999。MySQL保存值时进行四舍五入，因此如果在FLOAT(7,4)列内插入999.00009，近似结果是999.0001。

MySQL将DOUBLE视为DOUBLE PRECISION(非标准扩展)的同义词。MySQL还将REAL视为DOUBLE PRECISION(非标准扩展)的同义词，除非SQL服务器模式包括REAL_AS_FLOAT选项。

为了保证最大可能的可移植性，需要使用近似数值数据值存储的代码应使用FLOAT或DOUBLE PRECISION，不规定精度或位数。

DECIMAL和NUMERIC类型在MySQL中视为相同的类型。它们用于保存必须为确切精度的值，例如货币数据。当声明该类型的列时，可以(并且通常要)指定精度和标度；例如：

salary DECIMAL(5,2)

在该例子中，5是精度，2是标度。精度表示保存值的主要位数，标度表示小数点后面可以保存的位数。

在MySQL 5.1中以二进制格式保存DECIMAL和NUMERIC值。

标准SQL要求salary列能够用5位整数位和两位小数保存任何值。因此，在这种情况下可以保存在salary列的值的范围是从-999.99到999.99。

在标准SQL中，语法DECIMAL(M)等价于DECIMAL(M,0)。同样，语法DECIMAL等价于DECIMAL(M,0)，可以通过计算确定M的值。在MySQL 5.1中支持DECIMAL和NUMERIC数据类型的变量形式。M默认值是10。

DECIMAL或NUMERIC的最大位数是65，但具体的DECIMAL或NUMERIC列的实际范围受具体列的精度或标度约束。如果此类列分配的值小数点后面的位数超过指定的标度允许的范围，值被转换为该标度。(具体操作与操作系统有关，但一般结果均被截取到允许的位数）。

BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1到64。

要指定位值，可以使用b'value'符。value是一个用0和1编写的二进制值。例如，b'111'和b'100000000'分别表示7和128。参见9.1.5节，“位字段值”。

如果为BIT(M)列分配的值的长度小于M位，在值的左边用0填充。例如，为BIT(6)列分配一个值b'101'，其效果与分配b'000101'相同。

当要在一个数值列内保存一个超出该列允许范围的值时，MySQL的操作取决于此时有效的SQL模式。如果模式未设置，MySQL将值裁剪到范围的相应端点，并保存裁减好的值。但是，如果模式设置为traditional(“严格模式”)，超出范围的值将被拒绝并提示错误，并且根据SQL标准插入会失败。参见5.3.2节，“SQL服务器模式”。

如果INT列是UNSIGNED，列范围的大小相同，但其端点会变为到0和4294967295。如果你试图保存-9999999999和9999999999，以非严格模式保存到列中的值是0和4294967296。

如果在浮点或定点列中分配的值超过指定(或默认)精度和标度规定的范围，MySQL以非严格模式保存表示范围相应端点的值。

当MySQL没有工作在严格模式时，对于ALTER TABLE、LOAD DATA INFILE、UPDATE和多行INSERT语句，由于裁剪发生的转换将报告为警告。当MySQL工作在严格模式时，这些语句将失败，并且部分或全部值不会插入或更改，取决于是否表为事务表和其它因素。详情参见5.3.2节，“SQL服务器模式”。

11.3. 日期和时间类型

11.3.1. DATETIME、DATE和TIMESTAMP类型

11.3.2. TIME类型

11.3.3. YEAR类型

11.3.4. Y2K事宜和日期类型

表示时间值的DATE和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。TIMESTAMP类型有专有的自动更新特性，将在后面描述。

如果试图插入一个不合法的日期，MySQL将给出警告或错误。可以使用ALLOW_INVALID_DATES SQL模式让MySQL接受某些日期，例如'1999-11-31'。当你想要保存一个“可能错误的”用户已经在数据库中指定(例如，以web形式)用于将来处理的值时很有用。在这种模式下，MySQL只验证月范围为从0到12，日范围为从0到31。这些范围可以包括零，因为MySQL允许在DATE或DATETIME列保存日/月和日是零的日期。这在应用程序需要保存一个你不知道确切日期的生日时非常有用。在这种情况下，只需要将日期保存为'1999-00-00'或'1999-01-00'。如果保存此类日期，DATE_SUB()或DATE_ADD等需要完整日期的函数不会得到正确的结果。(如果你不想在日期中出现零，可以使用NO_ZERO_IN_DATE SQL模式)。

MySQL还允许将'0000-00-00'保存为“伪日期”(如果不使用NO_ZERO_DATE SQL模式)。这在某些情况下比使用NULL值更方便(并且数据和索引占用的空间更小)。

将sql_mode系统变量设置为相应模式值，可以更确切你想让MySQL支持哪种日期。参见5.3.2节，“SQL服务器模式”。

当使用日期和时间类型时应记住以下几点：

· MySQL以标准输出格式检索给定日期或时间类型的值，但它尽力解释你指定的各种输入值格式(例如，当你指定一个分配给或与日期或时间类型进行比较的值时)。只支持下面章节中描述的格式。期望你能提供有效值。如果你使用其它格式的值会发生意想不到的结果。

· 包含两位年值的日期会令人模糊，因为世纪不知道。MySQL使用以下规则解释两位年值：

o 70-99范围的年值转换为1970-1999。

o 00-69范围的年值转换为2000-2069。

· 尽管MySQL尝试解释几种格式的值，日期总是以年-月-日顺序(例如，'98-09-04')，而不是其它地方常用的月-日-年或日-月-年顺序(例如，'09-04-98'，'04-09-98')。

· 如果值用于数值上下文中，MySQL自动将日期或时间类型的值转换为数字，反之亦然。

· 当 MySQL遇到一个日期或时间类型的超出范围或对于该类型不合法的值时(如本节开始所描述)，它将该值转换为该类的“零”值。一个例外是超出范围的TIME值被裁剪到TIME范围的相应端点。

下面的表显示了各类“零”值的格式。请注意如果启用NO_ZERO_DATE SQL模式，使用这些值会产生警告。

列类型