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 中根本也不會運行。
