關於網路那些事...

網路行銷,SEO,網路趨勢,教學文章,網頁設計,生活時事

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)

如果你喜歡我們的文章內容,請在這裡按個讚



最新文章推薦