MySQL数据库技术（14)

【 tulaoshi.com - 编程语言 】

　　3.4 创建、删除、索引和更改表

    可利用C R E ATE TA B L E、DROP TABLE 和A LTER TABLE 语句创建表，然后，对它们进行删除，更改它们的结构。对于它们中的每一条语句，存在MySQL 专有的扩充，这些扩充使各语句更为有用。C R E ATE INDEX 和DROP INDEX 语句使您能够增加或删除现有表上的索引。

    3.4.1 CREATE TABLE 语句

    用CREATE TABLE 语句创建表。此语句的完整语法是相当复杂的，因为存在那么多的可选子句，但在实际中此语句的应用相当简单。如我们在第1 章中使用的所有CREATE TABLE 语句都不那么复杂。有意思的是，大多数复杂东西都是一些子句，这些子句MySQL在分析后扔掉。参阅附录D 可看到这些复杂的东西。看看CREATE TABLE 语句的各项条款，注意该语句有多少语法是用于REFERENCES CONSTRAINT 和CHECK 子句的。这些子句涉及外部键、引用完整性及输入值约束。MySQL 不支持这些功能，但它分析其语法使其更容易利用在其他数据库系统中建立的表定义。（可以用较少的编辑工作更容易地利用该代码。）如果您从头开始编写自己的表描述，可以完全不管这些子句。本节中我们对它们也不多做介绍。CREATE TABLE 至少应该指出表名和表中列的清单。例如：

    除构成表的列以外，在创建表时还可以说明它应该怎样索引。另一个选择是创建表时不进行索引，以后再增加索引。如果计划在开始将表用于查询前，用大量的数据填充此表，以后再创建索引是一个好办法。在插入每一行时更新索引较装载数据到一个未索引的表中然后再创建索引要慢得多。我们已经在第1章中介绍了C R E ATE TABLE 语句的基本语法，并在第2章讨论了怎样描述列类型。这里假定您已经读过了这两章，因此我们就不重复这些内容了。在本节下面，我们将介绍一些MySQL 3.23 中对C R E ATE TABLE 语句的重要扩充，这些扩充在构造表方面提供了很大的灵活性，这些扩充为：

    ■ 表存储类型说明符。

    ■ 仅当表不存在时才进行创建。

    ■ 在客户机会话结束时自动删除临时表。

    ■ 通过选择希望表存储的数据来创建一个表。

    1. 表存储类型说明符

    在MySQL 3.23 之前，所有用户创建的表都利用的是ISAM 存储方法。在MySQL 3.23中，可在CREATE TABLE 语句的列的列表之后指定TYPE = type，以三种类型明确地创建表。其中type 可以为MYISAM、ISAM 或HEAP。例如：

    将表转换为HEAP 类型可能不是一个好主意，但是，如果希望表一直维持到服务器关闭，可以进行这个转换。HEAP 表在服务器退出之前，一直保留在内存中。这三种表类型的一般特点如下：

    ■ MyISAM 表。MyISAM 存储格式自版本3.23 以来是MySQL 中的缺省类型，它有下列特点：

    ■ 如果操作系统自身允许更大的文件，那么文件比ISAM 存储方法的大。

    ■ 数据以低字节优先的机器独立格式存储。这表示可将表从一种机器拷贝到另一种机器，即使它们的体系结构不同也可以拷贝。

    ■ 数值索引值占的存储空间较少，因为它们是按高字节优先存储的。索引值在低位字节中变化很快，因此高位字节更容易比较。

    ■ A U TO_INCREMENT 处理比ISAM 的表更好。详细内容在第2章讨论。

    ■ 减少了几个索引限制。例如，可对含NULL 值的列进行索引，还可以对BLOB 和TEXT 类型的列进行索引。

    ■ 为了改善表的完整性检查，每个表都具有一个标志，在myisamchk 对表进行过检查后，设置该标志。可利用myisamchk - fast 跳过对自前次检查以来尚未被修改过表的检查，这样使此管理任务更快。表中还有一个指示表是否正常关闭的标志。如果服务器关闭不正常，或机器崩溃，此标志可用来检测出服务器起动时需要检查的表。

    ■ ISAM 表。ISAM 存储格式是MySQL 3.23 所用的最旧的格式，但当前仍然可用。通常，相对于ISAM 表来说，宁可使用MyISAM 表，因为它们的限制较少。对ISAM 表的支持随着此存储格式被MyISAM 表格式所支持很有可能会逐渐消失。

    ■ HEAP 表。HEAP 存储格式建立利用定长行的内存中的表，这使表运行得非常快。在服务器停止时，它们将会消失。在这种意义上，这些表是临时的。但是，与用CREATE TEMPORARY TABLE 所创建的临时表相比，HEAP 表是其他客户机可见的。HEAP 表有几个限制，这些限制对MyISAM 或ISAM 表没有，如下所示：

    ■ 索引仅用于“=”和“ = ”比较。

    ■ 索引列中不能有NULL 值。

    ■ 不能使用BLOB 和TEXT 列。

    ■ 不能使用A U TO_INCREMENT 列。

    2. 创建不存在的表

    要创建一个不存在的表，使用C R E ATE TABLE IF NOT EXISTS 即可。在某种应用程序中，无法确定要用的表是否已经存在，因此，要创建这种表。IF NOT EXISTS 修饰符对于作为用mysql 运行的批量作业的脚本极为有用。在这里，普通的C R E ATE 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 的“表”仅是一个随着您的工作在显示屏上滚动的行和列的图像。在MySQL 3.23 以前，如果想将SELECT 的结果保存在一个表中以便以后的查询使用，必须进行特殊的安排：

    1) 运行DESCRIBE 或SHOW COLUMNS 查询以确定想从中获取信息的表中的列类型。

    2) 创建一个表，明确地指定刚才查看到的列的名称和类型。

    3) 在创建了该表后，发布一条I N S E RT ... SELECT 查询，检索出结果并将它们插入所创建的表中。

    在MySQL 3.23 中，全都作了改动。C R E ATE TABLE ... SELECT 语句消除了这些浪费时间的东西，使得能利用SELECT 查询的结果直接得出一个新表。只需一步就可以完成任务，不必知道或指定所检索的列的数据类型。这使得很容易创建一个完全用所喜欢的数据填充的表，并且为进一步查询作了准备。可以通过选择一个表的全部内容（无WHERE 子句）来拷贝一个表，或利用一个总是失
