选择MYSQL列的类型

2024-07-24 12:55:35

字体：大中小

来源：转载

供稿：网友

2.3 选择列的类型
上一节描述了各种可供选择的mysql的列类型及其属性，以及它们可存储的各种值，所占用的存储空间等等。但是在实际创建一个表时怎样决定用哪些类型呢？本节讨论在做出决定前应考虑的各种因素。最“常用”的列类型是串类型。可将任何数据存储为串，因为数和日期都可以串的形式表示。但是为什么不将所有列都定义为串从而结束这里的讨论呢？让我们来看一个简单的例子。假定有一些看起来像数的值。可将它们表示为串，但应该这样做吗？这样做会发生什么事？
有一桩事不可避免，那就是可能要使用更多的空间，因为较串来说，数的存储更为有效。我们可能已经注意到，由于数和串处理方式的不同，查询结果也有所不同。例如，数的排序与串的排序就有所不同。数2 小于数11，但串“2”按字典顺序大于“ 11”。可用如下数值内容的列来搞清这个问题：

将零加到该列强制得出一个数值，但是这样合理吗？一般可能不合理。将该列作为数而不是串具有几个重要的含义。它对每个列值实施串到数的转换，这是低效的。而且将该列的值转换为计算结果妨碍mysql使用该列上的索引，降低了以后的查询速度。如果这些值一开始就是作为数值存储的，那么这些性能上的降低都不会出现。采用一种表示而不用另一种的简单选择实际上并不简单，它在存储需求、查询效率以及处理性能等方面都会产生重要的影响。
前面的例子说明，在选择列类型时，有以下几个问题需要考虑：
■ 列中存储何种类型的值？这是一个显而易见的问题，但必须确定。可将任何类型的值表示为串，尤其当对数值使用更为合适的类型可能得到更好的性能时（日期和时间值也是这样）。可见，对要处理的值的类型进行评估不一定是件微不足道的事，特别在数据是别人的数据时更是如此。如果正在为其他人建立一个表，搞清列中要存储的值的类型极为重要，必须提足够多的问题以便得到作出决定的充足的信息。
■ 列值有特定的取值范围吗？如果它们是整数，它们总是非负值吗？如果这样，可采用unsigned 类型。如果它们是串，总能从定长值集中选出它们吗？如果这样， enum或set 是很合适的类型。在类型的取值范围与所用的存储量之间存在折衷。需有一个多“大”的类型？对于数，如果其取值范围有限，可以选择较小的类型，对取值范围几乎无限的数，应该选择较大的类型。对于串，可以使它们短也可以使它们长，但如果希望存储的值只含不到10 个字符，就不应该选用char( 2 5 5 )。
■ 性能与效率问题是什么？有些类型比另外一些类型的处理效率高。数值运算一般比串的运算快。短串比长串运行更快，而且磁盘消耗更小。定长类型比可变长类型的性能更好。
■ 希望对值进行什么样的比较？对于串，其比较可以是区分大小写的，也可以不区分大小写。其选择也会影响排序，因为它是基于比较的。
■ 计划对列进行索引吗？如果计划对列进行索引，那么将会影响您对列类型的选择，因为有的mysql版本不允许对某些类型进行索引，例如不能对blob 和text 类型进行索引。而且有的mysql版本要求定义索引列为not null 的，这使您不能使用null 值。
现在让我们来更详细地考虑这些问题。这里要指出的是：在创建表时，希望作出尽可能好的列类型选择，但如果所作的选择其实际并不是最佳的，这也不会带来多大的问题。可用alter table 将原来选择的类型转换为更好的类型。在发现数据所含的值比原设想的大时，可像将smallint 更换成mediumint 那样简单地对类型进行更换。有时这种更换也可能很复杂，例如将char 类型更换成具有特定值集的enum 类型。在mysql3.23 及以后的版本中，可使用procedure analyse( ) 来获得表列的信息，诸如最小值和最大值以及推荐的覆盖列中值的取值范围的最佳类型。这有助于确定使用更小的类型，从而改进涉及该表的查询的性能，并减少存储该表所需的空间量。
2.3.1列中存储何种类型的值
在决定列的类型时，首先应该考虑该列的值类型，因为这对于所选择的类型来说具有最为明显的意义。通常，在数值列中存储数，在串列中存储串，在日期和时间列中存储日期和时间。如果数值有小数部分，那么应该用浮点列类型而不是整数类型，如此等等。有时也存
在例外，不可一概而论。主要是为了有意义地选择类型，应该理解所用数据的特性。如果您打算存储自己的数据，大概对如何存储它们会有自己很好的想法。但是，如果其他人请您为
他们建一个表，决定列类型有时会很困难。这不像处理自己的数据那么容易。应该充分地提问，搞清表实际应该包含何种类型的值。
如果有人告诉您，某列需要记录“降雨量”。那是一个数吗？或者它“主要”是一个数值，即，一般是但不总是编码成一个数吗？例如，在看电视新闻时，气象预报一般包括降雨量。有时是一个数（如“ 0 . 2 5”英寸的雨量），但是有时是“微量( t r a c e )”降雨，意思是“雨根本就不大”。这对气象预报很合适，但在数据库中怎样存储？有可能需要将“微量”量化为一个数，以便能用数值列类型来记录降雨量，或许需要使用串，以便可以记录“微量”这个词。或者可以提出某种更为复杂的安排，使用一个数值列和一个串列，如果填充一个列就让另一个列为null。很明显，可能的话，应该避免最后这种选择；最后这种选择使表难于理解，使查询更为困难。我们一般尽量以数值形式存储所有的行，而且只为了显示的需要才对它们进行转换。例如，如果小于0.01英寸的非零降雨量被视为微量，那么可以如下选择列值：

