MSSQL网站制作之InnoDB 中文参考手册 --- 4 创建 InnoD...

其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。参考|参考手册|中文4创建InnoDB表
假定你已以mysqltest命令体例运转了MySQL客户端程序。为了创建一个InnoDB格局的表你必需在SQL命令中指定TYPE=InnoDB：

CREATETABLECUSTOMER(AINT,BCHAR(20),INDEX(A))TYPE=InnoDB;

这个SQL命令将在my.cnf中设定的InnoDB数据文件中创立一个表和一个列A的索引，同时将在MySQL数据目次下的test中创建一个名为CUSTOMER.frm的文件。在外部，InnoDB将在它本人的数据字典(datadictionary)中增加表“test/CUSTOMER”的进进点。如许你能够在别的的MySQL数据库中创建一个一样名为CUSTOMER的数据表，它将不会与InnoDB中的表抵触。

能够经由过程收回MySQL的检察表形态命令检察任何一个InnoDB范例表以检察InnoDB数据文件的残剩空间。SHOW输入信息中的表正文部分将显现数据文件的残剩空间。示例：

SHOWTABLESTATUSFROMtestLIKECUSTOMER

注重：SHOW给出的InnoDB表统计只是近似的：他们被SQL优化。但是，表和索引的reservedsizesinbytes是准确的。

要出格注重不要在InnoDB数据库中手动增加与删除“.frm”文件：利用CREATETABLE和DROPTABLE命令。InnoDB有它本人的外部数据字典(datadictionary)，假如MySQL的“.frm”文件与InnoDB外部的数据字典不“同步”将发生一个毛病。
4.1怎样经由过程分歧的APIs在InnoDB中利用事件
默许的，MySQL老是启动主动提交你运转的每一个SQL语句的主动提交(autocommit)形式创立每的新毗连。为了利用事件，可使用SQL命令SETAUTOCOMMIT=0封闭主动提交(autocommit)开关，利用COMMIT和ROLLBACK来提交事件和回滚事件。假如你想保存autocommit开关翻开，你能够将事件放进BEGIN和COMMIT或ROLLBACK之间。
heikki@hundin:~/mysql/client>mysqltestWelcometotheMySQLmonitor.Commandsendwith;org.YourMySQLconnectionidis5toserverversion:3.23.50-logTypehelp;orhforhelp.Typectoclearthebuffer.mysql>CREATETABLECUSTOMER(AINT,BCHAR(20),INDEX(A))TYPE=InnoDB;QueryOK,0rowsaffected(0.00sec)mysql>BEGIN;QueryOK,0rowsaffected(0.00sec)mysql>INSERTINTOCUSTOMERVALUES(10,Heikki);QueryOK,1rowaffected(0.00sec)mysql>COMMIT;QueryOK,0rowsaffected(0.00sec)mysql>SETAUTOCOMMIT=0;QueryOK,0rowsaffected(0.00sec)mysql>INSERTINTOCUSTOMERVALUES(15,John);QueryOK,1rowaffected(0.00sec)mysql>ROLLBACK;QueryOK,0rowsaffected(0.00sec)mysql>SELECT*FROMCUSTOMER;+------+--------+|A|B|+------+--------+|10|Heikki|+------+--------+1rowinset(0.00sec)mysql>

　

经由过程APIs(好比PHP,PerlDBI/DBD,JDBC,ODBC,或MySQL的尺度C挪用接口)，发送一个事件把持语句(好比"COMMIT")到MySQL服务器能够好像别的的SQL语句。比方："SELECT..."或"INSERT..."。APIsoftencontainseparatespecialcommit-transactionmethods,可是MySQL对事件的撑持还绝对对照年老，他们并非在一切版本的APIs上均能事情的。
4.2将MyISAM表转换到InnoDB范例主要提示：不成以将MySQL体系表好比user或host转换成InnoDB范例。体系表必需为MyISAM范例。
　

假如你但愿一切你新建的表是InnoDB范例，从MySQLversion3.23.43入手下手，你能够在你的my.cnf或my.ini文件的[mysqld]项下到场以下一行
default-table-type=innodb

　

InnoDB没有一个特别的分册索引创建优化机制。因而不撑持先export/import表然后创立索引。最快的举措就是间接将表范例改动为InnoDB范例或间接拔出数据，这就是说，利用ALTERTABLE...TYPE=INNODB或新建一个空的具有不异布局的InnoDB表，然后利用INSERTINTO...SELECT*FROM....拔出数据。

