Mysql入门系列：MySQL的列类型（2）

【 tulaoshi.com - 编程语言 】

　　序列不从1开始的另一个原因从技术的角度来说可能不值一提。例如，在分配会员号时，序列号不要从1开始，以免出现关于谁是第一号的政治争论。

　　(4) 不用AUTO_INCREMENT 生成序列生成序列号的另一个方法根本就不需要使用AUTO_INCREMENT 列。它利用取一个参数的L A S T _ INSERT_ID( ) 函数的变量来生成序列号。（这种形式在MySQL3.22.9. 中引入）如果利用L A S T _ INSERT_ID(expr) 来插入或更新一个列， 则下一次不用参数调用L A S T _ INSERT_ID( ) 时，将返回expr 的值。换句话说，就像由AUTO_INCREMENT 机制生成的那样对expr 进行处理。这样使得能生成一个序列号，然后可在以后的客户会话中利用它，用不着取受其他客户机影响的值。利用这种策略的一种方法是创建一个包含一个值的单行表，该值在想得到序列中下一个值时进行更新。例如，可创建如下的表：

　　上面的语句创建了表seq_table 并用包含seq 值0 的行对其进行初始化。可利用这个表产生下一个序列号，如下所示：

　　该语句取出seq 列的当前值并对其加1，产生序列中的下一个值。利用L A S T _ INSERT _ID(seq + 1) 生成新值使它就像一个AUTO_INCREMENT 值一样，而且此值可在以后的语句中通过调用无参数的L A S T _ INSERT_ID( ) 来取出。即使某个其他客户机同时生成了另一个序列号，上述作用也不会改变，因为L A S T _ INSERT_ID( ) 是客户机专用的。如果希望生成增量不是1的编号序列或负增量的编号序列，也可以利用这个方法。例如，下面两个语句可以用来分别生成一个增量为100 的编号序列和一个负的编号序列：

　　通过将seq 列设置为相应的初始值，可利用这个方法生成以任意值开始的序列。关于将此序列生成方法用于多个计数器的应用，可参阅第3章。

　　2.2.3 串列类型

　　MySQL提供了几种存放字符数据的串类型。串常常用于如下这样的值：

　　在某种意义上，串实际是一种通用类型，因为可用它们来表示任意值。例如，可用串类型来存储二进制数据，如影像或声音，或者存储gzip 的输出结果，即存储压缩数据。对于所有串类型，都要剪裁过长的值使其适合于相应的串类型。但是串类型的取值范围很不同，有的取值范围很小，有的则很大。取值大的串类型能够存储近4GB 的数据。因此，应该使串足够长以免您的信息被切断（由于受客户机/服务器通信协议的最大块尺寸限制，列

　　值的最大限额为2 4 MB）。

　　表2 - 8给出了MySQL定义串值列的类型，以及每种类型的最大尺寸和存储需求。对于可变长的列类型，各行的值所占的存储量是不同的，这取决于实际存放在列中的值的长度。这个长度在表中用L 表示。

　　L 以外所需的额外字节为存放该值的长度所需的字节数。MySQL通过存储值的内容及其长度来处理可变长度的值。这些额外的字节是无符号整数。请注意，可变长类型的最大长度、此类型所需的额外字节数以及占用相同字节数的无符号整数之间的对应关系。例如，

　　MEDIUMBLOB 值可能最多22 4 - 1字节长并需要3 个字节记录其结果。3 个字节的整数类型MEDIUMINT 的最大无符号值为22 4 - 1。这并非偶然。

　　1. CHAR 和VARCHAR 列类型

　　CHAR 和VARCHAR 是最常使用的串类型。它们是有差异的， CHAR 是定长类型而VARCHAR 是可变长类型。CHAR(M) 列中的每个值占M 个字节；短于M 个字节的值存储时在右边加空格（但右边的空格在检索时去掉）。VARCHAR(M) 列的值只用所必需的字节数来存放（结尾的空格在存储时去掉，这与ANSI SQL 的VARCHAR 值的标准不同），然后再加一个字节记录其长度。如果所需的值在长度上变化不大，则CHAR 是一种比VARCHAR 好的选择，因为处理行长度固定的表比处理行长度可变的表的效率更高。如果所有的值长度相同，由于需要额外的字节来记录值的长度，VARCHAR 实际占用了更多的空间。在MySQL3.23 以前，CHAR 和VARCHAR 列用最大长度为1到255 的M 来定义。从MySQL3.23 开始，CHAR(0) 也是合法的了。在希望定义一个列，但由于尚不知道其长度，所以不想给其分配空间的情况下， CHAR(0) 列作为占位符很有用处。以后可以用ALTE RTABLE 来加宽这个列。如果允许其为NULL，则CHAR(0) 列也可以用来表示o n / o ff 值。这样的列可能取两个值，NULL 和空串。CHAR(0) 列在表中所占的空间很小，只占一位。除少数情况外，在同一个表中不能混用CHAR 和VARCHAR。MySQL根据情况甚至会将列从一种类型转换为另一种类型。这样做的原因如下：

