简单理解MySQL索引视图事务，存储引擎MyLSAM和InnoDB相关知识

本文主要给大家简单讲讲MySQL索引视图事务，存储引擎MyLSAM和InnoDB相关知识，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望MySQL索引视图事务，存储引擎MyLSAM和InnoDB相关知识这篇文章可以给大家带来一些实际帮助。

目前创新互联建站已为超过千家的企业提供了网站建设、域名、虚拟空间、网站运营、企业网站设计、赛罕网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。

索引的作用

设置了合适的索引之后，数据库利用各种快速的定位技术，能够大大加快查询速率；
特别是当表很大时，或者查询涉及到多个表时，使用索引可使查询加快成千倍；
可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本；
通过创建唯一性索引保证数据表数据的唯一性；
可以加快表与表之间的连接；
在使用分组和排序时，可大大减少分组和排序时间；

1，进入数据库，创建数据库，创建表

[root@master2 ~]# mysql -uroot -p     ##进入数据库
Enter password:     ##输入密码

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database school;    ##创建数据库school
Query OK, 1 row affected (0.00 sec)

mysql> use school;       ##使用数据库
Database changed
mysql> create table info(        ##创建表
        -> id int(4) not null primary key auto_increment,   ##设置主键，自动增加
        -> name varchar(10) not null,       ##名字类型varchar不为空
        -> address varchar(50) default 'nanjing',    ##默认南京
        -> age int(3) not null);
Query OK, 0 rows affected (0.02 sec)

2，向表中插入数据

mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22); 
##插入数据
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;   ##查看表内容
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

mysql> desc info;               ##查看表结构
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(4)      | NO   | PRI | NULL    | auto_increment |
| name    | varchar(10) | NO   |     | NULL    |                |
| address | varchar(50) | YES  |     | nanjing |                |
| age     | int(3)      | NO   |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3，使用create，alter，创建表直接定义三种方式创建索引（普通索引，唯一性索引）

mysql> create index index_age on info (age);   ##创建普通索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;   ##查看表中的索引
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY   |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | index_age |            1 | age         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> drop index index_age on info;   ##删除表中的索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;        ##查看表中的索引         
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

mysql> create unique index unique_name on info (name); ##创建唯一性索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;  ##查看表中索引
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY     |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | unique_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> drop index unique_name on info;  ##删除表中的索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;          ##查看表中的索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY  |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql> alter table info add unique index index_name (name);   ##使用alter插入表索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from info;   ##查看表中的索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

4，创建两个表，进行关联，多表查询

mysql> create table user(     ##创建user表
        -> id int(4) not null primary key auto_increment,   ##设置主键和自动增加
        -> name varchar(10) not null,
        -> score decimal not null,
        -> hobby int(2) not null default '1',  ##默认1
        -> index index_score (score));    ##设置索引score
Query OK, 0 rows affected (0.01 sec)

mysql> desc user;  ##查看表结构
+-------+---------------+------+-----+---------+----------------+
| Field | Type          | Null | Key | Default | Extra          |
+-------+---------------+------+-----+---------+----------------+
| id    | int(4)        | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)   | NO   |     | NULL    |                |
| score | decimal(10,0) | NO   | MUL | NULL    |                |
| hobby | int(2)        | NO   |     | 1       |                |
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

mysql> insert into user (name,score,hobby) values ('test01',88,1),('stu01',99,2),('wangwu',77,3); 
##向表中插入数据
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user;    ##查看表内容
+----+--------+-------+-------+
| id | name   | score | hobby |
+----+--------+-------+-------+
|  1 | test01 |    88 |     1 |
|  2 | stu01  |    99 |     2 |
|  3 | wangwu |    77 |     3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

mysql> create table hob(     ##创建hob表
        -> id int(2) not null primary key,
        -> hob_name varchar(10) not null);
Query OK, 0 rows affected (0.00 sec)

