【Mysql 学习】Mysql 存储引擎

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.01 sec)
--查看默认的存储引擎
mysql> show variables like 'table_type';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| table_type    | InnoDB |
+---------------+--------+
1 row in set (0.00 sec)
--查看当前数据库支持的存储引擎
mysql> show engines \G;
*************************** 1. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: Default engine as of MySQL 3.23 with great performance
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 2. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 6. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
8 rows in set (0.00 sec)
--方法二
mysql> show variables like 'have%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| have_community_features | YES      |
| have_compress           | YES      |
| have_crypt              | NO       |
| have_csv                | YES      |
| have_dynamic_loading    | YES      |
| have_geometry           | YES      |
| have_innodb             | YES      |
| have_ndbcluster         | NO       |
| have_openssl            | DISABLED |
| have_partitioning       | YES      |
| have_query_cache        | YES      |
| have_rtree_keys         | YES      |
| have_ssl                | DISABLED |
| have_symlink            | YES      |
+-------------------------+----------+
14 rows in set (0.00 sec)

mysql> use test;
Database changed

mysql> create table test (i int not null auto_increment,
    -> primary key(i) ) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
--可以使用alter table 语句修改一个表的存储引擎。
mysql> alter table test engine=innodb;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `i` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)