败的WHERE 子句来创建一个空表，如：

    如果希望利用LOAD DATA 将一个数据文件装入原来的文件中，而不敢肯定是否具有指定的正确数据格式时，创建空拷贝很有用。您并不希望在第一次未得到正确的选项时以原来表中畸形的记录而告终。利用原表的空拷贝允许对特定的列和行分隔符用LOAD DATA 的选项进行试验，直到对输入数据的解释满意时为止。在满意之后，就可以将数据装入原表了。可结合使用C R E ATE TEMPORARY TABLE 与SELECT 来创建一个临时表作为它自身的拷贝，如：

    这允许修改my_tbl 的内容而不影响原来的内容。在希望试验对某些修改表内容的查询，而又不想更改原表内容时，这样做很有用。为了使用利用原表名的预先编写的脚本，不需要为引用不同的表而编辑这些脚本；只需在脚本的起始处增加CREATE TEMPORARY TA B L E语句即可。相应的脚本将创建一个临时拷贝，并对此拷贝进行操作，当脚本结束时服务器会自动删除这个拷贝。

    要创建一个作为自身的空拷贝的表，可以与C R E ATE 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 值的列进行索引。

    如果使用的是MySQL 3.23版或更新的版本，但表是过去以ISAM 表创建的，可利用ALTER TABLE 很方便地将它们转换为MyISAM 存储格式，这样使您能利用某些较新的索引功能，如：

    ALTER TABLE tb1_name TYPE=MYISAM

    2. 创建索引

    在执行C R E ATE TABLE 语句时，可为新表创建索引，也可以用CREATE INDEX 或ALTER TABLE 来为一个已有的表增加索引。C R E ATE INDEX 是在MySQL 3.23版中引入的，但如果使用3.23 版以前的版本，可利用A LTER TABLE 语句创建索引（MySQL 通常在内部将CRE ATE INDEX 映射到A LTER TA B L E）。可以规定索引能否包含重复的值。如果不包含，则索引应该创建为P R I M A RY 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 允许在单个语句中指定多个表的更改，因此可以在同时创建多个索引。