　　■ 行定长的表比行可变长的表容易处理（其理由请参阅2 . 3节选择列的类型）。

　　■ 表行只在表中所有行为定长类型时是定长的。即使表中只有一列是可变长的，该表的行也是可变长的。

　　■ 因为在行可变长时定长行的性能优点完全失去。所以为了节省存储空间，在这种情况下最好也将定长列转换为可变长列。这表示，如果表中有VARCHAR 列，那么表中不可能同时有CHAR 列；MySQL会自动地将它们转换为VARCHAR 列。例如创建如下一个表：

　　请注意，VARCHAR 列的出现使MySQL将c1也转换成了VARCHAR 类型。如果试图用ALTER TABLE 将c1转换为CHAR，将不起作用。将VARCHAR 列转换为CHAR 的惟一办法是同时转换表中所有VARCHAR 列：

　　BLOB 和TEXT 列类型像VARCHAR 一样是可变长的，但是它们没有定长的等价类型，因此不能在同一表中与BLOB 或TEXT 列一起使用CHAR 列。这时任何CHAR 列都将被转换为VARCHAR 列。定长与可变长列混用的情形是在CHAR 列短于4 个字符时，可以不对其进行转换。例如，MySQL不会将下面所创建的表中的CHAR 列转换为VARCHAR 列：

　　短于4个字符的列不转换的原因是，平均情况下，不存储尾空格所节省的空间被VA R C HA R列中记录每个值的长度所需的额外字节所抵消了。实际上，如果所有列都短， MySQL将会把所定义的所有列从VARCHAR 转换为CHAR。MySQL这样做的原因是，这种转换平均来说不会增加存储需求，而且使表行定长，从而改善了性能。如果按如下创建一个表，VARCHAR 列全都会转换为CHAR 列：

　　2. BLOB 与TEXT 列类型

　　BLOB 是一个二进制大对象，是一个可以存储大量数据的容器，可以使其任意大。在MySQL中，BLOB 类型实际是一个类型系列（ T I N Y B L O B、B L O B、M E D I U MB L O B、L O N G B L O B），除了在可以存储的最大信息量上不同外（请参阅表2 - 8），它们是等同的。

　　MySQL还有一个TEXT 类型系列（ T I N Y T E X T、T E X T、M E D I U M T E X T、L O N G T E X T）。除了用于比较和排序外，它们在各个方面都与相应的BLOB 类型等同，BLOB 值是区分大小写的，而TEXT 值不区分大小写。BLOB 和TEXT 列对于存储可能有很大增长的值或各行大小有很大变化的值很有用，例如，字处理文档、图像和声音、混合数据以及新闻文章等等。BLOB 或TEXT 列在MySQL3.23 以上版本中可以进行索引，虽然在索引时必须指定一个用于索引的约束尺寸，以免建立出很大的索引项从而抵消索引所带来的好处。除此之外，一般不通过查找BLOB 或TEXT 列来进行搜索，因为这样的列常常包含二进制数据（如图像）。常见的做法是用表中另外的列来记录有关BLOB 或TEXT 值的某种标识信息，并用这些信息来确定想要哪些行。使用BLOB 和TEXT 列需要特别注意以下几点：

　　■ 由于BLOB 和TEXT 值的大小变化很大，如果进行的删除和更新很多，则存储它们的

　　表出现高碎片率会很高。应该定期地运行OPTIMIZE TABLE 减少碎片率以保持良好的

　　性能。要了解更详细的信息请参阅第4章。

　　■ 如果使用非常大的值，可能会需要调整服务器增加max_allowed_packet 参数的值。详细的信息请参阅第11章常规的MySQL管理。如果需要增加希望使用非常大的值的客户机的块尺寸，可见附录EMySQL程序参考，该附录介绍了怎样对mysql和mysqldump 客户机进行这种块尺寸的增加。

　　3. ENUM 和SET 列类型

　　ENUM 和SET 是一种特殊的串类型，其列值必须从一个固定的串集中选择。它们之间的主要差别是ENUM 列值必须确实是值集中的一个成员，而SET 列值可以包括集合中任意或所有的成员。换句话说， ENUM 用于互相排斥的值，而S E T列可以从一个值的列表中选择多个值。

