MySQL - InnoDB 簡介

MySQL 5.0 之後的版本支援的儲存引擎包括 MyISAM, InnoDB, MEMORY, CSV …等

其中 InnoDB 屬於交易安全表(支持交易，行級鎖定，外鍵和表加密)

MyISAM、MEMORY、CSV則屬於非交易安全表。

將目前支援的儲存引擎列出來，就能看到個引擎的支援狀況:

> SHOW ENGINES \G
...
*************************** 2. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
...
8 rows in set (0.00 sec)

在 MySQL 5.5 之前，預設的儲存引擎是 MyISAM

MySQL 5.5 之後，預設搜尋引擎則設定為 InnoDB

如果要修改這個預設，可以直接修改 default-table-type

特性

InnoDB 儲存引擎具有交易安全的功能，因此具備了提交、回滾(rollback)以及崩壞修復能力，著重於安全性及功能面

因此，對於寫入速度相對於MyISAM 差一點，並且InnoDB在保存資料時，會占用較多的空間來管理資料及索引。

接下來介紹InnoDB 的一些特性:

AUTO INCREATMENT 欄位必須是索引

InnoDB 的 AUTO_INCREMENT 欄位必須符合以下條件

必須是索引 (INDEX or PRIMARY KEY)
若是組合索引，則必須是組合索引的第一列

可以設定外鍵

InnoDB 是 MySQL 唯一支援外鍵的儲存引擎

範例:

這裡透過國家及城市來說明外鍵的用法

我們先建立好 country 及 city 的資料表

country

MariaDB [example]> CREATE TABLE country( 
	country_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
	country VARCHAR(50) NOT NULL, 
	last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	PRIMARY KEY(country_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.05 sec)

MariaDB [example]> desc country;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| country_id  | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| country     | varchar(50)          | NO   |     | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
3 rows in set (0.04 sec)

city 在 city 資料表中，設定外鍵連結 FOREIGN KEY(country_id)

並且，設定了一些串聯式條件:

ON DELETE RESTRICT : 父表 counrty 具有存在 city 外鍵時，無法單獨刪除 country 內容

ON UPDATE CASCADE : 父表 counrty 執行更新時，city 要同時更新

如果沒有指定ON DELETE 或ON UPDATE，NO ACTION 將會是預設值

MariaDB [example]> CREATE TABLE city( 
	city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, 
	city VARCHAR(50) NOT NULL, 
	country_id SMALLINT UNSIGNED NOT NULL, 
	last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	PRIMARY KEY (city_id), 
	KEY idx_fk_country_id (country_id),  
	FOREIGN KEY(country_id) 
	REFERENCES country (country_id) 
	ON DELETE RESTRICT 
  ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.06 sec)
MariaDB [example]> desc city;
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                 | Null | Key | Default           | Extra                       |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
| city_id     | smallint(5) unsigned | NO   | PRI | NULL              | auto_increment              |
| city        | varchar(50)          | NO   |     | NULL              |                             |
| country_id  | smallint(5) unsigned | NO   | MUL | NULL              |                             |
| last_update | timestamp            | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+----------------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.02 sec)

插入幾筆資料

MariaDB [example]> INSERT INTO country(`country`) VALUES('Taiwan'), ('China');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [example]> SELECT * FROM country;
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|          1 | Taiwan  | 2018-02-28 15:31:12 |
|          2 | China   | 2018-02-28 15:31:12 |
+------------+---------+---------------------+
2 rows in set (0.00 sec)

MariaDB [example]> INSERT INTO city(`city`,`country_id`) VALUES('Taipei',1), ('Beijing',2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [example]> SELECT * FROM city;
+---------+---------+------------+---------------------+
| city_id | city    | country_id | last_update         |
+---------+---------+------------+---------------------+
|       1 | Taipei  |          1 | 2018-02-28 15:32:47 |
|       2 | Beijing |          2 | 2018-02-28 15:32:47 |
+---------+---------+------------+---------------------+
2 rows in set (0.00 sec)

測試 ON DELETE RESTRICT 接下來測試看看，單獨刪除 country 因為有設定 ON DELETE RESTRICT，因此在具有子對應紀錄的情況，無法單獨刪除 country 資料

MariaDB [example]> DELETE FROM country WHERE country_id = 1;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`example`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)

測試 ON UPDATE CASCADE 更新 country 的 id，再檢查 country 與 city 欄位值因為有設定 ON UPDATE CASCADE，因此更新 country 資料也會同時更新子表單對應資料

MariaDB [example]> SELECT * FROM country;
+------------+---------+---------------------+
| country_id | country | last_update         |
+------------+---------+---------------------+
|          2 | China   | 2018-02-28 15:31:12 |
|          3 | Taiwan  | 2018-02-28 15:53:35 |
+------------+---------+---------------------+
2 rows in set (0.00 sec)

MariaDB [example]> SELECT * FROM city;
+---------+---------+------------+---------------------+
| city_id | city    | country_id | last_update         |
+---------+---------+------------+---------------------+
|       1 | Taipei  |          1 | 2018-02-28 15:32:47 |
|       2 | Beijing |          3 | 2018-02-28 15:32:47 |
+---------+---------+------------+---------------------+
2 rows in set (0.00 sec)

暫時關閉外鍵約束

有時需要暫時忽略外鍵的束縛，直接操作時，可以透過下方的指令將外鍵檢查關閉

SET FOREIGN_KEY_CHECKS=0;

若要重新啟用外鍵檢查，則再將值設為1

SET FOREIGN_KEY_CHECKS=1;

範例: 直接刪除具有子對應紀錄的父層資料，會顯示錯誤接著把 SET FOREIGN_KEY_CHECKS=0; 設定完成後就能成功刪除父層資料再重新開啟 SET FOREIGN_KEY_CHECKS=1; 外鍵檢查功能就恢復無法直接刪除具有子對應紀錄的父層資料

MariaDB [example]> DELETE FROM country WHERE country_id = 3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`example`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)
MariaDB [example]> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [example]> DELETE FROM country WHERE country_id = 3;
Query OK, 1 row affected (0.00 sec)

MariaDB [example]> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [example]> DELETE FROM country WHERE country_id = 2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`example`.`city`, CONSTRAINT `city_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON UPDATE CASCADE)