假如有UNIQUE束缚，从3.23.52入手下手为了进步拔出速率你能够封闭独一性反省：
SETUNIQUE_CHECKS=0;
关于较年夜的表，在向InnoDB中拔出时会利用索引缓冲区来兼并帮助索引纪录来分批拔出，这将削减很多磁盘的I/O。
　

为了更好地把持拔出历程，最好将年夜的表分批拔出：

INSERTINTOnewtableSELECT*FROMoldtableWHEREyourkey>somethingANDyourkey<=somethingelse;

等全体数据拔出终了后，能够从头定名表名。

在拔出过程当中，要将InnoDB的缓冲池(bufferpool)设年夜一点以削减磁盘I/O，但是不要凌驾物理内存的80%。同时还应当将日记文件和日记缓冲加年夜。

必要注重的是不要让表空间用尽：InnoDB表比MyISAM表利用更多的内存。假如ALTERTABLE用光了表空间，将会回滚，假如磁盘不敷的话，这个历程将延续几个小时。在向InnoDB中拔出时会利用索引缓冲区来兼并帮助索引纪录来分批拔出，这将削减良多磁盘I/O。而在回滚中其实不利用这类机制，这将比前者多利用30倍的工夫。

为了加入掉控的回滚，假如在你的导进表中没有甚么主要的数据，从3.23.53和4.0.3入手下手可使用技能加入回滚，请检察6.1加入掉控的回滚(stoptherunawayrollback)。
4.3外键束缚
从3.23.43b入手下手InnoDB撑持外键束缚特征。InnoDB表范例第一次为MySQL供应了外键束缚以包管你的数据完全性。

InnoDB中外键束缚的界说语法以下所示：
[CONSTRAINTsymbol]FOREIGNKEY[id](index_col_name,...)REFERENCEStable_name(index_col_name,...)[ONDELETE{CASCADE|SETNULL|NOACTION|RESTRICT}][ONUPDATE{CASCADE|SETNULL|NOACTION|RESTRICT}]
两个表必需为InnoDB范例，外键和被援用键(referencedkey)必需是索引中的第一(FIRST)列。InnoDB不会主动为外键和被援用键创建索引，必需明白创立它们。
　

外键与对应的被援用键在InnoDB内必需有类似的外部数据范例，以便他们不必要一个范例转换就能够举行对照。整型(Integer)字段的长度与有标记范例(signedness)必需分歧。字符型则不必要分歧。假如指定了一个SETNULL举措，那你必需要断定子表中的对应字段没有界说为NOTNULL。

假如CREATETABLE给出1005号毛病，毛病信息字符串提醒毛病号(errno)150，那末就是由于外键束缚未被准确创建而招致表创立失利。一样的，假如一条ALTERTABLE失利而前往毛病号150，那就意味着alteredtable未能准确界说一个外键。从4.0.13入手下手，你能够经由过程利用SHOWINNODBSTATUS来检察服务器是最初一条InnoDB的外键毛病的具体申明。

从3.23.50入手下手，InnoDB不再在同意NULL值外键或被援用键上反省外键束缚。

与SQL尺度纷歧致：ifintheparenttablethereareseveralrowswhichhavethesamereferencedkeyvalue,thenInnoDBactsinforeignkeychecksliketheotherparentrowswiththesamekeyvaluewouldnotexist.Forexample,ifyouhavedefinedaRESTRICTtypeconstraint,andthereisachildrowwithseveralparentrows,InnoDBdoesnotallowthedeletionofanyofthoseparentrows.

从3.23.50入手下手，大概团结ONDELETECASCADE或ONDELETESETNULL子句与外键束缚一同感化。响应的ONUPDATE选项将从4.0.8入手下手撑持。假如ONDELETECASCADE被指定，当主表中的纪录行被删除时，InnoDB将主动删除子表中被援用键值与主表中绝对应的外键值不异的纪录。假如ONDELETESETNULL被指定，子表中的外键对应即将被设置为NULL值。

与SQL尺度纷歧致：ifONUPDATECASCADEorONUPDATESETNULLrecursestoupdatetheSAMETABLEithasalreadyupdatedduringthecascade,itactslikeRESTRICT.Thisistopreventinfiniteloopsresultingfromcascadedupdates.Aself-referentialONDELETESETNULL,ontheotherhand,worksstartingfrom4.0.13.Aself-referentialONDELETECASCADEhasalwaysworked.