　　ENUM 列类型定义了一个枚举。可赋予ENUM 列一个在创建表时指定的值列表中选择的成员。枚举可具有最多65 536 个成员（其中之一为MySQL保留）。枚举通常用来表示类别值。例如，定义为ENUM (N, Y) 的列中的值可以是N或Y。或者可将ENUM 用于诸如调查或问卷中的多项选择问题，或用于某个产品的可能尺寸或颜色等：

　　如果正在处理Web 页中的选择，那么可以利用ENUM 来表示站点访问者在某页上的互相排斥的单选钮集合中进行的选择。例如，如果运行一个在线比萨饼订购服务系统，可用ENUM 来表示顾客订购的比萨饼形状：

　　如果枚举类别表示计数，在建立该枚举时最重要的是选择合适的类别。例如，在记录实验室检验中白血球的数目时，可能会将计数分为如下的几组：

　　在某个测试结果以精确的计数到达时，要根据该值所属的类别来记录它。但如果想将列从基于类别的ENUM 转换为基于精确计数的整数时，不可能恢复原来的计数。在创建SET 列时，要指定一个合法的集合成员列表。在这种意义上， SET 类型与ENUM是类似的。但是SET 与ENUM 不同，每个列值可由来自集合中任意数目的成员组成。集合中最多可有64 个成员。对于值之间互斥的固定集合，可使用SET 列类型。例如，可利用SET 来表示汽车的可用选件，如下所示：

　　然后，特定的SET 值将表示顾客实际订购哪些选件，如下所示：

　　空串表示顾客未订购任何选件。这是一个合法的SET 值。SET 列值为单个串。如果某个值由多个集合成员组成，那么这些成员在串中用逗号分隔。显然，这表示不应该用含有逗号的串作为SET 成员。SET 列的其他用途是表示诸如病人的诊断或来自Web 页的选择结果这样的信息。对于诊断，可能会有一个向病人提问的标准症状清单，而病人可能会表现出某些症状或所有的症状。对于在线比萨饼服务系统，用于订购的Web 页应该具有一组复选框，用来表示顾客想在比萨饼上加的配料。对ENUM 或SET 列的合法值列表的定义很重要，例如：

　　■ 正如上面所介绍的，此列表决定了列的可能合法值。

　　■ 可按任意的大小写字符插入ENUM 或SET 值，但是列定义中指定的串的大小写字符决定了以后检索它们时的大小写。例如，如果有一个ENUM (Y, N) 列，但您在其中存储了 y和n，当您检索出它们时显示的是 Y和N。这并不影响比较或排序的状态，因为ENUM 和SET 列是不区分大小写的。

　　■ 在ENUM 定义中的值顺序就是排序顺序。SET 定义中的值顺序也决定了排序顺序，但是这个关系更为复杂，因为列值可能包括多个集合成员。

　　■ SET 定义中的值顺序决定了在显示由多个集合成员组成的SET 列值时，子串出现的顺序。

　　ENUM 和SET 被归为串类型是由于在建立这些类型的列时，枚举和集合成员被指定为串。但是，这些成员在内部存放时作为数值，而且同样可作为数值来处理。这表示ENUM 和S E T类型比其他的串类型更为有效，因为通常可用数值运算而不是串运算来处理它们。而且这还表示ENUM 和SET 值可用在串或数值的环境中。

　　列定义中的ENUM 成员是从1开始顺序编号的。（0 被MySQL用作错误成员，如果以串的形式表示就是空串。）枚举值的数目决定了ENUM 列的存储大小。一个字节可表示256 个值，两个字节可表示65 536 个值。（可将其与一字节和两字节的整数类型T I N Y I N T、

　　UNSIGNED 和SMALLINT UNSIGNED 进行对比。）因此，枚举成员的最大数目为65 536（包括错误成员），并且存储大小依赖于成员数目是否多于256 个。在ENUM 定义中，可以最多指定65 535（而不是65 536）个成员，因为MySQL保留了一个错误成员，它是每个枚举的隐含成员。在将一个非法值赋给ENUM 列时，MySQL自动将其换成错误成员。下面有一个例子，可用mysql客户机程序测试一下。它给出枚举成员的数值顺序，而且还说明了NULL 值无顺序编号：

　　可对ENUM 成员按名或者按编号进行运算，例如：

　　可以定义空串为一个合法的枚举成员。与列在定义中的其他成员一样，它将被赋予一个非零的数值。但是使用空串可能会引起某些混淆，因为该串也被作为数值为0 的错误成员。在下面的例子中，将非法的枚举值 x赋予ENUM 列引起了错误成员的赋值。仅在以数值