对于金钱的计算，需要处理元和分部分。这似乎像浮点值，但float和double 容易出现舍入错误，除了只需要大致精确的记录外，这些类型可能不适合。因为人们对自己的钱都是很敏感的，最好是用一种能提供完善的精确性的类型，例如：
■ 将钱表示为decimal(m, 2) 类型，选择m 为适合于所需取值范围的最大宽度。这给出具有两位小数精度的浮点值。decimal 的优点是将值表示为一个串，而且不容易出现舍入错误。不利之处是串运算比内部存储为数的值上的运算效率差。
■ 可在内部用整数类型来表示所有的钱值。其优点是内部用整数来计算，这样会非常快。不利之处是在输入或输出时需要利用乘或除100 对值进行转换。有些数据显然是数值的，但必须决定是使用浮点类型还是使用整数类型。应该搞清楚所用的单位是什么以及需要什么样的精度。整个单元的精度都够吗？或者需要表示小数的单元吗？这将有助于您在整数列和浮点数列之间进行区分。例如，如果您正表示权重，那么如果记录的值为英磅，可以使用一个整形列。如果希望记录小数部分，就应该使用浮点列。在有的情况下，甚至会使用多个字段，例如：如果希望根据磅和盎司记录权重，则可以使用多个列。
高度（h e i g h t）是另外一种数值类型，有如下几种表示方法：
■ 诸如“6 英尺2 英寸”可表示为“ 6 - 2”这样一个串。这种形式具有容易察看和理解的优点（当然比“ 74 英寸更好理解”），但是这种值很难用于数学运算，如求和或取平均值。
■ 一个数值字段表示英尺，另一个数值字段表示英寸。这样的表示进行数值运算相对容易，但两个字段比一个字段难于使用。
■ 只用一个表示英寸的数值段。这是数据库最容易处理的方式，但是这种方式意义最不明确。不过要记住，不一定要用与您惯常使用的那种格式来表示值。可以用mysql的函数将值转换为看上去意义明显的值。因此，最后这种表示方法可能是表示高度的最好方法。
如果需要存储日期信息，需要包括时间吗？即，它们永远都需要包括时间吗？ mysql不提供具有可选时间部分的日期类型： date 可不包含时间，而datetime 必须包含时间。如果时间确实是可选的，那么可用一个date 列记录日期，一个time 列记录时间。允许time 列为null 并解释为“无时间”：

