mysql的存储引擎问题(以mysql5.6为例,引擎主要针对表)
如果对运维课程感兴趣,可以在b站上、csdn或微信视频号 上搜索我的账号: 运维实战课程,可以关注我,学习更多免费的运维实战技术视频
1.mysql的默认存储引擎设置:
方式1:修改my.cnf配置文件(根据自己情况查看文件路径位置,永久修改)
刚安装完mysql后的用户状态:
[root@bogon ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM 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 |
| 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 |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
默认是innodb存储引擎时,建立的库表的存储引擎就是innodb的:
mysql> create database ku1;
mysql> use ku1;
mysql> create table biao (name varchar(10),age int(2));
mysql> insert into biao values("shi1",20);
mysql> insert into biao values("shi2",21);
mysql> select * from ku1.biao;
+------+------+
| name | age |
+------+------+
| shi1 | 20 |
| shi2 | 21 |
+------+------+
mysql> show create database ku1\G #库的存储引擎查看不到,一般是指表的
*************************** 1. row ***************************
Database: ku1
Create Database: CREATE DATABASE `ku1` /*!40100 DEFAULT CHARACTER SET latin1 */
mysql> show create table ku1.biao\G #查看表的存储引擎,主要是表的
*************************** 1. row ***************************
Table: biao
Create Table: CREATE TABLE `biao` (
`name` varchar(10) DEFAULT NULL,
`age` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> quit
修改存储为myisam存储引擎时,建立的库表的存储引擎就是myisam的:
[root@bogon ~]# vim /usr/my.cnf
[mysqld]
default-storage-engine=myisam/MyISAM #或innodb/innoDB #添加存储引擎设置
[root@bogon ~]# /etc/init.d/mysql restart
[root@bogon ~]# mysql -uroot -p123
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM 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 | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
mysql> create database ku2;
mysql> use ku2;
mysql> create table biao (name varchar(10),age int(2));
mysql> insert into biao values("shi1",20);
mysql> insert into biao values("shi2",21);
mysql> select * from ku2.biao;
+------+------+
| name | age |
+------+------+
| shi1 | 20 |
| shi2 | 21 |
+------+------+
mysql> show create database ku2\G
*************************** 1. row ***************************
Database: ku2
Create Database: CREATE DATABASE `ku2` /*!40100 DEFAULT CHARACTER SET latin1 */
mysql> show create table ku2.biao\G
*************************** 1. row ***************************
Table: biao
Create Table: CREATE TABLE `biao` (
`name` varchar(10) DEFAULT NULL,
`age` int(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
方式2:设置当前会话的默认存储引擎
刚安装完mysql后的用户状态:
[root@bogon ~]# mysql -uroot -p123
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM 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 |
| 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 |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
默认是innodb存储引擎时,建立的库表的存储引擎就是innodb的:
mysql> create database ku1;
mysql> use ku1;
mysql> create table biao (name varchar(10),age int(2));
mysql> insert into biao values("shi1",20);
mysql> insert into biao values("shi2",21);
mysql> select * from ku1.biao;
+------+------+
| name | age |
+------+------+
| shi1 | 20 |
| shi2 | 21 |
+------+------+
mysql> show create database ku1\G #库的存储引擎查看不到,一般是指表的
*************************** 1. row ***************************
Database: ku1
Create Database: CREATE DATABASE `ku1` /*!40100 DEFAULT CHARACTER SET latin1 */
mysql> show create table ku1.biao\G #查看表的存储引擎,主要是表的
*************************** 1. row ***************************
Table: biao
Create Table: CREATE TABLE `biao` (
`name` varchar(10) DEFAULT NULL,
`age` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> quit
修改存储为myisam存储引擎时,建立的库表的存储引擎就是myisam的:
[root@bogon ~]# mysql -uroot -p123
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM 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 |
| 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 |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
mysql> set storage_engine=myisam/MyISAM; 或innodb/innoDB
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | DEFAULT | MyISAM 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 | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
mysql> create database ku2;
mysql> use ku2;
mysql> create table biao (name varchar(10),age int(2));
mysql> insert into biao values("shi1",20);
mysql> insert into biao values("shi2",21);
mysql> select * from ku2.biao;
+------+------+
| name | age |
+------+------+
| shi1 | 20 |
| shi2 | 21 |
+------+------+
mysql> show create database ku2\G
*************************** 1. row ***************************
Database: ku2
Create Database: CREATE DATABASE `ku2` /*!40100 DEFAULT CHARACTER SET latin1 */
mysql> show create table ku2.biao\G
*************************** 1. row ***************************
Table: biao
Create Table: CREATE TABLE `biao` (
`name` varchar(10) DEFAULT NULL,
`age` int(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
2.mysql的表的存储引擎创建,修改和查看
1)创建新表时候指定存储引擎:
[root@bogon ~]# mysql -uroot -p123
mysql> create database ku;
mysql> use ku;
mysql> create table biao(id int(2))engine=innodb; #创建表时指定引擎#innodb/innoDB或:myisam/MyISAM
mysql> show create table biao\G
*************************** 1. row ***************************
Table: biao
Create Table: CREATE TABLE `biao` (
`id` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
2)修改已有表的存储引擎:
[root@bogon ~]# mysql -uroot -p123
mysql> use ku;
mysql> show tables;
+--------------+
| Tables_in_ku |
+--------------+
| biao |
+--------------+
mysql> show create table biao\G
*************************** 1. row ***************************
Table: biao
Create Table: CREATE TABLE `biao` (
`id` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> alter table biao engine=myisam; #修改表的存储引擎,innodb/innoDB或:myisam/MyISAM
mysql> show create table biao\G
*************************** 1. row ***************************
Table: biao
Create Table: CREATE TABLE `biao` (
`id` int(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
如果对运维课程感兴趣,可以在b站上、csdn或微信视频号 上搜索我的账号: 运维实战课程,可以关注我,学习更多免费的运维实战技术视频