InnoDB 中文参考手册 --- 4 建立 InnoDB 表
2024-07-21 02:08:51
供稿：网友
4 建立 innodb 表
假设你已以 mysql test 命令方式运行了 mysql 客户端程序。为了建立一个 innodb 格式的表你必须在 sql 命令中指定 type = innodb ：
create table customer (a int, b char (20), index (a)) type = innodb;
这个 sql 命令将在 my.cnf 中设定的 innodb 数据文件中创建一个表和一个列 a 的索引，同时将在 mysql 数据目录下的 test 中建立一个名为 customer.frm 的文件。在内部，innodb 将在它自己的数据字典(data dictionary)中添加表“test/customer”的进入点。这样你可以在其它的 mysql 数据库中建立一个同样名为 customer 的数据表，它将不会与 innodb 中的表冲突。
可以通过发出 mysql 的查看表状态命令查看任何一个 innodb 类型表以查看 innodb 数据文件的剩余空间。show 输出信息中的表注释部分将显示数据文件的剩余空间。示例：
show table status from test like 'customer'
注意：show 给出的 innodb 表统计只是近似的：他们被 sql 优化。然而，表和索引的 reserved sizes in bytes 是精确的。
要特别注意不要在 innodb 数据库中手动添加与删除“.frm” 文件：使用 create table 和 drop table 命令。 innodb 有它自己的内部数据字典(data dictionary)，如果 mysql的“.frm” 文件与 innodb 内部的数据字典不“同步”将产生一个错误。
4.1 如何通过不同的 apis 在 innodb 中使用事务
默认的，mysql 总是启动自动提交你运行的每个 sql 语句的自动提交(autocommit)模式创建每的新连接。为了使用事务，可以使用 sql 命令 set autocommit = 0 关闭自动提交(autocommit)开关，使用 commit 和 rollback 来提交事务和回滚事务。如果你想保留 autocommit 开关打开，你可以将事务放入 begin 和 commit 或 rollback之间。
[email protected]:~/mysql/client> 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)) type = 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>
通过 apis (比如 php, perl dbi/dbd, jdbc, odbc, 或 mysql 的标准 c 调用接口)，发送一个事务控制语句(比如 "commit" )到 mysql 服务器可以如同其它的 sql 语句。例如："select..." 或 "insert..."。 apis often contain separate special commit-transaction methods, 但是 mysql 对事务的支持还相对比较年轻，他们并不是在所有版本的 apis 上均能工作的。
4.2 将 myisam 表转换到 innodb 类型重要提醒：不可以将 mysql 系统表比如 'user' 或 'host' 转换成 innodb 类型。系统表必须为 myisam 类型。
如果你希望所有你新建的表是 innodb 类型，从 mysql version 3.23.43 开始，你可以在你的 my.cnf 或my.ini 文件的 [mysqld] 项下加入下列一行
default-table-type=innodb
innodb 没有一个特殊的分册索引建立优化机制。因此不支持先 export/import 表然后创建索引。最快的办法就是直接将表类型改变为 innodb 类型或直接插入数据，这就是说，使用 alter table ... type=innodb 或新建一个空的具有相同结构的 innodb 表，然后使用 insert into ... select * from .... 插入数据。
如果有 unique 约束，从 3.23.52 开始为了提高插入速度你可以关闭唯一性检查：
set unique_checks=0;
对于较大的表，在向 innodb 中插入时会使用索引缓冲区来合并辅助索引记录来分批插入，这将减少许多磁盘的 i/o。
为了更好地控制插入过程，最好将大的表分批插入：
insert into newtable select * from oldtable where yourkey > something and yourkey <= somethingelse;
等全部数据插入完毕后，可以重新命名表名。
在插入过程中，要将 innodb 的缓冲池(buffer pool)设大一点以减少磁盘 i/o，然而不要超过物理内存的 80 %。同时还应该将日志文件和日志缓冲加大。
需要注意的是不要让表空间用尽： innodb 表比 myisam 表使用更多的内存。如果 alter table 用光了表空间，将会回滚，如果磁盘不够的话，这个过程将持续几个小时。在向 innodb 中插入时会使用索引缓冲区来合并辅助索引记录来分批插入，这将减少很多磁盘 i/o。而在回滚中并不使用这种机制，这将比前者多使用 30 倍的时间。
为了退出失控的回滚，如果在你的导入表中没有什么重要的数据，从 3.23.53 和 4.0.3 开始可以使用技巧退出回滚，请查看 6.1 退出失控的回滚(stop the runaway rollback)。
4.3 外键约束
从 3.23.43b 开始 innodb 支持外键约束特性。innodb 表类型第一次为 mysql 提供了外键约束以保证你的数据完整性。
innodb 中外键约束的定义语法如下所示：
[constraint symbol] foreign key [id] (index_col_name, ...) references table_name (index_col_name, ...) [on delete {cascade | set null | no action | restrict}] [on update {cascade | set null | no action | restrict}]
两个表必须为 innodb 类型，外键和被引用键(referenced key)必须是索引中的第一(first)列。innodb 不会自动为外键和被引用键建立索引，必须明确创建它们。
外键与对应的被引用键在 innodb 内必须 有相似的内部数据类型，以便他们不需要一个类型转换就可以进行比较。 整型(integer)字段的长度与有符号类型(signedness)必须一致。 字符型则不需要一致。如果指定了一个 set null 动作，那你必须要确定 子表中的对应字段没有定义为 not null。
如果 create table 给出 1005 号错误，错误信息字符串提示错误号(errno) 150，那么就是因为外键约束未被正确建立而导致表创建失败。同样的，如果一条 alter table 失败而返回错误号 150，那就意味着 altered table 未能正确定义一个外键。从 4.0.13 开始，你可以通过使用 show innodb status 来查看服务器是最后一条 innodb 的外键错误的详细说明。
从 3.23.50 开始，innodb 不再在允许 null 值外键或被引用键上检查外键约束。
与 sql 标准不一致： if in the parent table there are several rows which have the same referenced key value, then innodb acts in foreign key checks like the other parent rows with the same key value would not exist. for example, if you have defined a restrict type constraint, and there is a child row with several parent rows, innodb does not allow the deletion of any of those parent rows.
从 3.23.50 开始，可能联合 on delete cascade 或 on delete set null 子句与外键约束一同作用。相应的 on update 选项将从 4.0.8 开始支持。如果 on delete cascade 被指定，当主表中的记录行被删除时，innodb 将自动删除子表中被引用键值与主表中相对应的外键值相同的记录。如果 on delete set null 被指定，子表中的外键对应行将被设置为 null 值。
与 sql 标准不一致： if on update cascade or on update set null recurses to update the same table it has already updated during the cascade, it acts like restrict. this is to prevent infinite loops resulting from cascaded updates. a self-referential on delete set null, on the other hand, works starting from 4.0.13. a self-referential on delete cascade has always worked.
示例：
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;
从 3.23.50 开始，innodb 允许通过下面的方法给一个表添加一个外键约束：
alter table yourtablename add [constraint symbol] foreign key [id] (...) references table_name (index_col_name, ...) [on delete {cascade | set null | no action | restrict}] [on update {cascade | set null | no action | restrict}]
记住首先要建立必要的索引，尽管可以通过 alter table 为一个表建立一个自参考(self-referential)的外键。
从 4.0.13 开始，innodb 支持
alter table drop foreign key internally_generated_foreign_key_id
当你需要删除一个外键时可以使用 show create table 来查看 internally generated foreign key id。
如果要导入表的几个转储(dump)，而数据并没有按外键排序，从 3.23.52 和 4.0.3 开始，可以在导入时关闭外键检查：
set foreign_key_checks=0;
这就允许以任何顺序导入数据，同时提高导入速度。
从 3.23.50 开始，innodb 语法分析器(parser)允许你 backquotes around table 以及将列名放入 foreign key ... references ... 子句中。从 4.0.5 开始，innodb 语法分析器能处理 my.cnf 文件可能设置的 lower_case_table_names。
在小于 3.23.50 的版本中，innodb 任何 alter table 或 create index 均不能在使用在有外键约束或被引用键约束的表上：任何 alter table 都将删除表中定义的外键约束。不能再使用 alter table 来任何一个表，只有通过 drop table 和 create table 来修改。当 mysql 执行一个 alter table 时，在内部处理上是通过 rename table 来实现的，这将引起外键约束对表的引用混乱。同样 create index 语句也是作为 alter table来处理的，也不能用于外键约束的表。
当 innodb 进行外键检查时会对主表与子表数据加行锁。nnodb 会立即检查外键约束：检查不会等到事务提交。
innodb 允许你 drop 任何表，即使这样会打破外键，这样操作的结果就是约束也被 drop 了。
innodb 允许你撤消(drop)任何表，即使这样会打破被引用表的外键约束。 当你撤消一个表时约束也同时被撤销了。
如果重新创建一个被撤消的表，必须参考原有定义建立一致的外键约束。 必须有正确的列我与类型。必须在引用键上有索引。如果不符合上面的条件，mysql 将返回1005 号错误，错误信息字符串提示错误号(errno) 150。
从 3.23.50 开始，通过下列指令可以使 innodb 返回表的外键约束定义
show create table yourtablename
还可以通过 mysqldump 将表的完整定义转储到文件中，当然包括外键定义。
还可以通过下面的指令列出表 t 的外键约束：
show table status from yourdatabasename like 't'
外键约束将会在表注释中列出。
4.4 自增列(auto-increment)是如何在 innodb 中工作的
如果表有一个自增(auto-increment)列，那么 innodb 表处理系统将它的数据字典中包含一个特别的计数器用以记录自增列的下一个列值。 自增计数器只放于主存中，而不是放在磁盘中。
innodb 使用下列规则初始化自增计数器。数据库启动后，当用户第一次向表 t 插入数据或运行 show table status 来显示表 t 时，innodb 将执行
select max(auto-inc-column) from t for update,
同时将所得值加 1 现填入字段并记录表的自增记录器。如果表是空的则将值赋为 1 。注意在这个初始化过程中将为表加一个读锁(a normal x-locking read)，这个锁将一直持续到事务处理结束。
innodb 为一个新建立的表以同样的方式建立自增计数器。
如果为一个自增列特别指定值 0 ，那么 innodb 将视为未为该列指定值而将该列赋于新值。
在自增计数器初始化后，在自增列中插入一个明确指定的新值 ，并且该值大于当前计数值，那么计数器将被设置为新的指定值。如果用户没有明确为它指定一个值，innodb 将自增计 数器，并将新值赋于自增列。
当访问自增计数器时，innodb 将使用一个特殊的表级锁定( auto-inc lock )，锁将一直保持到当前 sql 语句的运行结束，而不是线程的结束。特殊的锁释放策略被引入是为了改善向有自增字段的表中插入数据的并发性能。两个事务处理不能在同一张表上建立auto-inc 锁。
注意如果事务从自增计数器中取值，那么当事务回滚时在自增列顺序上可能会产生空隙。
如果给一个自增字段指定一个无效值或值比定义的整型类型最大值域还大，那么自增机制 的状态将无法预知。
4.5 innodb 和 mysql 复制(replication)
mysql 的复制特征(replication feature)在 innodb 表上的工作就与它在 myisam 表类型上一样。在 master 中的表类型与 slave 中的表类型是不一致时使用复制(replication)是可能的。举例来说，你可以将 master 中的一个 innodb 表的更改复制到一个 slave 中的 myisam 表中去。
为了设立一个 master 的新的 slave ，你必须建立一份 innodb 表空间和日志文件(log files)的复本，同样也包括 innodb 表相对应的 .frm 文件，并将这些复本移动到 slave 中。这看上去有点像下面 章节 7 关于移动一个 innodb 数据库的说明。如果你可以关闭 master，你可以建立一个 innodb 表空间和日志文件的冷备份(cold backup)来建立一个 slave。为了建立一个新的 slave 而不将 master 数据库关闭，你可以使用一个非免费的 innodb hot backup tool。
在 innodb 复制中的一引起小限制：
load table from master 不能够在 innodb 表类型中使用。有工作区 1) 转储 master 中的表并将转储文件(dumpfile)导入 slave，或 2) 在 master 中进行alter tablename type=myisam ，然后使用 load table tablename from master，最后在 master 中将表更改回 innodb 类型。 在 mysql-4.0.6 先前的版本中，slave stop 不会注意有几个 sql 语句的事务的边界。一个未完成的事务将会回滚，在下一个slave start 时将会运行另外半个事务的剩余部份。这会使复制失败。
在 mysql-4.0.6 先前的版本中，在运行多语句事务过程中 slave 的崩溃将引起与 slave stop 同样的问题。
在 mysql-4.0.11 先前的版本中，语句 set foreign_key_checks=0 的复制完全不能工作。
最后，一个较短的解释有关 mysql 在 master 中处理事务复制失败。mysql 的复制是基于二进制日志(binlog)的，它用于 mysql 记录修改了数据的 sql 语句。slave 读取 master 的二进制日志(binlog)，并运行同样的 sql 语句。如果一个语句失败，举例来说，由于违背一个外键约束 ，那么这个语句将不记入二进制日志中，因而也不复制到 slave 中。如果一个事务回滚了，那么事务中的 sql 语句将不记入二进制日志中，同样这个事务在 slave 中根本也不会运行。
