1、 查看数据库可配置的存储引擎
方法：登录mysql，使用show engines;查看系统所支持的引擎
[root@localhost ~]# mysql
MariaDB [(none)]> show engines\G
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
Comment: Non-transactional engine with good performance and small data footprint
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: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: Stores tables as CSV files
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: gzip-compresses tables for a low storage footprint
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
*************************** 9. row ***************************
Engine: FEDERATED
Support: YES
Comment: Allows to access tables on other MariaDB servers, supports transactions and more
Transactions: YES
XA: NO
Savepoints: YES
*************************** 10. row ***************************
Engine: Aria
Support: YES
Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
XA: NO
Savepoints: NO
10 rows in set (0.00 sec)
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
方法一：
MariaDB [(none)]> create database aaa;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use aaa
Database changed
MariaDB [aaa]> create table t1(id int(10) not null,name char(20));
Query OK, 0 rows affected (0.14 sec)
MariaDB [aaa]> show table status from aaa where name='t1'\G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: NULL
Create_time: 2019-10-15 21:15:19
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.02 sec)
方法二：show create table 表名；
MariaDB [aaa]> show create table aaa.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) NOT NULL,
`name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
3、配置存储引擎为所选择的类型
方法一：alter table表名 engine=引擎；
MariaDB [aaa]> alter table aaa.t1 engine=myisam;
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [aaa]> show create table aaa.t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(10) NOT NULL,
`name` char(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
MariaDB [aaa]> show table from aaa where name='t1'\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'from aaa where name='t1'' at line 1
MariaDB [aaa]> show table status from aaa where name='t1'\G
*************************** 1. row ***************************
Name: t1
Engine: MyISAM
Version: 10
Row_format: Fixed
Rows: 0
Avg_row_length: 0
Data_length: 0
Max_data_length: 7036874417766399
Index_length: 1024
Data_free: 0
Auto_increment: NULL
Create_time: 2019-10-15 21:18:07
Update_time: 2019-10-15 21:18:07
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
MariaDB [aaa]> exit
Bye
方法二：修改my.cnf的default-storage-engine为引擎
[root@localhost ~]# vim /etc/my.cnf
【mysql】
default-storage-engine=myisam
[root@localhost ~]# systemctl restart mariadb
[root@localhost ~]# mysql
MariaDB [(none)]> show engines\G
*************************** 1. row ***************************
Engine: InnoDB
Support: YES
Comment: Percona-XtraDB, Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Non-transactional engine with good performance and small data footprint
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: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: Stores tables as CSV files
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: gzip-compresses tables for a low storage footprint
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
*************************** 9. row ***************************
Engine: FEDERATED
Support: YES
Comment: Allows to access tables on other MariaDB servers, supports transactions and more
Transactions: YES
XA: NO
Savepoints: YES
*************************** 10. row ***************************
Engine: Aria
Support: YES
Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
XA: NO
Savepoints: NO
10 rows in set (0.00 sec)
MariaDB [(none)]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB | YES | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | DEFAULT | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| ARCHIVE | YES | gzip-compresses tables for a low storage footprint | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | YES | Allows to access tables on other MariaDB servers, supports transactions and more | YES | NO | YES |
| Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)
方法三：create table 建立表时使用engine=引擎
MariaDB [(none)]> use aaa
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
MariaDB [aaa]> create table t2(id int(10) not null,name char(20)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
MariaDB [aaa]> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(10) NOT NULL,
`name` char(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
