我的MYSQL学习心得（4）：数据类型上

最新推荐文章于 2024-09-13 22:38:14 发布

kuaileboy1989

最新推荐文章于 2024-09-13 22:38:14 发布

阅读量764

点赞数

分类专栏： mysql 文章标签： mysql

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

转自：http://mp.weixin.qq.com/s?__biz=MzA5ODM5MDU3MA==&mid=208562832&idx=2&sn=8c046d340c76395a271b1ecc0fc1c251&key=c468684b929d2be2480610bd2ce6430293918f5b05bb5f39b0823d3aff87789dda9e714c0c75f986ab621c03f0d35a9c&ascene=0&uin=MjQ4MDMyMzQyMg%3D%3D&devicetype=iMac+MacBookPro11%2C1+OSX+OSX+10.10.3+build(14D136)&version=11020012&pass_ticket=BE33sB4tFEx4FD33DB6S2vEoYBA8XxEEiYTVoyFEvaG6sZ8KAZgjJluN55Q9NUqm

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类型）之间使用减号时，结果是无符号。

· 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中的所有计算用双精度完成。

· 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语句中所给出的类型。

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属性声明列让排序和校对规则使用当前的字符代码值而不是词汇顺序。

· [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	1	-128	127
		0	255
SMALLINT	2	-32768	32767
		0	65535
MEDIUMINT	3	-8388608	8388607
		0	16777215
INT	4	-2147483648	2147483647
		0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807
		0	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)

在MySQL 5.1中以二进制格式保存DECIMAL和NUMERIC值。在该例子中，5是精度，2是标度。精度表示保存值的主要位数，标度表示小数点后面可以保存的位数。

标准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。

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

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

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

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

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

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支持哪种日期。

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

· 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模式，使用这些值会产生警告。

列类型	“零”值
DATETIME	’0000-00-00 00:00:00′
DATE	’0000-00-00′
TIMESTAMP	00000000000000
TIME	’00:00:00′
YEAR	0000

· “零”值是特殊值，但你可以使用表内显示的值显式保存或引用它们。你也可以使用值’0′或0来保存或引用，写起来更容易。

· MyODBC中使用的“零”日期或时间值在MyODBC 2.50.12和以上版本中被自动转换为NULL，因为ODBC不能处理此类值。

11.3.1. DATETIME、DATE和TIMESTAMP类型

11.3.1.1. 自MySQL 4.1以来的TIMESTAMP属性

DATETIME、DATE和TIMESTAMP类型是相关的。该节描述了它们的特征，它们的相似点和不同点。

当你需要同时包含日期和时间信息的值时则使用DATETIME类型。MySQL以’YYYY-MM-DD HH:MM:SS’格式检索和显示DATETIME值。支持的范围为’1000-01-01 00:00:00′到’9999-12-31 23:59:59′。(“支持”表示尽管先前的值可能工作，但没有保证)。

当你只需要日期值而不需要时间部分时应使用DATE类型。MySQL用’YYYY-MM-DD’格式检索和显示DATE值。支持的范围是’1000-01-01′到 ’9999-12-31′。

TIMESTAMP列类型的属性不固定，取决于MySQL版本和服务器运行的SQL模式。这些属性将在本节后面描述。

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

· ’YYYY-MM-DD HH:MM:SS’或’YY-MM-DD HH:MM:SS’格式的字符串。允许“不严格”语法：任何标点符都可以用做日期部分或时间部分之间的间割符。例如，’98-12-31 11:30:45′、’98.12.31 11+30+45′、’98/12/31 11*30*45′和’98@12@31 11^30^45′是等价的。

· ’YYYY-MM-DD’或’YY-MM-DD’格式的字符串。这里也允许使用“不严格的”语法。例如，’98-12-31′、’98.12.31′、’98/12/31′和’98@12@31′是等价的。

· ’YYYYMMDDHHMMSS’或’YYMMDDHHMMSS’格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，’19970523091528′和’970523091528′被解释为’1997-05-23 09:15:28′，但’971122129015′是不合法的(它有一个没有意义的分钟部分)，将变为’0000-00-00 00:00:00′。

