MySQL 5.6学习笔记（数据库基本操作，查看和修改表的存储引擎）

2023-07-18 13:51:04

1. 数据库基本操作

1.1 查看数据库

查看数据库列表：

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sakila             |

| test               |

| world              |

+--------------------+

6 rows in set (0.08 sec)

查看数据库创建语句：

mysql> show create database test\G

*************************** 1. row ***************************

       Database: test

Create Database: CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */

1 row in set (0.05 sec)

用“\G“取代”;“，可以使得显示结果更加直观，便于查看。

1.2 创建数据库

mysql> create database test_db;

Query OK, 1 row affected (0.00 sec)

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| performance_schema |

| sakila             |

| test               |

| test_db            |

| world              |

+--------------------+

7 rows in set (0.00 sec)

1.3 选择数据库

mysql> use test_db;

Database changed

1.4 删除数据库

mysql> drop database test_db;

Query OK, 0 rows affected (0.13 sec)

2. 存储引擎

数据库存储引擎是数据库底层软件组件，数据库管理系统（DBMS）使用数据库存储引擎进行创建、查询、更新和删除数据操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。
Oracle数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySQL数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎。

2.1 InnoDB存储引擎

InnoDB事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁和外键。MySQL5.5.5之后，InnoDB作为默认存储引擎。

2.2 MyISAM存储引擎

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事务。在MySQL5.5.5之前版本中，MyISAM是默认存储引擎。

2.3 Memory存储引擎

Memory存储引擎将表中的数据存储到内存中，为查询和引用其他表数据提供快速访问。

2.4 Archive存储引擎

Archive存储引擎只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面。

2.5 存储引擎的选择

表2.5 存储引擎的比较

功能	MyISAM	Memory	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持数索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

3. MySQL中关于存储引擎的操作

3.1 查看数据库可以支持的存储引擎

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |

| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |

| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |

| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | 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         |

| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |

| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

9 rows in set (0.00 sec)

3.2 查看表使用的存储引擎

方法一：

mysql> show create table mytable;

+---------+------------------------------------------------------------------------------------------+

| Table   | Create Table                                                                             |

+---------+------------------------------------------------------------------------------------------+

| mytable | CREATE TABLE `mytable` (

  `i` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

+---------+------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

方法二：

mysql> show table status from test_db where name='mytable'\G

*************************** 1. row ***************************

           Name: mytable

         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: 0

 Auto_increment: NULL

    Create_time: 2013-11-11 16:21:13

    Update_time: NULL

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options:

        Comment:

1 row in set (0.00 sec)

3.3 修改表存储引擎

mysql> alter table mytable engine=myisam;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

3.4 创建表时指定存储引擎

create table tablename(

columnName(列名1)  type(数据类型)  attri(属性设置),

columnName(列名2)  type(数据类型)  attri(属性设置),

……..) engine = engineName

示范：

mysql> create table mytable2(i int) engine=myisam;

Query OK, 0 rows affected (0.02 sec)

mysql> show create table mytable2\G

*************************** 1. row ***************************

       Table: mytable2

Create Table: CREATE TABLE `mytable2` (

  `i` int(11) DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

3.5 修改数据库默认存储引擎

打开DOS窗口，关闭mysql服务（每种MySQL版本服务名可能不一致）： net stop mysql56
打开mysql数据目录下的my.ini文件（如：C:\ProgramData\MySQL\MySQL Server 5.6）
在my.ini文件中将default-storage-engine=INNODB 替换为default-storage-engine=MYISAM
在DOS窗口中重新启动mysql服务：net start mysql56

码农公寓