MySQL数据库技术（14)[MySQL防范]

? 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 答应在单个语句中指定多个表的更改,因此可以在同时成立多个索引.
? ? CREATE INDEX 可对表增添普通索引或UNIQUE 索引,如:

? ? tbl _ name、index_name 和column_list 具有与A LTER TABLE 语句中相同的含义.这里索引名不可选.不能用CREATE INDEX 语句成立PRIMARY KEY 索引.要想在公布CREATE TABLE 语句时为新表成立索引,所利用的语法近似于ALTER TABLE 语句的语法,但是应当在您定义表列的语句部份指定索引成立子句,以下所示:

? ? 与ALTER TABLE 一样,索引名关于INDEX 和UNIQUE 都是可选的,假如未给出,MySQL 将为其选一个.有一种特别情形:可在列定义之后增添PRIMARY KEY 成立一个单列的PRIMARY KEY索引,以下所示:

? ? 前面全部表成立样例都对索引列指定了NOT NULL.假如是ISAM 表,这是必须的,因为不能对大概包含NULL 值的摆列行索引.假如是MyISAM 表,索引列可认为N U L L,只要该索引不是P R I M A RY KEY 索引便可.
? ? 假如对某个串列的前缀举行索引（列值的最左边n 个字符）,利用column_list 阐明符表示该列的语法为col_name(n) 而不用c o l _ name.比方,下面第一条语句成立了一个具有两个CHAR 列的表和一个由这两列构成的索引.第二条语句近似,但只对每个列的前缀举行索引:

? ? 在某些情形下,大概会发现必须对列的前缀举行索引.比方,索引行的长度有一个最大上限,因此,假如索引列的长度超越了这个上限,那么便大概需求操纵前缀举行索引.在MyISAM 表索引中,对BLOB 或TEXT 列也需求前缀索引.对一个列的前缀举行索引限制了今后对该列的更改;不能在不删除该索引并利用较短前缀的情形下,将该列缩短为一个长度小于索引所用前缀的长度的列.
? ? 3. 删除索引
? ? 可操纵DROP INDEX 或A LTER TABLE 语句来删除索引.近似于CREATE INDEX 语句,DROP INDEX 普通在内部作为一条A LTER TABLE 语句处理,并且DROP INDEX 是在MySQL 3.22 中引入的.删除索引语句的语法以下:

? ? 前两条语句是等价的.第三条语句只在删除PRIMARY KEY 索引时利用;在此情形中,不需求索引名,因为一个表只大概具有一个这样的索引.假如没有明确地成立作为PRIMARY KEY 的索引,但该表具有一个或多个UNIQUE 索引,则MySQL 将删除这些UNIQUE 索引中的第一个.
? ? 假如从表中删除了列,则索引大概会遭到影响.假如所删除的列为索引的构成部份,则该列也会从索引中删除.假如构成索引的全部列都被删除,则整个索引将被删除.
? ? 3.4.4 ALTER TABLE 语句
? ? ALTER TABLE 语句是MySQL 中一条通用的语句,可用它来做很多事情.我们已经看过了它的几种功效（成立和删除索引以及将表从一种存储格局转换为另一种存储格局）.本节中,我们将介绍它的一些其他功效.ALTER TABLE 的完好语法在附录D 中介绍.
? ? 在发现某个表的构造不再反映所但愿的东西时, A LTER TA B L E很有效处.大概但愿用该表记录其他信息,大概它含有多余的值.大概有的列太小,大概其定义较实际需求来说太大,需求将它们改小以节俭存储空间.大概公布C R E ATE TABLE 语句时给出的表名不对.等等,诸如此类的问题,都可以用A LTER TABLE 语句来办理.下面是一些例子:
? ? ■ 您正操作一个基于Web 的问卷,将每份提交的问卷作为表中的一个记录.后来决意改正此问卷,增添一些问题.这时必须对表增添一些列以存放新问题.
? ? ■ 您正在管理一个研究项目.用AUTO_INCREMENT 列分配案例号来研究记录.您不但愿经费延期太长产生多于50 000 个以上的记录,因此,令该列的范例为UNSIGNED SMALLINT,它能存储的最大惟一值为65 535.但是,项目的经费延伸了,仿佛大概别的产生50 000 个记录.这时,需求使该列的范例更大一些以便存储更多的件号.
? ? ■ 大小的更改也大概是反方向的.大概成立了一个CHAR(255) 列,但目前发现表中没有比100 个字符更长的串.这时可缩短该列以节俭存储空间.ALTER TABLE 的语法以下:

? ? 每个action 表示对表所做的一个改正.MySQL 扩大了A LTER TABLE 语句,答应指定多个行动,各行动间以逗号脱离.这关于削减键盘输入很有效,但这个扩大的更为重要的缘由是,除非能同时将全部VARCHAR 列更改成CHAR 列,不然不大概将表从行可变长的表更改成行定长的表.
? ? 下面的例子示出了某些A LTER TABLE 的功效.
? ? ■ 对表重新命名.这很简单;只需给出旧表名和新表名便可:

? ? 在MySQL 3.23 中有暂时表,重命名一个暂时表为数据库中已经存在的名称将躲藏原始表,只要暂时表存在就会躲藏原始表.这近似于通过用相同的名字成立一个暂时表来躲藏一个表的办法.
? ? ■ 更改列范例.为了更改列的范例,可以利用CHANGE 或MODIFY 子句.假定表m y _ t b l中的列为SMALLINT UNSIGNED 的,但愿将其更改成MEDIUMINT UNSIGNED 的列.用下面的任何一个号令都可完成此项工作:

? ? 为什么在CHANGE 号令中给出列名两次?因为CHANGE 可以做的而MODIFY 不能做的一桩事是,除了更改范例外还能更改列名.假如但愿在更改范例的同时重新将i 命名为j,可按以下举行:

? ? 重要的是命名了但愿更改的列,并阐明了一个包含列名的列的完好定义.即便不更改列名,也需求在定义中包含呼应的列名.
? ? 更改列范例的一个重要缘由是为了改进对比两个表的衔接查询的效率.在两个列的范例相同时,对比更快.假定履行以下的查询:

? ? 假如t1.name 为C H A R ( 1 0 ),而t2.name 为C H A R ( 1 5 ),此查询的运行速度没有它们二者都为CHAR(15) 时的快.那么可以用下面的任一条号令更改t1.name 使它们的范例相同:

? ? 关于3 . 2 3从前的MySQL 版本,所衔接的列必须是一样范例的这一点很重要,不然索引不能用于对比.关于版本3.23 或以上的版本,索引可用于差别的范例,但假如范例相同,查询仍旧更快.
? ? ■ 将表从可变长行转换为定长行.假定有一个表chartbl 具有VARCHAR 列,想要把它转换为CHAR 列,看看可以得到什么样的性能改进.（定长行的表普通比变长行的表处理更快.）这个表以下成立:

? ? 这里的问题是需求在相同的A LTER TABLE 语句中一次更改全部的列.不大概一次一列地改完,大概说这个计划将不起作用.假如履行DESCRIBE chartbl,会发现两个列仍旧是VARCHAR 的列!缘由是假如每次更改一列, MySQL 注意到表仍旧包含有可变长的列,则会把已经更改过的列重新转换为VARCHAR 以节俭空间.为了处理这个问题,应当同时更改全部VARCHAR 列:

? ? 目前DESCRIBE 将显示该表包含的都是CHAR 列.确切,这种范例的操作很重要,因为它使A LTER TABLE 能在相同的语句中支持多个行动.
? ? 这里要注意,在但愿转换这样的表时:假如表中存在BLOB 或TEXT 列将使转换表为定长行格局的计划失利.即便表中只有一个可变长的列都将会使表有可变长的行,因为这些可变长的列范例没有定长的等价物.
? ? ■ 将表从定长行转换为可变长的行.固然, chartbl 用定长行更快,但它要占用更多的空间,因此决意将它转换回本来的情势以节俭空间.这种转换更为简单.只需将某个CHAR 列转换为VARCHAR 列,MySQL 就自动地转换其他的CHAR 列.要想转换chartbl 表,用下列任一条语句都可以:

? ? ■ 转换表的范例.假如从MySQL 3.23 版从前的版本进级到3.23 版或更高,那么大概会有一些本来成立为ISAM 表的旧表.假如但愿使它们为MyISAM 格局,以下操作:

? ?为什么要这样做呢?正如在"成立和删除索引"小节中所介绍的那样,一个缘由是MyISAM 存储格局具有某些ISAM 格局没有的索引特点,比方可以对NULL 值、BLOB 和TEXT 列范例举行索引.另一个缘由为, MyISAM 表是独立于机械的,因此可通过将它们直接拷贝来将它们移到其他机械上,即便那些机械具有差别的硬件体系构造也一样.这在第11章中将要作进一步的介绍.

　　以上是“MySQL数据库技术（14)[MySQL防范]”的内容，如果你对以上该文章内容感兴趣，你可以看看七道奇为您推荐以下文章：