MySQL存储引擎查看和修改方法

介绍

相当于Linux文件系统，但是比文件系统强大。

常见存储引擎

1. InnoDB
2. MyISAM
3. MEMORY
4. ARCHIVE
5. CSV

存储引擎查看方法

使用SELECT 确认会话存储引擎

mysql> select @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

SHOW 确认每个表的存储引擎

mysql> show create table category\G
*************************** 1. row ***************************
       Table: category
Create Table: CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `icon_url` varchar(200) DEFAULT NULL,
  `sort` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique_index` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show table status like 'category'\G
*************************** 1. row ***************************
           Name: category
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 8
 Avg_row_length: 2048
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 0
 Auto_increment: 9
    Create_time: 2020-04-26 15:28:14
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.01 sec)

INFORMATION_SCHEMA 确认每个表的存储引擎

mysql> select table_schema,table_name,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
+--------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME | ENGINE |
+--------------+------------+--------+
| dianpingdb   | category   | InnoDB |
| dianpingdb   | seller     | InnoDB |
| dianpingdb   | shop       | InnoDB |
| dianpingdb   | user       | InnoDB |
+--------------+------------+--------+
4 rows in set (0.00 sec)

存储引擎修改方法

修改默认存储引擎

会话级别修改

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+
1 row in set (0.00 sec)

mysql> set default_storage_engine=myisam;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM                   |
+--------------------------+
1 row in set (0.00 sec)

全局级别（仅影响新会话）

当前会话不生效

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM                   |
+--------------------------+
1 row in set (0.00 sec)

mysql> set global default_storage_engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| MyISAM                   |
+--------------------------+
1 row in set (0.00 sec)

创建新会话发现设置生效。

重启数据库服务，所有参数均失效。

配置文件修改（永久生效）

vim /etc/my.cnf

[mysqld]
default_storage_engine=myisam

# 重启mysql服务
service mysqld restart

注：存储引擎是表级别，每个表创建时可以指定不同的存储引擎，建议统一为innodb.

修改一张表的存储引擎

mysql> SHOW CREATE TABLE category\G
*************************** 1. row ***************************
       Table: category
Create Table: CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `icon_url` varchar(200) DEFAULT NULL,
  `sort` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique_index` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table category engine myisam;
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE category\G
*************************** 1. row ***************************
       Table: category
Create Table: CREATE TABLE `category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `icon_url` varchar(200) DEFAULT NULL,
  `sort` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_unique_index` (`name`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

修改库中所有表的存储引擎

1. 查看都出文件路径

mysql> show global variables like '%secure_file_priv%';                                                                     +------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

• secure_file_priv 为 NULL 时，表示限制mysqld不允许导入或导出。

• secure_file_priv 为 /tmp 时，表示限制mysqld只能在/tmp目录中执行导入导出，其他目录不能执行。

• secure_file_priv 没有值时，表示不限制mysqld在任意目录的导入导出。

mysql> select concat("alter table dianpingdb.",table_name,"engine tokudb;") from information_schema.tables where table_schema = 'dianpingdb' into outfile '/var/lib/mysql-files/tokudb.sql';   
Query OK, 4 rows affected (0.00 sec)

运行导出的批量修改脚本即可。