mysql> desc hob;   ##查看表结构
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(2)      | NO   | PRI | NULL    |       |
| hob_name | varchar(10) | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into hob (id,hob_name) values (1,'看书'),(2,'运动'),(3,'听歌');   ##插入表数据
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from hob;   ##查看表内容
+----+----------+
| id | hob_name |
+----+----------+
|  1 | 看书     |
|  2 | 运动     |
|  3 | 听歌     |
+----+----------+
3 rows in set (0.00 sec)

mysql> select * from user inner join hob on user.hobby=hob.id;   ##关联user和hob两张表
+----+--------+-------+-------+----+----------+
| id | name   | score | hobby | id | hob_name |
+----+--------+-------+-------+----+----------+
|  1 | test01 |    88 |     1 |  1 | 看书     |
|  2 | stu01  |    99 |     2 |  2 | 运动     |
|  3 | wangwu |    77 |     3 |  3 | 听歌     |
+----+--------+-------+-------+----+----------+
3 rows in set (0.00 sec)

mysql> select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id;
##去除其他内容显示name和hob_name内容
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看书     |
| stu01  | 运动     |
| wangwu | 听歌     |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id; ##设置简易名称
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看书     |
| stu01  | 运动     |
| wangwu | 听歌     |
+--------+----------+
3 rows in set (0.00 sec)

mysql> create view view_user as select u.name,h.hob_name from user u inner join hob h on u.hobby
##创建视图
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_user;  ##查看视图
+--------+----------+
| name   | hob_name |
+--------+----------+
| test01 | 看书     |
| stu01  | 运动     |
| wangwu | 听歌     |
+--------+----------+
3 rows in set (0.00 sec)

mysql> update user set hobby=3 where name='test01';  ##修改user表中内容
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_user;   ##查看视图，即视图就是表的一个链接
+--------+----------+
| name   | hob_name |
+--------+----------+
| stu01  | 运动     |
| test01 | 听歌     |
| wangwu | 听歌     |
+--------+----------+
3 rows in set (0.00 sec)

5，全文索引，组合索引

mysql> select * from info;     ##查看表内容
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

mysql> show index from info;    ##查看表的索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> create fulltext index full_addr on info (address);      ##以address创建全文索引
Query OK, 0 rows affected, 1 warning (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show index from info;  ##查看表索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info  |          0 | PRIMARY    |            1 | id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          0 | index_name |            1 | name        | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| info  |          1 | full_addr  |            1 | address     | NULL      |           2 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> select * from user;     ##查看user表内容
+----+--------+-------+-------+
| id | name   | score | hobby |
+----+--------+-------+-------+
|  1 | test01 |    88 |     3 |
|  2 | stu01  |    99 |     2 |
|  3 | wangwu |    77 |     3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)

mysql> create index index_name_score on user (name,score);    ##创建name和score的组合索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from user;   ##查看表索引
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          0 | PRIMARY          |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_score      |            1 | score       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name_score |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name_score |            2 | score       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

事务的特点

原子性：
事务是一个完整的操作，事务的各元素是不可分的（原子的）
事务中的所有元素必须作为一个整体提交或回滚
如果事务中的任何元素失败，则整个事务将失败
一致性：
当事务完成时，数据必须处于一致状态：在事务开始之前，数据库中存储的数据处于一致状态；在正在进行的事务中，数据可能处于不一致的状态；当事务成功完成时，数据必须再次回到已知的一致状态
隔离性：
对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，他不应以任何方式依赖于或影响其他事务
修改数据的事务可以在另一个使用相同数据的事务开始之前访问这些数据，或者再另一个使用相同的数据的事务结束之后访问这些数据
持久性：
事务持久性指不管系统是否发生故障，事务处理的结果都是永久的
一旦事务被提交，事务的效果会被永久的保留在数据库中

1，开启事务，往表中插入数据

mysql> select * from info;   ##查看表内容数据
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)

mysql> insert into info (name,address,age) values ('wangwu','hangzhou',30)；##插入数据

mysql> begin;   ##开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('zhaoliu','hangzhou',31);  ##插入数据
Query OK, 1 row affected (0.00 sec)