· ’YYYYMMDD’或’YYMMDD’格式的没有间割符的字符串，假定字符串对于日期类型是有意义的。例如，’19970523′和’970523′被解释为 ’1997-05-23′，但’971332′是不合法的(它有一个没有意义的月和日部分)，将变为’0000-00-00′。

· YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，假定数字对于日期类型是有意义的。例如，19830905132800和830905132800被解释为 ’1983-09-05 13:28:00′。

· YYYYMMDD或YYMMDD格式的数字，假定数字对于日期类型是有意义的。例如，19830905和830905被解释为’1983-09-05′。

· 函数返回的结果，其值适合DATETIME、DATE或者TIMESTAMP上下文，例如NOW()或CURRENT_DATE。

无效DATETIME、DATE或者TIMESTAMP值被转换为相应类型的“零”值(’0000-00-00 00:00:00′、’0000-00-00′或者00000000000000)。

对于包括日期部分间割符的字符串值，如果日和月的值小于10，不需要指定两位数。’1979-6-9′与’1979-06-09′是相同的。同样，对于包括时间部分间割符的字符串值，如果时、分和秒的值小于10，不需要指定两位数。’1979-10-30 1:2:3′与’1979-10-30 01:02:03′相同。

数字值应为6、8、12或者14位长。如果一个数值是8或14位长，则假定为YYYYMMDD或YYYYMMDDHHMMSS格式，前4位数表示年。如果数字是6或12位长，则假定为YYMMDD或YYMMDDHHMMSS格式，前2位数表示年。其它数字被解释为仿佛用零填充到了最近的长度。

指定为非限定符字符串的值使用给定的长度进行解释。如果字符串为8或14字符长，前4位数表示年。否则，前2位数表示年。从左向右解释字符串内出现的各部分，以发现年、月、日、小时、分和秒值。这说明不应使用少于6字符的字符串。例如，如果你指定’9903′，认为它表示1999年3月，MySQL将在你的表内插入一个“零”日期值。这是因为年和月值是99和03，但日部分完全丢失，因此该值不是一个合法的日期。但是，可以明显指定一个零值来代表缺少的月或日部分。例如，可以使用’990300′来插入值’1999-03-00′。

在一定程度上，可以将一个日期类型的值分配给一个不同的日期类型。但是，值可能会更改或丢失一些信息：

· 如果你为一个DATETIME或TIMESTAMP对象分配一个DATE值，结果值的时间部分被设置为’00:00:00′，因为DATE值未包含时间信息。

· 如果你为一个DATE对象分配一个DATETIME或TIMESTAMP值，结果值的时间部分被删除，因为DATE值未包含时间信息。

· 记住尽管可以使用相同的格式指定DATETIME、DATE和TIMESTAMP值，不同类型的值的范围却不同。例如，TIMESTAMP值不能早于1970或晚于2037。这说明一个日期，例如’1968-01-01′，虽然对于DATETIME或DATE值是有效的，但对于TIMESTAMP值却无效，如果分配给这样一个对象将被转换为0。

当指定日期值时请注意某些缺陷：

· 指定为字符串的值允许的非严格格式可能会欺骗。例如，值’10:11:12′由于‘:’间割符看上去可能象时间值，但如果用于日期上下文值则被解释为年’2010-11-12′。值’10:45:15′被转换为’0000-00-00′因为’45′不是合法月。

· 在非严格模式，MySQL服务器只对日期的合法性进行基本检查：年、月和日的范围分别是1000到9999、00到12和00到31。任何包含超出这些范围的部分的日期被转换成’0000-00-00′。请注意仍然允许你保存非法日期，例如’2002-04-31′。要想确保不使用严格模式时日期有效，应检查应用程序。

在严格模式，非法日期不被接受，并且不转换。

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

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

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

11.3.1.1. 自MySQL 4.1以来的TIMESTAMP属性

注释：在旧版本的MySQL中(4.1之前)，TIMESTAMP列类型的属性在许多方面于本节所描述的大大不同。

TIMESTAMP列的显示格式与DATETIME列相同。换句话说，显示宽度固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS。