　　形式进行检索时，才能够与空串区分开：

　　SET 列的数值表示与ENUM 列的表示有所不同，集合成员不是顺序编号的。每个成员对应SET 值中的一个二进制位。第一个集合成员对应于0 位，第二个成员对应于1位，如此等等。数值SET 值0 对应于空串。SET 成员以位值保存。每个字节的8 个集合值可按此方式存

　　放，因此SET 列的存储大小是由集合成员的数目决定的，最多64 个成员。对于大小为1到8、9 到16、17 到2 4、25 到3 2、33 到64 个成员的集合，其SET 值分别占用1、2、3、4 或8个字节。

　　用一组二进制位来表示SET 正是允许SET 值由多个集合成员组成的原因。值中二进制位的任意组合都可以得到，因此，相应的值可由对应于这些二进制位的SET 定义中的串组合构成。下面给出一个说明SET 列的串形式与数值形式之间关系的样例；数值以十进制形式和二

　　进制形式分别给出：

　　如果给SET 列赋予一个含有未作为集合成员列出的子串的值，那么这些子串被删除，并将包含其余子串的值赋予该列。在赋值给SET 列时，子串不需要按定义该列时的顺序给出。但是，在以后检索该值时，各成员将按定义时的顺序列出。假如用下面的定义定义一个S E T列来表示家具：

　　如果给这个列赋予 c h a i r, couch, table值，那么，c o uc h被放弃，因为它不是集合的成员。其次，以后检索这个值时，显示为 table, chair。之所以这样是因为MySQL针对所赋的值的每个子串决定各个二进制位并在存储值时将它们置为1。c o uc h不对应二进制位，则忽略。在检索时，MySQL按顺序扫描各二进制位，通过数值值构造出串值，它自动地将子串排成定义列时给出的顺序。这个举动还表示，如果在一个值中不止一次地指定某个成员，但在检索时它也只会出现一次。如果将 lamp, lamp,lamp赋予某个SET 列，检索时也只会得出l a m p。MySQL重新对SET 值中的成员进行排序这个事实表示，如果用一个串来搜索值，则必须以正确的顺序列出各成员。如果插入 c h a i r, table，然后搜索c h a i r, table，那么将找不到相应的记录；必须查找 table, chair才能找到。ENUM 和SET 列的排序和索引是根据列值的内部值（数值值）进行的。下面的例子可能会显示不正确，因为各个值并不是按字母顺序存储的：

　　NULL 值排在其他值前（如果是降序，将排在其他值之后）。如果有一个固定的值集，并且希望按特殊的次序进行排序，可利用ENUM 的排序顺序。在创建表时做一个ENUM 列，并在该列的定义中以所想要的次序给出各枚举值即可。如果希望ENUM 按正常的字典顺序排序，可使用C O N C AT( ) 和排序结果将列转换成一个非ENUM 串，如下所示：

　　4. 串列类型属性

　　可对CHAR 和VARCHAR 类型指定B I N A RY 属性使列值作为二进制串处理（即，在比较和排序操作区分大小写）。

　　可对任何串类型指定通用属性NULL 和NOT NULL。如果两者都不指定，缺省值为NULL。但是定义某个串列为NOT NULL 并不阻止其取空串。空值不同于遗漏的值，因此，不要错误地认为可以通过定义NOT NULL 来强制某个串列只包含非空的值。如果要求串值非

　　空，那么这是一个在应用程序中必须强制实施的约束条件。

　　还可以对除BLOB 和TEXT 类型外的所有串列类型用D E FA U LT 属性指定一个缺省值。如果不指定缺省值， MySQL会自动选择一个。对于可以包含NULL 的列，其缺省值为NULL。对于不能包含NULL 的列，除ENUM 列外都为空串，在ENUM 列中，缺省值为第一个枚举成员（对于SET 类型，在相应的列不能包含NULL 时其缺省值实际上是空集，不过这里空集等价于空串）。

　　2.2.4 日期和时间列类型

　　MySQL提供了几种时间值的列类型，它们分别是： DATE、DATE TIME、TIME、TIMES TAMP 和YEAR。表2-9 给出了MySQL为定义存储日期和时间值所提供的这些类型，并给出了每种类型的合法取值范围。YEAR 类型是在MySQL3.22版本中引入的。其他类型在所有MySQL版本中都可用。每种时间类型的存储需求见表2 - 10。每个日期和时间类型都有一个零值，在插入该类型的一个非法值时替换成此值，见表2 - 11。这个值也是定义为NOT NULL 的日期和时间列的缺省值。