在用基于日期信息的主-细目关系连接两个表时，决定是否需要时间值特别重要。假如您正在进行一项研究，包括一些对进入您的办公室的人进行测试的题目。在一个标准的初步测试集之后，您可能会在同一天进行几个额外的测试，测试的选择视初步测试结果而定。您可能会利用一个主-细目关系来表示这些信息，其中题目的标识信息和标准的初步测试存储在一个主记录中，而其他测试保存为辅助细目表的行。然后基于题目id 与进行测试的日期将这两个表连接到一起。
在这种情况下必须回答的问题是，是否可以只用日期，或者是否需要既使用日期又使用时间。这个问题依赖于一个题目是否可以在同一天投入测试过程不止一次。如果是这样，那么应该记录时间（比方说，记录测试过程开始的时间），或者用datetime 列，或者分别用
date 和time 列（两者都必须填写）。如果一个题目一天测试了两次，没有时间值就不能将该题目的细目记录与适当的主记录进行关联。
我曾经听过有人声称“我不需要时间；我从不在同一天把一道题测试两次”。有时他们是对的，但是我也看到过这些人后来在录入同一天测试多次的题目的数据后，反过来考虑怎样防止细目记录与错误的主记录相混。很抱歉，这时已经太迟了！有时可以在表中增加time 列来处理这个问题，不幸的是，除非有某些独立的数据源，如原书面记录，否则很难整理现有记录。此外，没办法消除细目记录的歧义，以便将它们关联到合适的主记录上。即使有独立的信息源，这样做也是非常乱的，很可能使已经编写来利用表的应用程序出问题。最好是向表的拥有者说明问题并保证在创建他们的表之前进行很好的描述。
有时具有一些不完整的数据，这会干扰列类型的选择。如果进行家谱研究，需要记录出生日期和死亡日期，有时会发现所能搜集到的数据中只是某人出生或死亡的年份，但没有确切的日期。如果使用date 列，除非有完整的日期值，否则不能输入日期。如果希望能够记
录所具有的任何信息，即使不完整也保存，那么可能必须保存独立的年、月、日字段。这样就可以输入所具有的日期成员并将没有的部分设为null。在mysql3.23 及以后的版本中，还允许date 的日为0 或者月和日部分为0。这样“模糊”的日期可用来表示不完整的日期值。
2.3.2 列值有特定的取值范围吗
如果已经决定从通用类别上选择一种列类型，那么考虑想要表示的值的取值范围会有助于将您的选择缩减到该类别中特定的类型上。假如希望存储整数值。这些整数值的取值范围为0 到10 0 0，那么可以使用从smallint 到bigint 的所有类型。如果这些整数值的取值
范围最多为2 000 000，则不能使用s m a l l i n t，其选择范围从mediumint 到b i g i n t。需要从这个可能的选择范围中选取一种类型。当然，可以简单地为想要存储的值选择最大的类型（如上述例子中选择b i g i n t）。但是，一般应该为所要存储的值选择足以存储它的最小的类型。这样做，可以最小化表占用的存储量，得到最好的性能，因为通常较小列的处理比较大列的快。如果不知道所要表示的值的取值范围，那么必须进行猜测或使用bigint 以应付最坏的情况。（请注意，如果进行猜测时使用了一个太小的类型，工作不会白做；以后可以利用alter table 来将此列改为更大一些的类型。）
在第1章中，我们为学分保存方案创建了一个score 表，它有一个记录测验和测试学分的score 列。为了讨论简单起见，创建该表时使用了int 类型，但现在可以看出，如果学分在0到100 的取值范围内，更好的选择应该是tinyint unsigned，因为所用的存储空间较小。数据的取值范围还影响列类型的属性。如果该数据从不为负，可使用unsigned 属性；否则就不能用它。
串类型没有数值列那样的“取值范围”，但它们有长度，需要知道该串可使用的列最大长度。如果串短于2 5 6个字符，可使用char、varchar、tinytext 或tinyblob 等类型。如果想要更长的串，可使用text 或blob 类型，而char 和varchar 不再是
选项。对于用来表示某个固定值集合的串列，可以考虑使用enum 或set 列类型。它们可能是很好的选项，因为它们在内部是用数来表示的。这两个类型上的运算是数值化的，因此，比其他的串类型效率更高。它们还比其他串类型紧凑、节省空间。在描述必须处理的值的范围时，最好的术语是“总是”和“决不”（如“总是小于10 0 0”或“决不为负”），因为它们能更准确地约束列类型的选择。但在未确证之前，要慎用这两个术语。特别是与其他人谈他们的数据，而他们开始乱用这两个术语时要注意。在有人说“总是”或“决不”时，一定要搞清他们说的确实是这个含义。有时人们说自己的数据总是有某种特定的性质，而其真正的含义是“几乎总是”。
例如，假如您为某些人设计一个表，而他们告诉您，“我们的测试学分总是0 到10 0”。根据这个描述，您选择了tinyint 类型并使它为unsigned 的，因为值总是非负的。然而，您发现编码录入数据库的人有时用－ 1来表示“学生因病缺席”。呀，他们没告诉您这事。可能可以用null 来表示－1，但如果不能，必须记录－ 1，这样就不能用unsigned 列了（只好用alter table 来补救！）。有时关于这些情形的讨论可通过提一些简单的问题来简化，如问：曾经有过例外吗？如果曾经有过例外情况，即使是只有一次，也必须考虑。您会发现，和您讨论数据库设计的人总是认为，如果例外不经常发生，那么就没什么关系。然而在创建数据库时，就不能这样想了。需要提的问题并不是例外出现有多频繁，而是有没有例外？如果有，必须考虑进去。
2.3.3 性能与效率问题
列类型的选择会在几个方面影响查询性能。如果记住下几节讨论的一般准则，将能够选出有助于mysql有效处理表的列类型。
1. 数值与串的运算
数值运算一般比串运算更快。例如比较运算，可在单一运算中对数进行比较。而串运算涉及几个逐字节的比较，如果串更长的话，这种比较还要多。如果串列的值数目有限，应该利用enum 或set 类型来获得数值运算的优越性。这两种类型在内部是用数表示的，可更为有效地进行处理。例如替换串的表示。有时可用数来表示串值以改进其性能。例如，为了用点分四位数（d o t t e d - q ua d）表示法来表示ip 号，如19 2 . 16 8 . 0 . 4，可以使用串。但是也可以通过用四字节的unsigned 类型的每个字节存储四位数的每个部分，将ip 号转换为整数形式。这即可以节省空间又可加快查找速度。但另一方面，将ip 号表示为int 值会使诸如查找某个子网的号码这样的模式匹配难于完成。因此，不能只考虑空间问题；必须根据利用这些值做什么来决定哪种表示更适合。
2. 更小的类型与更大的类型
更小的类型比更大的类型处理要快得多。首先，它们占用的空间较小，且涉及的磁盘活动开销也少。对于串，其处理时间与串长度直接相关。一般情况下，较小的表处理更快，因为查询处理需要的磁盘i/o 少。对于定长类型的列，应该选择最小的类型，只要能存储所需范围的值即可。例如，如果mediumint 够用，就不要选择b i g i n t。如果只需要float精度，就不应该选择d o u b l e。对于可变长类型，也仍然能够节省空间。一个blob 类型的值用2 字节记录值的长度，而一个longblob 则用4 字节记录其值的长度。如果存储的值长度永远不会超过6 4 k b，使用blob 将使每个值节省2 字节（当然，对于text 类型也可以做类似的考虑）。
3. 定长与可变长类型
定长类型一般比可变长类型处理得更快：
■ 对于可变长列，由于记录大小不同，在其上进行许多删除和更改将会使表中的碎片更多。需要定期运行optimize table 以保持性能。而定长列就没有这个问题。
■ 在出现表崩溃时，定长列的表易于重新构造，因为每个记录的开始位置是确定的。可变长列就没有这种便利。这不是一个与查询处理有关的性能问题，但它必定能加快表的修复过程。如果表中有可变长的列，将它们转换为定长列能够改进性能，因为定长记录易于处理。在试图这样做之前，应该考虑下列问题：
■ 使用定长列涉及某种折衷。它们更快，但占用的空间更多。char(n) 类型列的每个值总要占用n 个字节（即使空串也是如此），因为在表中存储时，值的长度不够将在右边补空格。而varchar(n) 类型的列所占空间较少，因为只给它们分配存储每个值所需要的空间，每个值再加一个字节用于记录其长度。因此，如果在char 和varchar列之间进行选择，需要对时间与空间作出折衷。如果速度是主要关心的因素，则利用char 列来取得定长列的性能优势。如果空间是关键，应该使用varchar 列。
■ 不能只转换一个可变长列；必须对它们全部进行转换。而且必须使用一个alte rtable 语句同时全部转换，否则转换将不起作用。
■ 有时不能使用定长类型，即使想这样做也不行。例如对于比255 字符长的串，没有定长类型。
4. 可索引类型
索引能加快查询速度，因此，应该选择可索引的类型。
5. null 与not null 类型
如果定义一列为not null，其处理更快，因为mysql在查询处理中不必检查该列的值弄清它是否为null，表中每行还能节省一位。避免列中有null 可以使查询更简单，因为不需要将null 作为一种特殊情形来考虑。通常，查询越简单，处理就越快。所给出的性能准则有时是互相矛盾的。例如，根据mysql能对行定位这一方面来说，包含char 列的定长行比包含varchar 列的可变长行处理快。但另一方面，它也将占用更多的空间，因此，会导致更多的磁盘活动。从这个观点来看， varchar 可能会更快。作为一个经验规则，可假定定长列能改善性能，即使它占用更多的空间也如此。对于某个特殊的关键应用，可能会希望以定长和可变长两种方式实现一个表，并进行某些测试以决定哪种方式对您的特定应用来说更快。
2.3.4 希望对值进行什么样的比较
根据定义串的方式，可以使串类型以区分大小写或不区分大小写的方式进行比较和排序。表2-14 示出不区分大小写的每个类型及其等价的区分大小写类型。根据列定义中给不给出关键字b i n a ry，有的类型（char、varchar）是二进制编码或非二进制编码的。其他类型（b l o b、t e x t）的“二进制化”隐含在类型名中。