mysql> savepoint a;   ##设置保存节点a
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表数据
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> insert into info (name,address,age) values ('tianqi','hangzhou',32);  ##继续插入数据
Query OK, 1 row affected (0.00 sec)

mysql> savepoint b;   ##设置保存节点b
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('heiba','hangzhou',32);      ##继续插入数据
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;  ##查看表内容
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
|  5 | tianqi   | hangzhou |  32 |
|  6 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
6 rows in set (0.00 sec)

2，利用另一个终端查看是否成功插入

[root@master2 ~]# mysql -uroot -p  ##进入数据库
Enter password:     ##输入密码

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use school;   ##使用数据库
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> select * from info;   ##查看表内容，此时并没有提交
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)

3，使用回滚，返回保存的节点

mysql> rollback to b;    ##利用回滚到保存节点b
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表数据
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
|  5 | tianqi   | hangzhou |  32 |
+----+----------+----------+-----+
5 rows in set (0.00 sec)

mysql> rollback to a;  ##回滚到保存节点a
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;   ##查看表数据
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  4 | zhaoliu  | hangzhou |  31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> rollback;     ##回滚到初始，退出事务状态
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;  ##查看表数据
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)

4，使用commit提交事务

mysql> begin;    ##开启事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into info (name,address,age) values ('heiba','hangzhou',32);  ##插入数据
Query OK, 1 row affected (0.00 sec)

mysql> commit;   ##提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;  ##查看表数据
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

5，使用另一个终端查看

mysql> select * from info;   ##查看表数据
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

6，另一种事务的操作方式

mysql> set autocommit=0;   ##设置不自动提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> update info set address='beijing' where name='heiba';  ##修改表数据
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from info;   ##查看表信息
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | beijing  |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

##另一个终端查看
mysql> select * from info;  ##查看表信息，并没有修改
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | hangzhou |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

mysql> set autocommit=1;   ##开启自动提交事务
Query OK, 0 rows affected (0.00 sec)

##另一个终端查看
mysql> select * from info;   ##查看表数据，此时就已经修改
+----+----------+----------+-----+
| id | name     | address  | age |
+----+----------+----------+-----+
|  1 | zhangsan | beijing  |  20 |
|  2 | lisi     | shanghai |  22 |
|  3 | wangwu   | hangzhou |  30 |
|  7 | heiba    | beijing  |  32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)

存储引擎MyLSAM和InnoDB

1，查看系统默认存储引擎

mysql> show engines;   ##查看默认存储引擎innodb
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

mysql> show create table info;   ##查看创建的表的存储引擎innodb

| info  | CREATE TABLE "info" (
    "id" int(4) NOT NULL AUTO_INCREMENT,
    "name" varchar(10) NOT NULL,
    "address" varchar(50) DEFAULT 'nanjing',
    "age" int(3) NOT NULL,
    PRIMARY KEY ("id"),
    UNIQUE KEY "index_name" ("name"),
    FULLTEXT KEY "full_addr" ("address")
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

2，修改MySQL配置文件，设置默认的存储引擎

[root@localhost ~]# vim /etc/my.cnf ##修改配置文件

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysql.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
default-storage-engine=Myisam   ##添加默认存储引擎为Myisam

[root@master2 ~]# systemctl restart mysqld.service ##重启MySQL服务

3，进入数据库

[root@master2 ~]# mysql -uroot -p   ##进入数据库
Enter password:    ##输入密码

mysql> use school;    ##使用数据库
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 a ( id int );    ##创建一个a表
Query OK, 0 rows affected (0.00 sec)

mysql> show create table a;   ##查看表默认的存储引擎Myisam
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| a     | CREATE TABLE "a" (
    "id" int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table a engine=innodb;  ##修改表的存储引擎为innodb
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table a;  ##查看表的存储引擎innodb
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table                                                                        |
+-------+-------------------------------------------------------------------------------------+
| a     | CREATE TABLE "a" (
    "id" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MySQL索引视图事务，存储引擎MyLSAM和InnoDB相关知识就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的行业资讯。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。