MySQL資料庫中查看和修改表的存儲引擎、主要存儲引擎的對比

查看系統支持的存儲引擎

SHOW ENGINES;

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

查看數據表所使用的存儲引擎

SHOW CREATE TABLE 表名 SHOW TABLE STATUS FROM 資料庫名 WHERE name = '表名';

mysql> SHOW CREATE TABLE user_info;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                                                                                                                                     |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_info | CREATE TABLE `user_info` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(40) NOT NULL,
  `password` varchar(100) NOT NULL,
  `age` tinyint(3) unsigned DEFAULT NULL,
  `sex` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS FROM user WHERE name =  'user_info';
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
| user_info | InnoDB |      10 | Dynamic    |    3 |           5461 |       16384 |               0 |            0 |         0 |              9 | 2017-10-30 12:18:28 | 2017-10-30 22:10:26 | NULL       | utf8_general_ci |     NULL |                |         |
+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

主要存儲引擎的對比

我看網上有很多博客文章對各存儲引擎都有瞭一個比較詳細的闡述,但是多數是文字,各個儲存引擎之間的對比不夠明顯。所以我上IMOOC網找瞭一個截圖方便對比。

image


存儲引擎的修改

MySQL初始默認引擎

MySQL初始默認引擎為InnoDB。

關於InnoDB的介紹從這篇文章中摘選瞭一些介紹【原文鏈接】:

InnoDB是一個健壯的事務型存儲引擎,這種存儲引擎已經被很多互聯網公司使用,為用戶操作非常大的數據存儲提供瞭一個強大的解決方案。我的電腦上安裝的MySQL5.6.13版,InnoDB就是作為默認的存儲引擎。InnoDB還引入瞭行級鎖定和外鍵約束,在以下場合下,使用InnoDB是最理想的選擇:

更新密集的表:InnoDB存儲引擎特別適合處理多重並發的更新請求。 事務:InnoDB存儲引擎是支持事務的標準MySQL存儲引擎。 自動災難恢復:與其它存儲引擎不同,InnoDB表能夠自動從災難中恢復。 外鍵約束:MySQL支持外鍵的存儲引擎隻有InnoDB。 支持自動增加列AUTO_INCREMENT屬性: 一般來說,如果需要事務支持,並且有較高的並發讀取頻率,InnoDB是不錯的選擇。

① 通過修改MySQL配置文件

default-storage-engine = engine_name

② 通過創建數據表命令

CREATE TABLE new_tb( … )ENGINE = engine_name;

mysql> CREATE TABLE new_tb(
    -> id INT PRIMARY KEY
    -> )ENGINE = MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE new_tb;
+--------+------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                               |
+--------+------------------------------------------------------------------------------------------------------------+
| new_tb | CREATE TABLE `new_tb` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

③ 通過修改數據表命令

ALTER TABLE table_name ENGINE [=] engine_name

mysql> SHOW CREATE TABLE new_tb;
+--------+------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                               |
+--------+------------------------------------------------------------------------------------------------------------+
| new_tb | CREATE TABLE `new_tb` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE new_tb ENGINE = InnoDB;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE new_tb;
+--------+------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                               |
+--------+------------------------------------------------------------------------------------------------------------+
| new_tb | CREATE TABLE `new_tb` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *