1: 查看 MySQL 的存储引擎信息
1.1 使用 show engines 命令.
- mysql> show engines;
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | Engine | Support | Comment | Transactions | XA | Savepoints |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
- | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
- | MyISAM | YES | MyISAM storage engine | NO | NO | NO |
- | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
- | CSV | YES | CSV storage engine | NO | NO | NO |
- | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
- | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
- | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
- | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- 9 rows in set (0.01 sec)
Support 列, YES 表示当前版本支持这个存储引擎, DEFAULT 表示该引擎是默认的引擎. NO 表示不支持该存储引擎. 如下所示, InnoDB 是默认的存储引擎.
1.2 可以查看系统变量 default_storage_engine 或 storage_engine
1:default_storage_engine 表示永久表 (permanent tables) 的默认存储引擎.
2:default_tmp_storage_engine 表示临时表的默认存储引擎.
storage_engine 这个系统变量不推荐使用, 它已经被系统变量 default_storage_engine 替代了
- mysql> show variables like '%storage_engine%';
- +----------------------------+--------+
- | Variable_name | Value |
- +----------------------------+--------+
- | default_storage_engine | InnoDB |
- | default_tmp_storage_engine | InnoDB |
- | storage_engine | InnoDB |
- +----------------------------+--------+
- 3 rows in set (0.00 sec)
2: 如何修改 MySQL 的默认存储引擎?
2.1 修改 my.cnf, 在配置文件里面增加参数 default-storage-engine, 然后重启数据库服务.
- [mysqld]
- default-storage-engine=MyISAM
然后检查默认存储引擎, 就会看到 MyISAM 为默认存储引擎了.
2.2 使用命令修改系统变量(system variables)
- mysql> set default_storage_engine=InnoDB;
- Query OK, 0 rows affected (0.09 sec)
- mysql> show engines;
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | Engine | Support | Comment | Transactions | XA | Savepoints |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
- | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
- | MyISAM | YES | MyISAM storage engine | NO | NO | NO |
- | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
- | CSV | YES | CSV storage engine | NO | NO | NO |
- | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
- | ARCHIVE | YES | Archive storage engine | NO | NO | NO |
- | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
- | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
- +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
- 9 rows in set (0.00 sec)
- mysql>
3: 如何查看表使用的存储引擎?
查看表所用的存储引擎, 有下面几种方法:
3.1 查询 information_schema.TABLES
- mysql> SELECT TABLE_SCHEMA,
- -> TABLE_NAME,
- -> TABLE_TYPE,
- -> ENGINE
-> FROM information_schema.TABLES
- -> WHERE TABLE_NAME = 'TEST';
- +--------------+------------+------------+--------+
- | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE |
- +--------------+------------+------------+--------+
- | gsp | TEST | BASE TABLE | InnoDB |
- +--------------+------------+------------+--------+
- 1 row in set (0.00 sec)
- mysql>
3.2 使用 SHOW CREATE TABLE TEST \G 命令.
- mysql> SHOW CREATE TABLE TEST \G;
- *************************** 1. row ***************************
- Table: TEST
- Create Table: CREATE TABLE `TEST` (
- `ID` int(11) DEFAULT NULL,
- `COL1` varchar(6) DEFAULT NULL,
- `COL2` varchar(6) DEFAULT NULL,
- `COL3` varchar(6) DEFAULT NULL,
- `COL4` varchar(6) DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
- ERROR:
- No query specified
- mysql>
- 3.3 SHOW TABLE STATUS
- mysql> SHOW TABLE STATUS WHERE Name='TEST';
- ERROR 1046 (3D000): No database selected
- mysql>
- mysql>
- mysql> use gsp;
- Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
- Database changed
- mysql> SHOW TABLE STATUS WHERE Name='TEST';
如上所示, 这种写法必须制定数据库, 否则, 你只能使用下面语法
- SHOW TABLE STATUS [{FROM | IN} db_name]
- [LIKE 'pattern' | WHERE expr]
- mysql> SHOW TABLE STATUS FROM gsp WHERE Name='TEST';
4: 如何修改相关表的存储引擎?
修改表的存储引擎非常简单, 语法如下所示
- ALTER TABLE my_table ENGINE = InnoDB;
- mysql> ALTER TABLE TEST ENGINE=MyISAM;
- Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
- mysql> SHOW CREATE TABLE TEST \G;
- *************************** 1. row ***************************
- Table: TEST
- Create Table: CREATE TABLE `TEST` (
- `ID` int(11) DEFAULT NULL,
- `COL1` varchar(6) DEFAULT NULL,
- `COL2` varchar(6) DEFAULT NULL,
- `COL3` varchar(6) DEFAULT NULL,
- `COL4` varchar(6) DEFAULT NULL
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1
- 1 row in set (0.00 sec)
- ERROR:
- No query specified
- mysql>
5: 创建表的时候如何指定存储引擎.
创建表的时候, 如果要指定存储引擎, 只需要设置参数 ENGINE 即可. 非常简单.
- mysql> CREATE TABLE TEST1 (ID INT) ENGINE=InnoDB;
- Query OK, 0 rows affected (0.02 sec)
- mysql> CREATE TABLE TEST2 (ID INT) ENGINE=MyISAM;
- Query OK, 0 rows affected (0.00 sec)
来源: http://www.bubuko.com/infodetail-2650620.html