MySQL服务器也可以以MAXDB模式运行。当服务器以该模式运行时，TIMESTAMP与DATETIME相等。也就是说，如果创建表时服务器以MAXDB模式运行，TIMESTAMP列创建为DATETIME列。结果是，该列使用DATETIME显示格式，有相同的值范围，并且没有自动对当前的日期和时间进行初始化或更新。

要想启用MAXDB模式，在启动服务器时使用–sql-mode=MAXDB服务器选项或在运行时通过设置全局sql_mode变量将SQL服务器模式设置为MAXDB：

mysql> SET GLOBAL sql_mode=MAXDB；

客户端可以按照下面方法让服务器为它的连接以MAXDB模式运行：

mysql> SET SESSION sql_mode=MAXDB;

MySQL不接受在日或月列包括一个零或包含非法日期值的时间戳值。该规则的唯一例外是特殊值’0000-00-00 00:00:00′。

你可以非常灵便地确定什么时候初始化和更新TIMESTAMP和对哪些列进行初始化和更新：

· 你可以将当前的时间戳指定为默认值和自动更新的值。但只能选择一个，或者两者都不选。(不可能一个列选择一个行为而另一个列选择另一个行为）。

· 你可以指定哪个TIMESTAMP列自动初始化或更新为当前的日期和时间。不再需要为第1个TIMESTAMP列。

请注意下面讨论所信息只适用于创建时未启用MAXDB模式的表的TIMESTAMP列。(如上所述，MAXDB模式使列创建为DATETIME列）。控制TIMESTAMP列的初始化和更新的规则如下所示：

· 如果一个表内的第1个TIMESTAMP列指定为一个DEFAULT值，则不能忽略。默认值可以为CURRENT_TIMESTAMP或常量日期和时间值。

· DEFAULT NULL与第1个TIMESTAMP 列的DEFAULT CURRENT_TIMESTAMP相同。对于其它TIMESTAMP列，DEFAULT NULL被视为DEFAULT 0。

· 表内的任何一个TIMESTAMP列可以设置为自动初始化为当前时间戳和/或更新。

· 在CREATE TABLE语句中，可以用下面的任何一种方式声明第1个TIMESTAMP列：

o 用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP子句，列为默认值使用当前的时间戳，并且自动更新。

o 不使用DEFAULT或ON UPDATE子句，与DEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP相同。

o 用DEFAULT CURRENT_TIMESTAMP子句不用ON UPDATE子句，列为默认值使用当前的时间戳但是不自动更新。

o 不用DEFAULT子句但用ON UPDATE CURRENT_TIMESTAMP子句，列有默认值0并自动更新。

o 用常量DEFAULT值，列有给出的默认值。如果列有一个ON UPDATE CURRENT_TIMESTAMP子句，它自动更新，否则不。

换句话说，你可以为初始值和自动更新的值使用当前的时间戳，或者其中一个使用，或者两个皆不使用。(例如，你可以指定ON UPDATE来启用自动更新而不让列自动初始化）。

· 在DEFAULT和ON UPDATE子句中可以使用CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()或者NOW()。它们均具有相同的效果。

两个属性的顺序并不重要。如果一个TIMESTAMP列同时指定了DEFAULT和ON UPDATE，任何一个可以在另一个的前面。

例子，下面这些语句是等效的：

CREATE TABLE t (ts TIMESTAMP );

CREATE TABLE t (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP );

CREATE TABLE t (ts TIMESTAMP ON UPDATE CURRENT_TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

· 要为TIMESTAMP列而不是第1列指定自动默认或更新，必须通过将第1个TIMESTAMP列显式分配一个常量DEFAULT值来禁用自动初始化和更新。(例如，DEFAULT 0或DEFAULT’2003-01-01 00:00:00′)。然后，对于其它TIMESTAMP列，规则与第1个TIMESTAMP列相同，例外情况是不能忽略DEFAULT和ON UPDATE子句。如果这样做，则不会自动进行初始化或更新。

例如：下面这些语句是等效的：

CREATE TABLE t (

ts1 TIMESTAMP DEFAULT 0,

ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP );

CREATE TABLE t (

ts1 TIMESTAMP DEFAULT 0,

ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

DEFAULT CURRENT_TIMESTAMP );

TIMESTAMP值以UTC格式保存，存储时对当前的时区进行转换，检索时再转换回当前的时区。只要时区设定值为常量，便可以得到保存时的值。如果保存一个TIMESTAMP值，应更改时区然后检索该值，它与你保存的值不同。这是因为在两个方向的转换中没有使用相同的时区。当前的时区可以用作time_zone系统变量的值。

可以在TIMESTAMP列的定义中包括NULL属性以允许列包含NULL值。例如：

CREATE TABLE t

(

ts1 TIMESTAMP NULL DEFAULT NULL ,

ts2 TIMESTAMP NULL DEFAULT 0,

ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

);

如果未指定NULL属性，将列设置为NULL设置则会将它设置为当前的时间戳。请注意允许NULL值的TIMESTAMP列不会采用当前的时间戳，除非要么其默认值定义为CURRENT_TIMESTAMP，或者NOW()或CURRENT_TIMESTAMP被插入到该列内。换句话说，只有使用如下定义创建，定义为 NULL的TIMESTAMP列才会自动更新：

CREATE TABLE t (ts NULLDEFAULT CURRENT_TIMESTAMP)；

否则-也就是说，如果使用NULL而不是DEFAULT TIMESTAMP来定义TIMESTAMP列，如下所示…

CREATE TABLE t1 (ts NULL DEFAULT NULL );

CREATE TABLE t2 (ts NULL DEFAULT '0000-00-00 00:00:00' );

…则必须显式插入一个对应当前日期和时间的值。例如：

INSERT INTO t1 VALUES (NOW());

INSERT INTO t2 VALUES ( CURRENT_TIMESTAMP );

11.3.2. TIME类型

MySQL以’HH:MM:SS’格式检索和显示TIME值(或对于大的小时值采用’HHH:MM:SS’格式)。TIME值的范围可以从’-838:59:59′到’838:59:59′。小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。

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

· ’D HH:MM:SS.fraction’格式的字符串。还可以使用下面任何一种“非严格”语法：’HH:MM:SS.fraction’、’HH:MM:SS’、’HH:MM’、’D HH:MM:SS’、’D HH:MM’、’D HH’或’SS’。这里D表示日，可以取0到34之间的值。请注意MySQL还不保存分数。

· ’HHMMSS’格式的没有间割符的字符串，假定是有意义的时间。例如，’101112′被理解为’10:11:12′，但’109712′是不合法的(它有一个没有意义的分钟部分)，将变为’00:00:00′。

· HHMMSS格式的数值，假定是有意义的时间。例如，101112被理解为’10:11:12′。下面格式也可以理解：SS、MMSS、HHMMSS、HHMMSS.fraction。请注意MySQL还不保存分数。

· 函数返回的结果，其值适合TIME上下文，例如CURRENT_TIME。

对于指定为包括时间部分间割符的字符串的TIME值，如果时、分或者秒值小于10，则不需要指定两位数。’8:3:2′与’08:03:02′相同。

为TIME列分配简写值时应注意。没有冒号，MySQL解释值时假定最右边的两位表示秒。(MySQL解释TIME值为过去的时间而不是当天的时间）。例如，你可能认为’1112′和1112表示’11:12:00′(11点过12分)，但MySQL将它们解释为’00:11:12′(11分，12 秒)。同样，’12′和12 被解释为 ’00:00:12′。相反，TIME值中使用冒号则肯定被看作当天的时间。也就是说，’11:12′表示’11:12:00′，而不是’00:11:12′。

超出TIME范围但合法的值被裁为范围最接近的端点。例如，’-850:00:00′和’850:00:00′被转换为’-838:59:59′和’838:59:59′。

无效TIME值被转换为’00:00:00′。请注意由于’00:00:00′本身是一个合法TIME值，只从表内保存的一个’00:00:00′值还不能说出原来的值是 ’00:00:00′还是不合法的值。