MySQL学习笔记（六）：MySQL中查看和修改表的存储引擎

来源：互联网发布：tcp网络编程java 编辑：程序博客网时间：2024/06/05 20:09

查看系统支持的存储引擎

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网找了一个截图方便对比。

存储引擎的修改

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: 0mysql> 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)

阅读全文

0 0