一、配置适合的存储引擎
1.0 查看数据库可配置的存储引擎
方法: 登录mysql , 使用show engines ;查看系统支持的引擎
[root@mysql ~]# mysql -uroot -p123123
mysql> show engines\G
*************************** 1. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: InnoDB【默认为InnoDB引擎】
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 3. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
6 rows in set (0.00 sec)
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | 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 |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)
1.1 查看表正在使用的存储引擎
1.1.0 查看当前mysql的默认引擎
mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | 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 |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
6 rows in set (0.00 sec)
1.1.1 查看使用的存储引擎
方法一:show table status from 库名 where name= '表名';
mysql> show table status from client where name='user_info'\G
*************************** 1. row ***************************
Name: user_info
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 9437184
Auto_increment: NULL
Create_time: 2019-05-13 18:15:29
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
方法二: show create table 表名;
mysql> show create table client.user_info\G
*************************** 1. row ***************************
Table: user_info
Create Table: CREATE TABLE `user_info` (
`身份证` int(20) DEFAULT NULL,
`姓名` char(20) DEFAULT NULL,
`性别` char(2) DEFAULT NULL,
`用户ID号` int(110) DEFAULT NULL,
`资费` int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1.2配置存储引擎为所选择的类型
方法一:alter table 表名 engine=引擎
mysql> alter table client.user_info engine=myisam;
Query OK, 5 rows affected (0.06 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> show create table client.user_info\G
*************************** 1. row ***************************
Table: user_info
Create Table: CREATE TABLE `user_info` (
`身份证` int(20) DEFAULT NULL,
`姓名` char(20) DEFAULT NULL,
`性别` char(2) DEFAULT NULL,
`用户ID号` int(110) DEFAULT NULL,
`资费` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
方法二:修改my.cnf的default-storage-engine 为引擎
[root@mysql ~]# vim /etc/my.cnf
27 [mysqld]
32 default-storage-engine=MyISAM【手动添加】
[root@mysql ~]# /etc/init.d/mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
[root@mysql ~]# mysql -uroot -p123123 -e 'show engines\G'
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 3. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
mysql> create database yunjisuan;
Query OK, 1 row affected (0.00 sec)
mysql> use yunjisuan;
Database changed
mysql> create table user(name char(10),id int(10));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table user\G
*************************** 1. row ***************************
Table: user
Create Table: CREATE TABLE `user` (
`name` char(10) DEFAULT NULL,
`id` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8【此时新建的表已经默认为myisam引擎了】
1 row in set (0.00 sec)
方法三: create table 建立表时使用 engine=引擎
mysql> use yunjisuan;
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> create table id(id int) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table yunjisuan.id;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------+
| id | CREATE TABLE `id` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
方法四:mysql_convert_table_format --user=root --password=密码 --sock=文件路径 /mysql.sock --engine=引擎 库名 表名
mysql> show create table client.user_info\G
*************************** 1. row ***************************
Table: user_info
Create Table: CREATE TABLE `user_info` (
`身份证` int(20) DEFAULT NULL,
`姓名` char(20) DEFAULT NULL,
`性别` char(2) DEFAULT NULL,
`用户ID号` int(110) DEFAULT NULL,
`资费` int(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
[root@mysql ~]# yum -y install perl-DBD-MySQL-4.023-5.el7.x86_64 perl-DBI-1.627-4.el7.x86_64【为mysql的俩驱动,不安装驱动会到导致第四个方法失败】
[root@mysql ~]# mysql_convert_table_format --user=root --password=123123 --sock=/tmp/mysql.sock --engine=innoDB client user_info【innodb改为myisam会报以下错】
user_info already uses the 'MYISAM' engine; Ignored
【注意:使用此方法从Innodb改为myisam没有问题,但从myisam改为innodb不行。若忘记了命令的选项,可以通过--help来查看帮助信息】