示例：
CREATETABLEparent(idINTNOTNULL,PRIMARYKEY(id))TYPE=INNODB;CREATETABLEchild(idINT,parent_idINT,INDEXpar_ind(parent_id),FOREIGNKEY(parent_id)REFERENCESparent(id)ONDELETECASCADE)TYPE=INNODB;

　

从3.23.50入手下手，InnoDB同意经由过程上面的办法给一个表增加一个外键束缚：
ALTERTABLEyourtablenameADD[CONSTRAINTsymbol]FOREIGNKEY[id](...)REFERENCEStable_name(index_col_name,...)[ONDELETE{CASCADE|SETNULL|NOACTION|RESTRICT}][ONUPDATE{CASCADE|SETNULL|NOACTION|RESTRICT}]
记着起首要创建需要的索引，只管能够经由过程ALTERTABLE为一个表创建一个自参考(self-referential)的外键。
从4.0.13入手下手，InnoDB撑持
ALTERTABLEDROPFOREIGNKEYinternally_generated_foreign_key_id
当你必要删除一个外键时可使用SHOWCREATETABLE来检察internallygeneratedforeignkeyid。




假如要导进表的几个转储(dump)，而数据并没有按外键排序，从3.23.52和4.0.3入手下手，能够在导进时封闭外键反省：
SETFOREIGN_KEY_CHECKS=0;
这就同意以任何按次导进数据，同时进步导进速率。
　

从3.23.50入手下手，InnoDB语法剖析器(parser)同意你backquotesaroundtable和将列名放进FOREIGNKEY...REFERENCES...子句中。从4.0.5入手下手，InnoDB语法剖析器能处置my.cnf文件大概设置的lower_case_table_names。

在小于3.23.50的版本中，InnoDB任何ALTERTABLE或CREATEINDEX均不克不及在利用在有外键束缚或被援用键束缚的表上：任何ALTERTABLE都将删除表中界说的外键束缚。不克不及再利用ALTERTABLE来任何一个表，只要经由过程DROPTABLE和CREATETABLE来修正。当MySQL实行一个ALTERTABLE时，在外部处置上是经由过程RENAMETABLE来完成的，这将引发外键束缚对表的援用凌乱。一样CREATEINDEX语句也是作为ALTERTABLE来处置的，也不克不及用于外键束缚的表。

当InnoDB举行外键反省时会对主表与子表数据加行锁。nnoDB会当即反省外键束缚：反省不会比及事件提交。

InnoDB同意你drop任何表，即便如许会冲破外键，如许操纵的了局就是束缚也被drop了。

InnoDB同意你取消(drop)任何表，即便如许会冲破被援用表的外键束缚。当你取消一个表时束缚也同时被打消了。

假如从头创立一个被取消的表，必需参考原有界说创建分歧的外键束缚。必需有准确的列我与范例。必需在援用键上有索引。假如不切合下面的前提，MySQL将前往1005号毛病，毛病信息字符串提醒毛病号(errno)150。

从3.23.50入手下手，经由过程以下指令可使InnoDB前往表的外键束缚界说
SHOWCREATETABLEyourtablename
还能够经由过程mysqldump将表的完全界说转储到文件中，固然包含外键界说。
　

还能够经由过程上面的指令列出表T的外键束缚：
SHOWTABLESTATUSFROMyourdatabasenameLIKET
外键束缚将会在表正文中列出。
　
4.4自增列(auto-increment)是怎样在InnoDB中事情的
假如表有一个自增(auto-increment)列，那末InnoDB表处置体系将它的数据字典中包括一个出格的计数器用以纪录自增列的下一个列值。自增计数器只放于主存中，而不是放在磁盘中。
　
InnoDB利用以下划定规矩初始化自增计数器。数据库启动后，当用户第一次向表T拔出数据或运转SHOWTABLESTATUS来显现表T时，InnoDB将实行
SELECTMAX(auto-inc-column)FROMTFORUPDATE,
同时将所得值加1现填进字段并纪录表的自增纪录器。假如表是空的则将值赋为1。注重在这个初始化过程当中将为表加一个读锁(anormalx-lockingread)，这个锁将一向延续到事件处置停止。
　

InnoDB为一个新创建的表以一样的体例创建自增计数器。

假如为一个自增列出格指定值0，那末InnoDB将视为未为该列指定值而将该列赋于新值。

在自增计数器初始化后，在自增列中拔出一个明白指定的新值，而且该值年夜于以后计数值，那末计数器将被设置为新的指定值。假如用户没有明白为它指定一个值，InnoDB将自增计数器，并将新值赋于自增列。

当会见自增计数器时，InnoDB将利用一个特别的表级锁定(AUTO-INClock)，锁将一向坚持到以后SQL语句的运转停止，而不是线程的停止。特别的锁开释战略被引进是为了改良向有自增字段的表中拔出数据的并发功能。两个事件处置不克不及在统一张表上创建AUTO-INC锁。

注重假如事件从自增计数器中取值，那末当事件回滚时在自增列按次上大概会发生清闲。

假如给一个自增字段指定一个有效值或值比界说的整型范例最年夜值域还年夜，那末自增机制的形态将没法预知。
4.5InnoDB和MySQL复制(replication)
MySQL的复制特性(replicationfeature)在InnoDB表上的事情就与它在MyISAM表范例上一样。在master中的表范例与slave中的表范例是纷歧致时利用复制(replication)是大概的。举例来讲，你能够将master中的一个InnoDB表的变动复制到一个slave中的MyISAM表中往。

为了设立一个master的新的slave，你必需创建一份InnoDB表空间和日记文件(logfiles)的复本，一样也包含InnoDB表绝对应的.frm文件，并将这些复本挪动到slave中。这看上往有点像上面章节7关于挪动一个InnoDB数据库的申明。假如你能够封闭master，你能够创建一个InnoDB表空间和日记文件的冷备份(coldbackup)来创建一个slave。为了创建一个新的slave而不将master数据库封闭，你可使用一个非收费的InnoDBHotBackuptool。

在InnoDB复制中的一引发小限定：
LOADTABLEFROMMASTER不克不及够在InnoDB表范例中利用。有事情区1)转储master中的表并将转储文件(dumpfile)导进slave，或2)在master中举行ALTERtablenameTYPE=MyISAM，然后利用LOADTABLEtablenameFROMMASTER，最初在master中将表变动回InnoDB范例。在MySQL-4.0.6先前的版本中，SLAVESTOP不会注重有几个SQL语句的事件的界限。一个未完成的事件将会回滚，鄙人一个SLAVESTART时将会运转别的半个事件的残剩部分。这会使复制失利。
在MySQL-4.0.6先前的版本中，在运转多语句事件过程当中slave的溃散将引发与SLAVESTOP一样的成绩。
在MySQL-4.0.11先前的版本中，语句SETFOREIGN_KEY_CHECKS=0的复制完整不克不及事情。
最初，一个较短的注释有关MySQL在master中处置事件复制失利。MySQL的复制是基于二进制日记(binlog)的，它用于MySQL纪录修正了数据的SQL语句。slave读取master的二进制日记(binlog)，并运转一样的SQL语句。假如一个语句失利，举例来讲，因为违反一个外键束缚，那末这个语句将不记进二进制日记中，因此也不复制到slave中。假如一个事件回滚了，那末事件中的SQL语句将不记进二进制日记中，一样这个事件在slave中基本也不会运转。

在执行崩溃恢复时，理解在一个数据库中的每一个表tbl_name对应的在数据库目录中的3个文件是很重要的：

分区表效率问题肯定是大家关心的问题。在我的试验中，如果按照分区字段进行的查询（过滤）效率会高于未分区表的相同语句。但是如果按照非分区字段进行查询，效率会低于未分区表的相同语句。

然后最好有实践机会，能够把实践到的和实践结合起来，其实理论思考是个非常困扰和痛苦的事情

同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。

学习SQL语言的话如果要学会去做网站就不是很难!但是要做数据库管理的话就有难度了!

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

是否碎片会引发效率问题？这都是需要进一步探讨的东西。varbinary(max)代替image也让SQLServer的字段类型更加简洁统一。

如果处理少量数据,比如几百条记录的数据,我不知道这两种情况哪个效率更高,如果处理大量数据呢?比如有表中有20万条记录.

Mirror可以算是SQLServer的Dataguard了。但是能不能被大伙用起来就不知道了。

换言之，只有在不断的失败中尝试成功，而关于失败的总结却是很少的