Mysql入门系列：MYSQL创建、删除、索引和更改表

; 3.4 创建、删除、索引和更改表 ; 可利用CREATE TABLE、DROP TABLE 和ALTER TABLE 语句创建表，然后，对它们进行删除，更改它们的结构。对于它们中的每一条语句，存在MySQL专有的扩充，这些扩充使各语句更为有用。CREATE INDEX 和DROP INDEX 语句使您能够增加或删除现有表上的索引。 ; 3.4.1CREATE TABLE 语句 ; 用CREATE TABLE 语句创建表。此语句的完整语法是相当复杂的，因为存在那么多的可选子句，但在实际中此语句的应用相当简单。如我们在第1章中使用的所有CREATE TABLE 语句都不那么复杂。有意思的是，大多数复杂东西都是一些子句，这些子句MySQL在分析后扔掉。参阅附录D 可看到这些复杂的东西。看看CREATE TABLE 语句的各项条款，注意该语句有多少语法是用于REFERENCES CONSTRAINT 和CHECK 子句的。这些子句涉及外部键、引用完整性及输入值约束。MySQL不支持这些功能，但它分析其语法使其更容易利用在其他数据库系统中建立的表定义。（可以用较少的编辑工作更容易地利用该代码。）如果您从头开始编写自己的表描述，可以完全不管这些子句。本节中我们对它们也不多做介绍。CREATE TABLE 至少应该指出表名和表中列的清单。例如： 除构成表的列以外，在创建表时还可以说明它应该怎样索引。另一个选择是创建表时不进行索引，以后再增加索引。如果计划在开始将表用于查询前，用大量的数据填充此表，以后再创建索引是一个好办法。在插入每一行时更新索引较装载数据到一个未索引的表中然后再创建索引要慢得多。我们已经在第1章中介绍了CREATE TABLE 语句的基本语法，并在第2章讨论了怎样描述列类型。这里假定您已经读过了这两章，因此我们就不重复这些内容了。在本节下面，我们将介绍一些MySQL3.23 中对CREATE TABLE 语句的重要扩充，这些扩充在构造表方面提供了很大的灵活性，这些扩充为： ; ■ 表存储类型说明符。 ; ■ 仅当表不存在时才进行创建。 ; ■ 在客户机会话结束时自动删除临时表。 ; ■ 通过选择希望表存储的数据来创建一个表。 ; 1. 表存储类型说明符 ; 在MySQL3.23 之前，所有用户创建的表都利用的是ISAM 存储方法。在MySQL3.23中，可在CREATE TABLE 语句的列的列表之后指定TYPE = type，以三种类型明确地创建表。其中type 可以为MYISAM、ISAM 或HEAP。例如： 将表转换为HEAP 类型可能不是一个好主意，但是，如果希望表一直维持到服务器关闭，可以进行这个转换。HEAP 表在服务器退出之前，一直保留在内存中。这三种表类型的一般特点如下： ; ■ MyISAM 表。MyISAM 存储格式自版本3.23 以来是MySQL中的缺省类型，它有下列特点： ; ■ 如果操作系统自身允许更大的文件，那么文件比ISAM 存储方法的大。 ; ■ 数据以低字节优先的机器独立格式存储。这表示可将表从一种机器拷贝到另一种机器，即使它们的体系结构不同也可以拷贝。 ; ■ 数值索引值占的存储空间较少，因为它们是按高字节优先存储的。索引值在低位字节中变化很快，因此高位字节更容易比较。 ; ■ AUTO_INCREMENT 处理比ISAM 的表更好。详细内容在第2章讨论。 ; ■ 减少了几个索引限制。例如，可对含NULL 值的列进行索引，还可以对BLOB 和TEXT 类型的列进行索引。 ; ■ 为了改善表的完整性检查，每个表都具有一个标志，在myisamchk 对表进行过检查后，设置该标志。可利用myisamchk - fast 跳过对自前次检查以来尚未被修改过表的检查，这样使此管理任务更快。表中还有一个指示表是否正常关闭的标志。如果服务器关闭不正常，或机器崩溃，此标志可用来检测出服务器起动时需要检查的表。 ; ■ ISAM 表。ISAM 存储格式是MySQL3.23 所用的最旧的格式，但当前仍然可用。通常，相对于ISAM 表来说，宁可使用MyISAM 表，因为它们的限制较少。对ISAM 表的支持随着此存储格式被MyISAM 表格式所支持很有可能会逐渐消失。 ; ■ HEAP 表。HEAP 存储格式建立利用定长行的内存中的表，这使表运行得非常快。在服务器停止时，它们将会消失。在这种意义上，这些表是临时的。但是，与用CREATE TEMPORARY TABLE 所创建的临时表相比，HEAP 表是其他客户机可见的。HEAP 表有几个限制，这些限制对MyISAM 或ISAM 表没有，如下所示： ; ■ 索引仅用于“=”和“< = >”比较。 ; ■ 索引列中不能有NULL 值。 ; ■ 不能使用BLOB 和TEXT 列。 ; ■ 不能使用AUTO_INCREMENT 列。 ; 2. 创建不存在的表 ; 要创建一个不存在的表，使用CREATE TABLE IF NOT EXISTS 即可。在某种应用程序中，无法确定要用的表是否已经存在，因此，要创建这种表。IF NOT EXISTS 修饰符对于作为用mysql运行的批量作业的脚本极为有用。在这里，普通的CREATE TABLE 语句工作得不是很好。因为作业第一次运行时，建立这些表，如果这些表已经存在，则第二次运行时将出错。如果用IF NOT EXISTS语句，就不会有问题。每一次运行作业时，像前面一样创建表。如果这些表已经存在，在第二次运行时，创建表失败，但不出错。这使得作业可以继续运行，就像创建表的企图已经成功了一样。 ; 3. 临时表 ; 可用CREATE TEMPORARY TABLE 来创建临时表，这些表在会话结束时会自动消失。使用临时表很方便，因为不必费心发布DROP TABLE 语句明确地删除这些表，而且如果您的会话不正常结束，这些表不会滞留。例如，如果某个文件中有一个用mysql运行的查询，您决定不等到其结束，那么可以在其执行的中途停止这个查询，而且毫无问题，服务器将删除所创建的任意临时表。在旧版的MySQL中，没有真正的临时表，除了您在自己的头脑中认为它们是临时的除外。对于需要这样的表的应用程序，必须自己记住删除这些表。如果忘了删除，或在前面使其存在的客户机中出现错误时，这些表在有人注意到并删除它们以前会一直存在。临时表仅对创建该表的客户机可见。其名称可与一个现有的永久表相同。这不是错误，也不会使已有的永久表出问题。假如在samp_db 数据库中创建了一个名为member 的临时表。原来的member 表变成隐藏的（不可访问），对member 的引用将引用临时表。如果发布一条DROP TABLE member 语句，这个临时表将被删除，而原来的member 表“重新出现”。如果您简单地中断与服务器的连接而没有删除临时表，服务器会自动地删除它。下一次连接时，原来的member 表再次可见。名称隐藏机制仅在一个级别上起作用。即，不能创建两个具有同一个名称的临时表。 ; 4. 利用SELECT 的结果创建表 ; 关系数据库的一个重要概念是，任何数据都表示为行和列组成的表，而每条SELECT 语句的结果也都是一个行和列组成的表。在许多情况下，来自SELECT 的“表”仅是一个随着您的工作在显示屏上滚动的行和列的图像。在MySQL3.23 以前，如果想将SELECT 的结果保存在一个表中以便以后的查询使用，必须进行特殊的安排： ; 1) 运行DESCRIBE 或SHOW COLUMNS 查询以确定想从中获取信息的表中的列类型。 ; 2) 创建一个表，明确地指定刚才查看到的列的名称和类型。 ; 3) 在创建了该表后，发布一条INSERT ... SELECT 查询，检索出结果并将它们插入所创建的表中。 ; 在MySQL3.23 中，全都作了改动。CREATE TABLE ... SELECT 语句消除了这些浪费时间的东西，使得能利用SELECT 查询的结果直接得出一个新表。只需一步就可以完成任务，不必知道或指定所检索的列的数据类型。这使得很容易创建一个完全用所喜欢的数据填充的表，并且为进一步查询作了准备。可以通过选择一个表的全部内容（无WHERE 子句）来拷贝一个表，或利用一个总是失败的WHERE 子句来创建一个空表，如： 如果希望利用LOAD DATA 将一个数据文件装入原来的文件中，而不敢肯定是否具有指定的正确数据格式时，创建空拷贝很有用。您并不希望在第一次未得到正确的选项时以原来表中畸形的记录而告终。利用原表的空拷贝允许对特定的列和行分隔符用LOAD DATA 的选项进行试验，直到对输入数据的解释满意时为止。在满意之后，就可以将数据装入原表了。可结合使用CREATE TEMPORARY TABLE 与SELECT 来创建一个临时表作为它自身的拷贝，如： 这允许修改my_tbl 的内容而不影响原来的内容。在希望试验对某些修改表内容的查询，而又不想更改原表内容时，这样做很有用。为了使用利用原表名的预先编写的脚本，不需要为引用不同的表而编辑这些脚本；只需在脚本的起始处增加CREATE TEMPORARY TABLE语句即可。相应的脚本将创建一个临时拷贝，并对此拷贝进行操作，当脚本结束时服务器会自动删除这个拷贝。 ; 要创建一个作为自身的空拷贝的表，可以与CREATE TEMPORARY ... SELECT 一起使用WHERE 0 子句，例如： 但创建空表时有几点要注意。在创建一个通过选择数据填充的表时，其列名来自所选择的列名。如果某个列作为表达式的结果计算，则该列的“名称”为表达式的文本。表达式不是合法的列名，可在mysql中运行下列查询了解这一点： 如果选择了来自不同表的具有相同名称的列，将会出现一定的困难。假定表t1和t2 两者都具有列c，而您希望创建一个来自两个表中行的所有组合的表。那么可以提供别名指定新表中惟一性的列名，如： 通过选择数据进行填充来创建一个表并会自动拷贝原表的索引。 ; 3.4.2 DROP TABLE 语句 ; 删除表比创建表要容易得多，因为不需要指定有关其内容的任何东西；只需指定其名称即可，如： ; DROP TABLE tb1_name ; MySQL对DROP TABLE 语句在某些有用的方面做了扩充。首先，可在同一语句中指定几个表对它们进行删除，如： ; DROP TABLE tb1_name1,tb1_name2,...... ; 其次，如果不能肯定一个表是否存在，但希望如果它存在就删除它。那么可在此语句中增加IF EXISTS。这样，如果DROP TABLE 语句中给出的表不存在，MySQL不会发出错误信息。如： ; DROP TABLE IF EXISTS tb1_name ; IF EXISTS 在mysql所用的脚本中很有用，因为缺省情况下， mysql将在出错时退出。例如，有一个安装脚本能够创建表，这些表将在其他脚本中继续使用。在此情形下，希望保证此创建表的脚本在开始运行时无后顾之忧。如果在该脚本开始处使用普通的DROP TABLE，那么它在第一次运行时将会失败，因为这些表从未创建过。如果使用IF EXISTS，就不会产生问题了。当表已经存在时，将它们删除；如果不存在，脚本继续运行。 ; 3.4.3 创建和删除索引 ; 索引是加速表内容访问的主要手段，特别对涉及多个表的连接的查询更是如此。这是第4章“查询优化”中的一个重要内容，第4章讨论了为什么需要索引，索引如何工作以及怎样利用它们来优化查询。本节中，我们将介绍索引的特点，以及创建和删除索引的语法。 ; 1. 索引的特点 ; MySQL对构造索引提供了很大的灵活性。可对单列或多列的组合进行索引。如果希望能够从一个表的不同列中找出一个值，还可以在一个表上构造不止一个索引。如果某列为串类型而非ENUM 或SET 类型，可以选择只对该列最左边的n 个字符进行索引。如果该列的前n个字符最具有唯一性，这样做一般不会牺牲性能，而且还会对性能有大的改善：用索引列的前缀而非整个列可使索引更小且访问更快。虽然随着MySQL的进一步开发创建索引的约束将会越来越少，但现在还是存在一些约束的。下面的表根据索引的特性，给出了ISAM 表和MyISAM 表之间的差别： 从此表中可以看到，对于ISAM 表来说，其索引列必须定义为NOT NULL，并且不能对BLOB 和TEXT 列进行索引。MyISAM 表类型去掉了这些限制，而且减缓了其他的一些限制。两种表类型的索引特性的差异表明，根据所使用的MySQL版本的不同，有可能对某些列不能进行索引。例如，如果使用3.23 版以前的版本，则不能对包含NULL 值的列进行索引。 ; 如果使用的是MySQL3.23版或更新的版本，但表是过去以ISAM 表创建的，可利用ALTER TABLE 很方便地将它们转换为MyISAM 存储格式，这样使您能利用某些较新的索引功能，如： ; ALTER TABLE tb1_name TYPE=MYISAM ; 2. 创建索引 ; 在执行CREATE TABLE 语句时，可为新表创建索引，也可以用CREATE INDEX 或ALTER TABLE 来为一个已有的表增加索引。CREATE INDEX 是在MySQL3.23版中引入的，但如果使用3.23 版以前的版本，可利用ALTER TABLE 语句创建索引（MySQL通常在内部将CRE ATE INDEX 映射到ALTER TABLE）。可以规定索引能否包含重复的值。如果不包含，则索引应该创建为PRIMARY KEY 或UNIQUE 索引。对于单列惟一索引，这保证了列不包含重复的值。对于多列惟一索引，它保证值的组合不重复。 ; PRIMARY KEY 索引和UNIQUE 索引非常类似。事实上， PRIMARY KEY 索引仅是一个具有名称PRIMARY 的UNIQUE 索引。这表示一个表只能包含一个PRIMARY KEY，因为一个表中不可能具有两个同名的索引。同一个表中可有多个UNIQUE 索引，虽然这样做意义不大。 ; 为了给现有的表增加一个索引，可使用ALTER TABLE 或CREATE INDEX 语句。ALTER TABLE 最常用，因为可用它来创建普通索引、UNIQUE 索引或PRIMARY KEY 索引，如： 其中tbl_name 是要增加索引的表名，而column_list 指出对哪些列进行索引。如果索引由不止一列组成，各列名之间用逗号分隔。索引名index_name 是可选的，因此可以不写它，MySQL将根据第一个索引列赋给它一个名称。ALTER TABLE 允许在单个语句中指定多个表的更改，因此可以在同时创建多个索引。 ; CREATE INDEX 可对表增加普通索引或UNIQUE 索引，如： tbl _ name、index_name 和column_list 具有与ALTER TABLE 语句中相同的含义。这里索引名不可选。不能用CREATE INDEX 语句创建PRIMARY KEY 索引。要想在发布CREATE TABLE 语句时为新表创建索引，所使用的语法类似于ALTER TABLE 语句的语法，但是应该在您定义表列的语句部分指定索引创建子句，如下所示： 与ALTER TABLE 一样，索引名对于INDEX 和UNIQUE 都是可选的，如果未给出，MySQL将为其选一个。有一种特殊情形：可在列定义之后增加PRIMARY KEY 创建一个单列的PRIMARY KEY索引，如下所示： 前面所有表创建样例都对索引列指定了NOT NULL。如果是ISAM 表，这是必须的，因为不能对可能包含NULL 值的列进行索引。如果是MyISAM 表，索引列可以为NULL，只要该索引不是PRIMARY KEY 索引即可。 ; 如果对某个串列的前缀进行索引（列值的最左边n 个字符），应用column_list 说明符表示该列的语法为col_name(n) 而不用c o l _ name。例如，下面第一条语句创建了一个具有两个CHAR 列的表和一个由这两列组成的索引。第二条语句类似，但只对每个列的前缀进行索引： 在某些情况下，可能会发现必须对列的前缀进行索引。例如，索引行的长度有一个最大上限，因此，如果索引列的长度超过了这个上限，那么就可能需要利用前缀进行索引。在MyISAM 表索引中，对BLOB 或TEXT 列也需要前缀索引。对一个列的前缀进行索引限制了以后对该列的更改；不能在不删除该索引并使用较短前缀的情况下，将该列缩短为一个长度小于索引所用前缀的长度的列。 ; 3. 删除索引 ; 可利用DROP INDEX 或ALTER TABLE 语句来删除索引。类似于CREATE INDEX 语句，DROP INDEX 通常在内部作为一条ALTER TABLE 语句处理，并且DROP INDEX 是在MySQL3.22 中引入的。删除索引语句的语法如下： 前两条语句是等价的。第三条语句只在删除PRIMARY KEY 索引时使用；在此情形中，不需要索引名，因为一个表只可能具有一个这样的索引。如果没有明确地创建作为PRIMARY KEY 的索引，但该表具有一个或多个UNIQUE 索引，则MySQL将删除这些UNIQUE 索引中的第一个。 ; 如果从表中删除了列，则索引可能会受到影响。如果所删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。 ; 3.4.4 ALTER TABLE 语句 ; ALTER TABLE 语句是MySQL中一条通用的语句，可用它来做许多事情。我们已经看过了它的几种功能（创建和删除索引以及将表从一种存储格式转换为另一种存储格式）。本节中，我们将介绍它的一些其他功能。ALTER TABLE 的完整语法在附录D 中介绍。 ; 在发现某个表的结构不再反映所希望的东西时， ALTER TABLE很有用处。可能希望用该表记录其他信息，或者它含有多余的值。或者有的列太小，或者其定义较实际需要来说太大，需要将它们改小以节省存储空间。或者发布CREATE TABLE 语句时给出的表名不对。等等，诸如此类的问题，都可以用ALTER TABLE 语句来解决。下面是一些例子： ; ■ 您正操纵一个基于Web 的问卷，将每份提交的问卷作为表中的一个记录。后来决定修改此问卷，增加一些问题。这时必须对表增加一些列以存放新问题。 ; ■ 您正在管理一个研究项目。用AUTO_INCREMENT 列分配案例号来研究记录。您不希望经费延期太长产生多于50 000 个以上的记录，因此，令该列的类型为UNSIGNED SMALLINT，它能存储的最大惟一值为65 535。但是，项目的经费延长了，似乎可能另外产生50 000 个记录。这时，需要使该列的类型更大一些以便存储更多的件号。 ; ■ 大小的更改也可能是反方向的。可能创建了一个CHAR(255) 列，但现在发现表中没有比100 个字符更长的串。这时可缩短该列以节省存储空间。ALTER TABLE 的语法如下： 每个action 表示对表所做的一个修改。MySQL扩充了ALTER TABLE 语句，允许指定多个动作，各动作间以逗号分隔。这对于减少键盘输入很有用，但这个扩充的更为重要的原因是，除非能同时将所有VARCHAR 列更改为CHAR 列，否则不可能将表从行可变长的表更改为行定长的表。 ; 下面的例子示出了某些ALTER TABLE 的功能。 ; ■ 对表重新命名。这很简单；只需给出旧表名和新表名即可： 在MySQL3.23 中有临时表，重命名一个临时表为数据库中已经存在的名称将隐藏原始表，只要临时表存在就会隐藏原始表。这类似于通过用相同的名字创建一个临时表来隐藏一个表的方法。 ; ■ 更改列类型。为了更改列的类型，可使用CHANGE 或MODIFY 子句。假如表my _ t b l中的列为SMALLINT UNSIGNED 的，希望将其更改为MEDIUMINT UNSIGNED 的列。用下面的任何一个命令都可完成此项工作： 为什么在CHANGE 命令中给出列名两次？因为CHANGE 可以做的而MODIFY 不能做的一桩事是，除了更改类型外还能更改列名。如果希望在更改类型的同时重新将i 命名为j，可按如下进行： 重要的是命名了希望更改的列，并说明了一个包括列名的列的完整定义。即使不更改列名，也需要在定义中包括相应的列名。 ; 更改列类型的一个重要原因是为了改善比较两个表的连接查询的效率。在两个列的类型相同时，比较更快。假如执行如下的查询： 如果t1.name 为CHAR( 10 )，而t2.name 为CHAR( 15 )，此查询的运行速度没有它们两者都为CHAR(15) 时的快。那么可以用下面的任一条命令更改t1.name 使它们的类型相同： 对于3 . 2 3以前的MySQL版本，所连接的列必须是同样类型的这一点很重要，否则索引不能用于比较。对于版本3.23 或以上的版本，索引可用于不同的类型，但如果类型相同，查询仍然更快。 ; ■ 将表从可变长行转换为定长行。假如有一个表chartbl 具有VARCHAR 列，想要把它转换为CHAR 列，看看能够得到什么样的性能改善。（定长行的表一般比变长行的表处理更快。）这个表如下创建： 这里的问题是需要在相同的ALTER TABLE 语句中一次更改所有的列。不可能一次一列地改完，或者说这个企图将不起作用。如果执行DESCRIBE chartbl，会发现两个列仍然是VARCHAR 的列！原因是如果每次更改一列， MySQL注意到表仍然包含有可变长的列，则会把已经更改过的列重新转换为VARCHAR 以节省空间。为了处理这个问题，应该同时更改所有VARCHAR 列： 现在DESCRIBE 将显示该表包含的都是CHAR 列。确实，这种类型的操作很重要，因为它使ALTER TABLE 能在相同的语句中支持多个动作。 ; 这里要注意，在希望转换这样的表时：如果表中存在BLOB 或TEXT 列将使转换表为定长行格式的企图失败。即使表中只有一个可变长的列都将会使表有可变长的行，因为这些可变长的列类型没有定长的等价物。 ; ■ 将表从定长行转换为可变长的行。虽然， chartbl 用定长行更快，但它要占用更多的空间，因此决定将它转换回原来的形式以节省空间。这种转换更为容易。只需将某个CHAR 列转换为VARCHAR 列，MySQL就自动地转换其他的CHAR 列。要想转换chartbl 表，用下列任一条语句都可以： ■ 转换表的类型。如果从MySQL3.23 版以前的版本升级到3.23 版或更高，那么可能会有一些原来创建为ISAM 表的旧表。如果希望使它们为MyISAM 格式，如下操作：为什么要这样做呢？正如在“创建和删除索引”小节中所介绍的那样，一个原因是MyISAM 存储格式具有某些ISAM 格式没有的索引特性，例如能够对NULL 值、BLOB 和TEXT 列类型进行索引。另一个原因为， MyISAM 表是独立于机器的，因此可通过将它们直接拷贝来将它们移到其他机器上，即使那些机器具有不同的硬件体系结构也同样。这在第11章中将要作进一步的介绍。