數據庫存儲引擎

Mysql中提到的引擎，簡而言之就是指表的類型。數據庫的存儲引擎決定了表在計算機中的存儲方式。

下面我們使用show engines語句查看mysql數據庫支持的存儲引擎類型。

1.查詢方法：show engines；

此語句可以以”;”，“\\g”,”\\G”結束；”;”和”\\g”作用相同，”\\G”可以讓結果顯示更美觀。

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |

| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |

| CSV | YES | CSV storage engine | NO | NO | NO |

| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |

| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

| MyISAM | YES | MyISAM storage engine | NO | NO | NO |

| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |

| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |

| ARCHIVE | YES | Archive storage engine | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

……

或

mysql> show engines \\G

*************************** 1. row ***************************

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

XA: NO

Savepoints: NO

*************************** 2. row ***************************

Engine: MRG_MYISAM

Support: YES

Comment: Collection of identical MyISAM tables

Transactions: NO

XA: NO

Savepoints: NO

*************************** 3. row ***************************

Engine: CSV

Support: YES

Comment: CSV storage engine

Transactions: NO

XA: NO

Savepoints: NO

*************************** 4. row ***************************

Engine: FEDERATED

Support: NO

Comment: Federated MySQL storage engine

Transactions: NULL

XA: NULL

Savepoints: NULL

在查詢結果中：Engine參數指存儲引擎名稱；

Support參數說明mysql是否支持該類引擎；

Yes表示支出；

Comment參數指對該引擎的評論；

Transaction參數表示是否支出事物處理，yes 表示支持；

XA參數表示是否分佈式交易處理的XA規範，yes表示支持；

Savepoints參數表示是否支持保存點，以便事物回滾到保存點，yes表示支持；

結果中可以看出mysql支持的存儲引擎有：MyISAM、MEMORY、InnoDB、

MRG_MYISAM、ARCHIVE；InnoDB為默認引擎。

2.查詢方式：show variables like “have%”;

mysql> show variables like "have%";

+------------------------+----------+

| Variable_name | Value |

+------------------------+----------+

| have_compress | YES |

| have_dynamic_loading | YES |

| have_geometry | YES |

| have_openssl | YES |

| have_profiling | YES |

| have_query_cache | NO |

| have_rtree_keys | YES |

| have_ssl | YES |

| have_statement_timeout | YES |

| have_symlink | DISABLED |

+------------------------+----------+

10 rows in set, 1 warning (0.00 sec)

查詢結果中：Variable_name表示存儲引擎的名稱，value表示mysql的支持情況。Yes支出，no表示不支持。Disable表示支持，但是還沒有開啟。Variable_name列有取值為have_innodb的記錄，對應value的值為yes，表示支持innodb存儲引擎。

3.查詢默認引擎方法:show variables like ‘storage_engine’;

mysql> show variables like "storage_engine";

+------------------------+----------+

| Variable_name | Value |

+------------------------+----------+

|storage_engine | InnoDB |

1 rows in set, (0.03 sec)

此方法可以查看默認引擎是InnoDB;如果想要改變默認引擎，可以去my.ini文件中修改。

即default-storage-engine=INNODB修改為default-storage-engine=MyISAM,然後重啟服務。