15.2.6.1. 如何在InnoDB用不同API来使用事务
15.2.6.3. AUTO_INCREMENT列如何在InnoDB中工作
假如你用mysql test命令启动MySQL客户端。要创建一个InnoDB表，你必须在表创建SQL语句中指定ENGINE = InnoDB或者TYPE = InnoDB选项：
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) ENGINE=InnoDB;
CREATE TABLE customers (a INT, b CHAR (20), INDEX (a)) TYPE=InnoDB;
SQL语句在表空间的列上创建一个表和索引，表空间包含你在my.cnf指定的数据文件。此外，MySQL在MySQL数据库目录下的test目录里创建一个名为customers.frm的文件。内部地，InnoDB为'test/customers'表往自己的数据目录添加一个条目。这意味这你可以在其它数据库创建一个具有相同名字customers的表，表的名字不会与InnoDB内的冲突。
你可以对任何InnoDB表，通过使用SHOW TABLE
STATUS语句，查询在InnoDB表空间内空闲空间的数量。表空间内空闲空间的数量出现在SHOW TABLE STATUS的输出结果内的Comment节里。例如：
SHOW TABLE STATUS FROM test LIKE 'customers'
注意，统计的SHOW只给出关于InnoDB表的大概情况。它们被用于SQL优化。可是，表和索引保留的大小，以字节为单位是准确的。
默认地，每个连接到MySQL服务器的客户端开始之时是允许自动提交模式的，这个模式自动提交你运行的每个SQL语句。要使用多语句事务，你可以用SQL语句SET
AUTOCOMMIT = 0禁止自动提交，并且用COMMIT和ROLLBACK来提交或回滚你的事务。
如果你想要autocommit保持打开状态，可以在START TRANSACTION与COMMIT或ROLLBACK之间封装你的事务。下列的例子演示两个事务。第一个是被提交的，第二个是被
回滚的：
shell> mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 3.23.50 -log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE CUSTOMER (A INT, B CHAR (20), INDEX (A))
-> ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (10, 'Heikki');
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO CUSTOMER VALUES (15, 'John');
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM CUSTOMER;
+------+--------+
| A | B |
+------+--------+
| 10 | Heikki |
+------+--------+
1 row in set (0.00 sec)
mysql>
在类似PHP, Perl DBI/DBD,
JDBC, ODBC, 或者MySQL的标准C调用接口这样的API上，你能够以字符串形式发送事务控制语句，如COMMIT，到MySQL服务器，就像其它任何的SQL语句
那样，诸如SELECT或INSERT。一些API也提供单独的专门的事务提交和回滚函数或者方法。
要点：你不应该在mysql数据库（比如，user或者host）里把MySQL系统表转换为InnoDB类型。系统表总是MyISAM型。
如果你想要所有（非系统）表都被创建成InnoDB表，你可以简单地把default-table-type=innodb行添加到my.cnf或my.ini文件的[mysqld]节里。
InnoDB对MyISAM存储引擎采用的单独索引创建方法没有做专门的优化。因此，它不值得导出或导入表以及随后创建索引。改变一个表为InnoDB型最快的办法就是直接插入进一个InnoDB表。即，使用ALTER TABLE ... ENGINE=INNODB，或用相同的定义创建一个空InnoDB表，并且用INSERT INTO ... SELECT * FROM ...插入行。
如果你对第二个键有UNIQUE约束，你可以在导入阶段设置：SET
UNIQUE_CHECKS=0，以临时关掉唯一性检查好加速表的导入。对于大表，这节省了大量的磁盘I/O，因为InnoDB随后可以使用它的插入缓冲区来第二个索引记录作为一批来写入。
为获得对插入进程的更好控制，分段插入大表可能比较好：
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey <= somethingelse;
所有记录已经本插入之后，你可以重命名表。
在大表的转换中，你应该增加InnoDB缓冲池的大小来减少磁盘I/O。尽管如此，不要使用超过80%的内部内存。你也可以增加InnoDB日志文件和日志文件的大小。
确信你没有填满表空间：InnoDB表比MyISAM表需要大得多的磁盘空间。如果一个ALTER TABLE耗尽了空间，它就开始一个
回滚，并且如果它是磁盘绑定的，回滚可能要几个小时。对于插入，InnoDB使用插入缓冲区来以成批地合并第二个索引记录到索引中。那样节省了大量磁盘I/O。在回滚中，没有使用这样的机制，而回滚要花比插入长30倍的时间来完成。
在失控的回滚情况下，如果你在数据库中没有有价值的数据，比较明智的是杀掉数据库进程而不是等几百万个磁盘I/O被完成。
完整的过程，请参阅15.2.8.1节，“强制恢复”。
如果你为一个表指定AUTO_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该
列赋新值。自动增长计数器仅被存储在主内存中，而不是存在磁盘上。
InnoDB使用下列算法来为包含一个名为ai_col的AUTO_INCREMENT列的表T初始化自动增长计数器：服务器启动之后，当一个用户对表T做插入之时，InnoDB执行等价如下语句的动作：
SELECT MAX(ai_col) FROM T FOR UPDATE;
语句取回的值逐次加一，并被赋给列和自动增长计数器。如果表是空的，值1被赋予该列。如果自动增长计数器没有被初始化，而且用户调用为表T显示输出的SHOW TABLE STATUS语句，则计数器被初始化（但不是增加计数）并被存储以供随后的插入使用。注意，在这个初始化中，我们对表做一个正常的独占读锁定，这个锁持续到事务的结束。
InnoDB对为新创建表的初始化自动增长计数器允许同样的过程。
注意，如果用户在INSERT中为AUTO_INCREMENT列指定NULL或者0，InnoDB处理行，就仿佛值还没有被指定，且为它生成一个新值。
自动增长计数器被初始化之后，如果用户插入一个明确指定该列值的行，而且该值大于当前计数器值，则计数器被设置为指定
列值。如果没有明确指定一个值，InnoDB给计数器增加一，并且赋新值给该列。
当访问自动增长计数器之时，InnoDB使用专用的表级的AUTO-INC锁定，该锁持续到当前SQL语句的结束而不是到业务的结束。
引入了专用锁释放策略，来为对一个含AUTO_INCREMENT列的表的插入改善部署。两个事务不能同时对同一表有AUTO-INC锁定。
注意，如果你回滚从计数器获得数的事务，你可能会在赋给AUTO_INCREMENT列的值的序列中发现间隙。
如果用户给列赋一个赋值，或者，如果值大过可被以指定整数格式存储的最大整数，自动增长机制的行为不被定义。
在CREATE TABLE和ALTER TABLE语句中，InnoDB支持AUTO_INCREMENT = n
表选项来设置计数器初始值或变更当前计数器值。因在本节早先讨论的原因，这个选项的影响在服务器重启后就无效了。
InnoDB也支持外键约束。InnoDB中对外键约束定义的语法看起来如下：
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
外键定义服从下列情况：
·
所有tables必须是InnoDB型，它们不能是临时表。
·
在引用表中，必须有一个索引，外键列以同样的顺序被列在其中作为第一列。这样一个索引如果不存在，它必须在
引用表里被自动创建。
·
在引用表中，必须有一个索引，被引用的列以同样的顺序被列在其中作为第一列。
·
不支持对外键列的索引前缀。这样的后果之一是BLOB和TEXT列不被包括在一个外键中，这是因为对这些列的索引必须总是包含一个前缀长度。
·
如果CONSTRAINTsymbol被给出，它在数据库里必须是唯一的。如果它没有被给出，InnoDB自动创建这个名字。
InnoDB拒绝任何试着在子表创建一个外键值而不匹配在父表中的候选键值的INSERT或UPDATE操作。一个父表有一些匹配的行 的子表，InnoDB对任何试图更新或删除该父表中候选键值的UPDATE或DELETE操作有所动作，这个动作取决于用FOREIGN KEY子句的ON UPDATE和ON DETETE子句指定的referential action。当用户试图从一个父表删除或更新一行之时，且在子表中有一个或多个匹配的行，InnoDB根据要采取的动作有五个选择：
·
CASCADE:
从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON
UPDATE CASCADE都可用。在两个表之间，你不应定义若干在父表或子表中的同一列采取动作的ON UPDATE CASCADE子句。
· SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。如果外键列没有指定NOT NULL限定词，这就是唯一合法的。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。
·
NO ACTION: 在ANSI
SQL-92标准中，NO ACTION意味这不采取动作，就是如果有一个相关的外键值在被参考的表里，删除或更新主要键值的企图不被允许进行（Gruber,
掌握SQL,
2000:181）。 InnoDB拒绝对父表的删除或更新操作。
·
RESTRICT: 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样，删除ON
DELETE或ON UPDATE子句。（一些数据库系统有延期检查，并且NO ACTION是一个延期检查。在MySQL中，外键约束是被立即检查的，所以NO
ACTION和RESTRICT是同样的）。
·
SET DEFAULT:
这个动作被解析程序识别，但InnoDB拒绝包含ON DELETE SET DEFAULT或ON UPDATE
SET DEFAULT子句的表定义。
当父表中的候选键被更新的时候，InnoDB支持同样选择。选择CASCADE，在子表中的外键
列被设置为父表中候选键的新值。以同样的方式，如果在子表更新的列参考在另一个表中的外键，更新级联。
注意，InnoDB支持外键在一个表内引用，在这些情况下，子表实际上意味这在表内附属的记录。
InnoDB需要对外键和被引用键的索引以便外键检查可以快速进行且不需要一个表扫描。对外键的索引被自动创建。这是相对于一些老版本，在老版本中索引必须明确创建，否则外键约束的创建会失败。
在InnoDB内，外键里和被引用列里相应的列必须有类似的内部数据类型，以便它们不需类型转换就可被比较。整数类型的大小和符号必须相同。字符串类型的长度不需要相同。如果你指定一个SET NULL动作，请确认你没有在子表中宣告该
列为为NOT NULL。
如果MySQL从CREATE TABLE语句报告一个错误号1005，并且错误信息字符串指向errno
150，这意思是因为一个外键约束被不正确形成，表创建失败。类似地，如果ALTER
TABLE失败，且它指向errno 150，
那意味着对已变更的表，外键定义会被不正确的形成。你可以使用SHOW
INNODB STATUS来显示一个对服务器上最近的InnoDB外键错误的详细解释。
注释：InnoDB不对那些
外键或包含NULL列的被引用键值检查外键约束。
对SQL标准的背离：如果在父表内有数个行，其中有相同的
被引用键值，然后InnoDB在外键检查中采取动作，就仿佛其它有相同键值的父行不存在一样。例如，如果你已定义一个RESTRICT类型的约束，并且有一个带数个父行的子行，InnoDB不允许任何对这些父行的删除。
居于对应外键约束的索引内的记录，InnoDB通过深度优先选法施行级联操作。
对SQL标准的背离： 如果ON UPDATE CASCADE或ON
UPDATE SET NULL递归更新相同的表，之前在级联过程中该表一被更新过，它就象RESTRICT一样动作。这意味着你不能使用自引用ON UPDATE CASCADE或者ON UPDATE
SET NULL操作。这将阻止级联更新导致的无限循环。另一方面，一个自引用的ON DELETE SET NULL是有可能的，就像一个自引用ON DELETE CASCADE一样。
级联操作不可以被嵌套超过15层深。
对SQL标准的背离： 类似一般的MySQL，在一个插入，删除或更新许多行的SQL语句内，InnoDB逐行检查UNIQUE和FOREIGN KEY约束。按照SQL的标准，
默认的行为应被延迟检查，即约束仅在整个SQL语句被处理之后才被检查。直到InnoDB实现延迟的约束检查之前，一些事情是不可能的，比如删除一个通过外键参考到自身的记录。
注释：当前，触发器不被级联外键的动作激活。
一个通过单列外键联系起父表和子表的简单例子如下：
CREATE TABLE parent(id INT NOT NULL,
PRIMARY KEY (id)
) TYPE=INNODB;
CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) TYPE=INNODB;
如下是一个更复杂的例子，其中一个product_order表对其它两个表有外键。一个外键引用一个product表中的双列索引。另一个
引用在customer表中的单行索引：
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)) TYPE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) TYPE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) TYPE=INNODB;
InnoDB允许你用ALTER TABLE往一个表中添加一个新的
外键约束：
ALTER TABLE yourtablename
ADD [CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
REFERENCES tbl_name (index_col_name, ...)
[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION}]
记住先创建需要的索引。你也可以用ALTER
TABLE往一个表添加一个自引用外键约束。
ALTER TABLE yourtablename DROP FOREIGN KEY fk_symbol;
当年创建一个外键之时，如果FOREIGN KEY子句包括一个CONSTRAINT名字，你可以引用那个名字来移除
外键。另外，当外键被创建之时，fk_symbol值被InnoDB内部保证。当你想要移除一个外键之时，要找出标记，请使用SHOW
CREATE TABLE语句。例子如下：
mysql> SHOW CREATE TABLE ibtest11c \G
*************************** 1. row ***************************
Table: ibtest11c
Create Table: CREATE TABLE `ibtest11c ` (
`A` int(11) NOT NULL auto_increment,
`D` int(11) NOT NULL default '0',
`B` varchar(200) NOT NULL default '',
`C` varchar(175) default NULL,
PRIMARY KEY (`A`,`D`,`B`),
KEY `B` (`B`,`C`),
KEY `C` (`C`),
CONSTRAINT `0_38775` FOREIGN KEY (`A`, `D`)
REFERENCES `ibtest11a ` (`A`, `D`)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `0_38776` FOREIGN KEY (`B`, `C`)
REFERENCES `ibtest11a ` (`B`, `C`)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=INNODB CHARSET=latin1
1 row in set (0.01 sec)
mysql> ALTER TABLE ibtest11c DROP FOREIGN KEY 0_38775;
InnoDB解析程序允许你在FOREIGN
KEY ... REFERENCES ...子句中用`（backticks)把表和列名名字围起来。InnoDB解析程序也考虑到lower_case_table_names系统变量的设置。
InnoDB返回一个表的外键定义作为SHOW CREATE TABLE语句输出的一部分：
SHOW CREATE TABLE tbl_name;
从这个版本起，mysqldump也将表的正确定义生成到转储文件中，且并不忘记
外键。
你可以如下对一个表显示外键约束：
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
外键约束被列在输出的Comment列。
当执行外键检查之时，InnoDB对它照看着的子或父记录设置共享的行级锁。InnoDB立即检查外键约束，检查不对事务提交延迟。
要使得对有外键关系的表重新载入转储文件变得更容易，mysqldump自动在转储输出中包括一个语句设置FOREIGN_KEY_CHECKS为0。这避免在转储被重新装载之时，与不得不被以特别顺序重新装载的表相关的问题。也可以手动设置这个变量：
mysql> SET FOREIGN_KEY_CHECKS = 0;
mysql> SOURCE dump_file_name;
mysql> SET FOREIGN_KEY_CHECKS = 1;
如果转储文件包含对外键是不正确顺序的表，这就以任何顺序导入该表。这样也加快导入操作。设置FOREIGN_KEY_CHECKS为0，对于在LOAD
DATA和ALTER TABLE操作中忽略外键限制也是非常有用的。
InnoDB不允许你删除一个被FOREIGN KEY表约束
引用的表，除非你做设置SET FOREIGN_KEY_CHECKS=0。当你移除一个表的时候，在它的创建语句里定义的约束也被移除。
如果你重新创建一个被移除的表，它必须有一个遵从于也引用它的外键约束的定义。它必须有正确的列名和类型，并且如前所述，它必须对被
引用的键有索引。如果这些不被满足，MySQL返回错误号1005
并在错误信息字符串中指向errno
MySQL复制就像对MyISAM表一样，也对InnoDB表起作用。以某种方式使用复制也是可能的，在这种方式中从服务器上表的类型不同于主服务器上
原始表的类型。例如，你可以复制修改到主服务器上一个InnoDB表，到从服务器上一个MyISAM表里。
要为一个主服务器建立一个新服务器，你不得不复制InnoDB表空间和日志文件，以及InnoDB表的.frm文件，并且移动复件到从服务器。
关于其恰当步骤请参阅15.2.9节，“移动InnoDB数据库到另一台机器”。
如果你可以关闭主服务器或者一个存在的从服务器。你可以采取InnoDB表空间和日志文件的冷备份，并用它来建立一个从服务器。要不关闭任何服务器来建立一个新的从服务器，你也可以使用非免费（商业的）InnoDB热备份工具。
InnoDB复制里一个小的限制是LOAD TABLE FROM MASTER不对InnoDB类型表起作用。有两种可能的工作区：
·
转储主服务器上的表并导入转储文件到从服务器。
·
在用LOAD TABLE tbl_name FROM MASTER建立复制之前，在主服务器上使用ALTER
TABLE tbl_name TYPE=MyISAM，并且随后使用ALTER TABLE把主表转换回InnoDB。
在主服务器失败的事务根本不影响复制。MySQL复制基于二进制日志，MySQ在那里写修改数据的SQL语句。从服务器读主服务器的二进制日志，并执行同样的SQL语句。尽管如此，在事务里发生的语句不被写进二进制日志直到事务提交，在那一刻，事务里所有语句被一次性写进日志。如果语句失败了，比如，因为外键违例，或者，如果事务被回滚，没有SQL语句被写进二进制日志，并且事务根本不在从服务器上执行。
你可以存储每个InnoDB表和它的索引在它自己的文件在中，这个特征被称为“多表空间”
，因为实际上每个表有它自己的表空间。
对那些想把特定表格移到分离物理磁盘的用户，或者那些希望快速恢复单个表的备份而无须打断其余InnoDB表的使用的用户，使用多表空间会是有益的。
你可以往my.cnf的[mysqld]节添加下面行来允许多表空间：
[mysqld]
innodb_file_per_table
重启服务器之后，InnoDB存储每个新创建的表到表格所属于的数据库目录下它自己的文件tbl_name.ibd里。这类似于MyISAM存储引擎所做的，但MyISAM
把表分成数据文件tbl_name.MYD和索引文件tbl_name.MYI。对于InnoDB，数据和所以被一起存到.ibd文件。tbl_name.frm文件照旧依然被创建。
如果你从my.cnf文件删除innodb_file_per_table行，并重启服务器，InnoDB在共享的表空间文件里再次创建表。
innodb_file_per_table只影响表的创建。如果你用这个选项启动服务器，新表被用.ibd文件来创建，但是你仍旧能访问在共享表空间里的表。如果你删掉这个选项，新表在共享表空间内创建，但你仍旧可以访问任何用多表空间创建的表。
InnoDB总是需要共享标空间。.ibd文件对InnoDB不足以去运行，共享表空间包含熟悉的ibdata文件，InnoDB把内部数据词典和未作日志放在这个文件中。
注释：你不能
像对MyISAM一样，在数据目录之间随意地移动.ibd文件。这是因为表定义被存在InnoDB共享表空间内，而且InnoDB必须保持事务ID和日志顺序号的一致性。
在一个给定的MySQL安装里，你可以用RENAME TABLE语句把.ibd文件和关联的表从一个数据库移到另一个数据库：
RENAME TABLE old_db_name.tbl_name TO new_db_name.tbl_name;
如果你有.ibd文件的一个干净的备份，你可以按如下操作从被起源的地方恢复它到MySQL安装中：
1. 发出这个ALTER TABLE语句：
2. ALTER TABLE tbl_name DISCARD TABLESPACE;
警告：这个语句删除当前.ibd文件。
3. 把备份的.ibd文件放回到恰当的数据库目录。
4. 发出这个ALTER TABLE语句:
5. ALTER TABLE tbl_name IMPORT TABLESPACE;
在上下文中，一个.ibd文件干净的备份意为：
·
.ibd文件里没有尚未提交的事务做的修改。
·
.ibd文件里无未合并的插入混充条目。
·
净化已经从.ibd文件移除所有已标注删除的索引记录。
·
mysqld已经把.ibd文件的所有已修改页面从缓冲池
刷新到文件。
你可以用下列方法生成一个.ibd文件的干净备份：
1.
停止所有来自mysqld服务器的活动，并提交所有事务。
2.
等待直至SHOW INNODB STATUS显示在数据库被已经没有激活的事务，并且InnoDB主线程的状态是Waiting for server activity。然后你就可以复制.ibd文件了。
生成一个.ibd文件的干净复制的另一个方法是使用商业的InnoDB热备份工具：
1.
使用InnoDB热备份工具备份InnoDB安装。
2.
在备份上启动第二个mysqld服务器，让它清洁备份里的.ibd文件。