请注意，二进制（区分大小写）类型仅在比较和排序行为上不同于相应的非二进制（不区分大小写）类型。任意串类型都可以包含任意种类的数据。特别是， text 类型尽管在列类型名中称为“t e x t（文本）”，但它可以很好地存储二进制数据。如果希望使用一个在比较时既区分大小写，又可不区分大小写的列。可在希望进行区分大小写的比较时，利用b i n a ry 关键字强制串作为二进制串值。例如，如果my_col 为一个char 列，可按不同的方式对其进行比较：
my_col = “a b c” 不区分大小写
binary my_col =“a b c” 区分大小写
my_col = binary“a b c” 区分大小写
如果有一个希望以非字典顺序存储的串值，可考虑使用enum 列。enum 值的排序是根据列定义中所列出枚举值的顺序进行的，因此可以使这些值以任意想要的次序排序。
2.3.5 计划对列进行索引吗
使用索引可更有效地处理查询。索引的选择是第4 章中的一个主题，但一般原则是将where 子句中用来选择行的列用于索引。如果您要对某列进行索引或将该列包含在多列索引中，则在类型的选择上可能会有限定。在早于3.23.2 版的mysql发行版中，索引列必须定义为not null，并且不能对blob 或text 类型进行索引。这些限制在mysql3.23.2 版中都撤消了，但如果您正使用一个更早的版本，不能或不愿升级，那么必须遵从这些约束。不过在下列情形中可以绕过它们：
■ 如果可以指定某个值作为专用的值，那么能够将其作为与null 相同的东西对待。对于date 列，可以指定“0000 - 00 - 00”表示“无日期”。在串列中，可以指定空串代表“缺值”。在数值列中，如果该列一般只存储非负值，则可使用- 1。
■ 不能对blob 或text 类型进行索引，但如果串不超过255 它符，可使用等价的varchar 列类型并对其进行索引。可varchar(255) binary 用于blob 值，将varchar(255) 用于text 值。
2.3.6 列类型选择问题的相互关联程度
不要以为列类型的选择是相互独立的。例如，数值的取值范围与存储大小有关；在增大取值的范围时，需要更多的存储空间，这会影响性能。另外，考虑选择使用auto _increment 来创建一个存放唯一序列号的列有何含义。这个选择有几个结果，它们涉及列
的类型、索引和null 的使用，现列出如下：
■ auto_increment 是一个应该只用于整数类型的列属性。它将您的选择限定在tinyint 到bigint 之上。
■ auto_increment 列应该进行索引，从而当前最大的序列号可以很快就确定，不用对表进行全部扫描。此外，为了防止序列号被重用，索引号必须是唯一的。这表示必须将列定义为primary key 或定义为unique 索引。
■ 如果所用的mysql版本早于3 . 2 3 . 2，则索引列不能包含null 值，因此，必须定义列为not null。所有这一切表示，不能像如下这样只定义一个auto_increment 列：