分类： Mysql/postgreSQL
2008-03-30 18:11:30
第11章：列类型目录 · M 表示最大显示宽度。最大有效显示宽度是255。 · D 适用于浮点和定点类型，并表示小数点后面的位数。最大可能的值是30，但不应大于M-2。 · 方括号(‘[’和‘]’)表示可选部分。 |
| 11.1.1. 数值类型概述 如果为一个数值列指定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值视为真。 · 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。 o 使用带符号的BIGINT或DOUBLE值进行所有算法，因此除了位函数，不应使用大于9223372036854775807(63位)的无符号的大整数! 如果这样做，结果中的最后几位可能出错，这是由于将BIGINT值转换为DOUBLE进行四舍五入时造成的错误。 · FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]小(单精度)浮点数。允许的值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38。这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些。 使用浮点数可能会遇到意想不到的问题，因为在MySQL中的所有计算用双精度完成。 · DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]普通大小(双精度)浮点数。允许的值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到 1.7976931348623157E+308。这些是理论限制，基于IEEE标准。实际的范围根据硬件或操作系统的不同可能稍微小些。是小数总位数，D是小数点后面的位数。如果M和D被省略，根据硬件允许的限制来保存值。双精度浮点数精确到大约15位小数位。 · 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数据类型相同。 · DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]压缩的“严格”定点数。M是小数位数(精度)的总数，D是小数点(标度)后面的位数。小数点和(负数)的‘-’符号不包括在M中。如果D是0，则值没有小数点或分数部分。DECIMAL整数最大位数(M)为65。支持的十进制数的最大位数(D)是30。如果D被省略， 默认是0。如果M被省略， 默认是10。 所有DECIMAL列的基本计算(+，-，*，/)用65位精度完成。 · DEC[(M[,D])] [UNSIGNED] [ZEROFILL], NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL], FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]是DECIMAL的同义词。FIXED同义词适用于与其它服务器的兼容性。 11.1.2日期和时间类型概述 · 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。 · 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字符串类型 在某些情况中，MySQL可以将一个字符串列更改为不同于CREATE TABLE或ALTER TABLE语句中所给出的类型。参见。 MySQL字符串特性： 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 · ); · MySQL 5.1用字符单位解释在字符列定义中的长度规范 · 对于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 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值的尾部空格 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。
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中，语法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日期和时间类型 如果试图插入一个不合法的日期，MySQL将给出警告或错误。可以使用ALLOW_INVALID_DATES SQL模式让MySQL接受某些日期，例如'1999-11-31'。在这种模式下，MySQL只验证月范围为从0到12，日范围为从0到31。这些范围可以包括零，因为MySQL允许在DATE或DATETIME列保存日/月和日是零的日期。。(如果你不想在日期中出现零，可以使用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模式，使用这些值会产生警告。
· “零”值是特殊值，但你可以使用表内显示的值显式保存或引用它们。你也可以使用值'0'或0来保存或引用，写起来更容易。 · MyODBC中使用的“零”日期或时间值在MyODBC 2.50.12和以上版本中被自动转换为NULL，因为ODBC不能处理此类值。 11.3.1，DateTime,Time,TimeStamp时间类型 对于包括日期部分间割符的字符串值，如果日和月的值小于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'。要想确保不使用严格模式时日期有效，应检查应用程序。 11.3.1.1. 自MySQL 4.1以来的TIMESTAMP属性TIMESTAMP列的显示格式与DATETIME列相同。换句话说，显示宽度固定在19字符，并且格式为YYYY-MM-DD HH:MM:SS。MySQL服务器也可以以MAXDB模式运行。当服务器以该模式运行时，TIMESTAMP与DATETIME相等。也就是说，如果创建表时服务器以MAXDB模式运行，TIMESTAMP列创建为DATETIME列。结果是，该列使用DATETIME显示格式，有相同的值范围，并且没有自动对当前的日期和时间进行初始化或更新。 启用MAXDB模式： mysql> SET GLOBAL sql_mode=MAXDB； 客户端可以按照下面方法让服务器为它的连接以MAXDB模式运行： mysql> SET SESSION sql_mode=MAXDB; MySQL不接受在日或月列包括一个零或包含非法日期值的时间戳值。该规则的唯一例外是特殊值'0000-00-00 00:00:00'。 可以非常灵便地确定什么时候初始化和更新TIMESTAMP和对哪些列进行初始化和更新： ·
你可以将当前的时间戳指定为默认值和自动更新的值。但只能选择一个，或者两者都不选。(不可能一个列选择一个行为而另一个列选择另一个行为）。 注意下面讨论所信息只适用于创建时未启用MAXDB模式的表的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子句，它自动更新，否则不。 · 在DEFAULT和ON UPDATE子句中可以使用CURRENT_TIMESTAMP、CURRENT_TIMESTAMP()或者NOW()。它们均具有相同的效果。 两个属性的顺序并不重要。如果一个TIMESTAMP列同时指定了DEFAULT和ON UPDATE，任何一个可以在另一个的前面。 例子，下面这些语句是等效的： CREATE TABLE t (ts 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。 |
·
要为TIMESTAMP列而不是第1列指定自动默认或更新，必须通过将第1个TIMESTAMP列显式分配一个常量DEFAULT值来禁用自动初始化和更新。(例如，DEFAULT
0或DEFAULT'2003-01-01
00:00:00')。然后，对于其它TIMESTAMP列，规则与第1个TIMESTAMP列相同，例外情况是不能忽略DEFAULT和ON
UPDATE子句。如果这样做，则不会自动进行初始化或更新。
NULL属性 可以在TIMESTAMP列的定义中包括NULL属性以允许列包含NULL值。例如：
如果未指定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类型 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'还是不合法的值。 11.3.3，YEAR类型 MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。 可以指定各种格式的YEAR值： · 四位字符串，范围为'1901'到'2155'。 · 四位数字，范围为1901到2155。 · 两位字符串，范围为'00'到'99'。'00'到'69'和'70'到'99'范围的值被转换为2000到2069和1970到1999范围的YEAR值 · 两位整数，范围为1到99。1到69和70到99范围的值被转换为2001到2069和1970到1999范围的YEAR值。请注意两位整数范围与两位字符串范围稍有不同，因为你不能直接将零指定为数字并将它解释为2000。你必须将它指定为一个字符串'0'或'00'或它被解释为0000。 · 函数返回的结果，其值适合YEAR上下文，例如NOW()。 非法YEAR值被转换为00000 |
| 11.4. String类型 CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。 CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。 CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换 VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。 同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。 如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉的字符不是空格，则会产生一条警告。这是在服务器没有启用严格模式时的情况，若启用了严格模式，则会产生错误。 下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：
11.4.2. BINARY和VARBINARY类型 BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。（即不存在字符集及相应的校对规则） BINARY和VARBINARY允许的最大长度一样，如同CHAR和VARCHAR，不同的是BINARY和VARBINARY的长度是字节长度而不是字符长度。 BINARY列的保存： 当保存BINARY值时，在它们右边填充值以达到指定长度。填充值是0x00(零字节)。插入值时在右侧添加0x00 on，并且选择时不删除尾部的字节。比较时所有字节很重要，包括ORDER BY和DISTINCT操作。比较时0x00字节和空格是不同的，0x00<空格。检索时不会删除尾部插入时添加的空格。 例如：对于一个BINARY(3)列，当插入时 'a' 变为 'a \0'。'a\0'插入时变为'a\0\0'。当选择时两个插入的值均不更改。 VARBINARY的插入： 对于VARBINARY，插入时不填充字符，选择时不裁剪字节。比较时所有字节很重要，包括ORDER BY和DISTINCT操作。比较时0x00字节和空格是不同的，0x00<空格。 如果你计划使用这些数据类型来保存二进制数据并且需要检索的值与保存的值完全相同(varbinary似乎是理想的选择)，应考虑前面所述的填充和裁剪特征。下面的例子说明了用0x00填充的BINARY值如何影响列值比较：
11.4.3. BLOB和TEXT类型 BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。 有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。 blob与text的区别： BLOB 列被视为二进制字符串(字节字符串)。TEXT列被视为非二进制字符串(字符字符串)。BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较。 在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。 当未运行在严格模式时，如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值，值被截取以保证适合。如果截掉的字符不是空格，将会产生一条警告。使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告。参见。 在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR： · 当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同）。 · 对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。参见。 · BLOB和TEXT列不能有 默认值 使用BLOB和TEXT的约束： 由于BLOB和TEXT值可能会非常长，使用它们时可能遇到一些约束： · 当排序时只使用该列的前max_sort_length个字节。max_sort_length的 默认值是1024；该值可以在启动mysqld服务器时使用--max_sort_length选项进行更改。参见。 运行时增加max_sort_length的值可以在排序或组合时使更多的字节有意义。任何客户端可以更改其会话max_sort_length变量的值： mysql> SET max_sort_length = 2000; mysql> SELECT id, comment FROM tbl_name -> ORDER BY comment; 当你想要使超过max_sort_length的字节有意义，另一种方式是将列值转换为固定长度的对象。标准方法是使用SUBSTRING函数。例如，下面的语句对comment列的2000个字节进行排序： mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name -> ORDER BY SUBSTRING(comment,1,2000） 11.4.4，ENUM类型 ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。 在某些情况下，ENUM值也可以为空字符串('')或NULL： · 如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。 · 如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。 每个枚举值有一个索引： · 来自列规定的允许的值列中的值从1开始编号。 · 空字符串错误值的索引值是0。这说明你可以使用下面的SELECT语句来找出分配了非法ENUM值的行： mysql> SELECT * FROM tbl_name WHERE enum_col=0; · NULL值的索引是NULL 例如，定义为ENUM的列('one'，'two'，'three')可以有下面所示任何值。还显示了每个值的索引：
mysql> SELECT enum_col+0 FROM tbl_name; 不建议使用类似数字的枚举值来定义一个ENUM列(上面已经遇到了这种情况)，因为这很容易引起混淆。例如，下面的列含有字符串值'0'、'1'和'2'的枚举成员，但数值索引值为1、2和3： 对 Enum列的排序规则： 根据枚举成员在列定义中列出的顺序对ENUM值进行排序。(换句话说，ENUM值根据索引编号进行排序）。例如，对于ENUM('a'，'b')，'a'排在'b'前面，但对于ENUM('b'，'a')，'b'排在'a'前面。空字符串排在非空字符串前面，并且NULL值排在所有其它枚举值前面。要想防止意想不到的结果，按字母顺序规定ENUM列。还可以使用GROUP BY CAST(col AS CHAR)或GROUP BY CONCAT(col)来确保按照词汇对列进行排序而不是用索引数字。 11.4.5，SET类型 SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。例如，指定为SET('one', 'two') NOT NULL的列可以有下面的任何值：''，'one'，'two'，'one,two' SET最多可以有64个不同的成员 当创建表时，SET成员值的尾部空格将自动被删除 当检索时，保存在SET列的值使用列定义中所使用的大小写来显示 MySQL用数字保存SET值，所保存值的低阶位对应第1个SET成员 如果在数值上下文中检索一个SET值，检索的值的位设置对应组成列值的SET成员。例如，你可以这样从一个SET列检索数值值： mysql> SELECT set_col+0 FROM tbl_name; 对于指定为SET('a','b','c','d')的列，成员有下面的十进制和二进制值：
如果你为该列分配一个值9，其二进制形式为1001，因此第1个和第4个SET值成员'a'和'd'被选择，结果值为 'a,d' 对于包含多个SET元素的值，当插入值时元素所列的顺序并不重要。在值中一个给定的元素列了多少次也不重要。当以后检索该值时，值中的每个元素出现一次，根据表创建时指定的顺序列出元素。例如，假定某个列指定为SET('a','b','c','d') mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd')); mysql> insert into myset (col) -> values ('a,d'),('d,a'),('a,d,d'); mysql> select col from myset; +------+ | col | +------+ | a,d | | a,d | | a,d | +------+ 如果将SET列设置为一个不支持的值，则该值被忽略并发出警告： mysql> INSERT INTO myset (col) VALUES ('a,d,d,s'); mysql> SHOW WARNINGS; +-------+------+------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------+ | Error | 1265 | Data truncated for column 'col' at row 1 | +-------+------+------------------------------------------+ SET值按数字顺序排序。NULL值排在非NULL SET值的前面 通常情况，可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值： mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
第1个语句找出SET_col包含value set成员的行。第2个类似，但有所不同：它在其它地方找出set_col包含value的行，甚至是在另一个SET成员的子字符串中。 下面的语句也是合法的： mysql> SELECT * FROM tbl_name WHERE set_col & 1;
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2'; 第1个语句寻找包含第1个set成员的值。第2个语句寻找一个确切匹配的值。应注意第2类的比较。将set值与'val1,val2'比较返回的结果与同'val2,val1'比较返回的结果不同。指定值时的顺序应与在列定义中所列的顺序相同。 如果想要为SET列确定所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE set_col并解析输出中第2列的SET定义 mysql> show columns from myset like 'col'; 11.5 |
