MySQL数据引擎、建库及账号管理

淡淡的烟草味﹌ 2024-03-17 14:54 40阅读 0赞

**目录**

一、引言

二、MySQL数据库引擎

1、InnoDB(MySQL默认引擎)

2、事务

ACID事务

四种隔离级别

何为脏读，幻读，不可重复读？

脏读

不可重复读

幻读

三、数据库授权

1、查看用户

2、创建用户、用户设置密码

3、设置用户授权

4、收回部分权限 (Revoke)

5、删除用户

--------------------

## 一、引言 ##

MySQL是一个开源的关系型数据库管理系统，其名称源自于创始人Michael Widenius的女儿名字"My"和"SQL"（结构化查询语言）的缩写。作为最流行的开源数据库之一，MySQL被广泛使用在各种应用和网站开发中。它具有**良好的性能、稳定性和可扩展性**，支持多种操作系统，并且提供了丰富的功能和灵活的配置选项。

MySQL提供了强大的数据管理功能，包括创建、修改和删除数据库、数据表和索引，以及实现数据的**增删改查**操作。它支持多种数据类型，如整数、字符、日期和时间等，并提供了丰富的内置函数和操作符，方便进行数据处理和计算。

除了基本的数据管理功能，MySQL还提供了高级特性，如**事务处理、存储过程、触发器和视图**等。它支持多用户并发访问，可以通过用户权限管理实现对数据库的安全控制。

另外，MySQL提供了多种**编程接口和驱动程序**，使开发人员可以使用各种编程语言（如**Python、Java、PHP**等）与数据库进行交互。同时，MySQL还提供了可视化工具，如MySQL Workbench，以便于开发人员管理和操作数据库。

总的来说，MySQL是一个功能强大、易于使用且可靠稳定的数据库管理系统，它在各种应用场景下都得到了广泛应用，并且不断发展和更新，以满足不断变化的数据管理需求。

## 二、MySQL数据库引擎 ##

数据库存储引擎是数据库底层软件组织，数据库管理系统（**DBMS**）使用数据引擎进行**创建、查询、更新和删除数据**。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。

MySQL 提供了多个不同的存储引擎，包括处理事务安全表的引擎和**处理非事务安全表的引擎**。在 MySQL 中，不需要在整个服务器中使用同一种存储引擎，针对具体的要求，可以对每一个表使用不同的存储引擎。

MySQL 5.7 支持的存储引擎有 **InnoDB、MyISAM、Memory、Merge、Archive、CSV、BLACKHOLE** 等，可分别根据各个引擎的功能和特性为不同的数据库处理任务提供各自不同的适应性和灵活性。

> MySQL数据库引擎是MySQL系统中负责数据存储和访问的核心组件。MySQL提供了多个不同的数据库引擎，每个引擎有其特定的特性和适用场景。

> 以下是MySQL中一些常见的数据库引擎：

1.  InnoDB引擎：InnoDB是MySQL的默认引擎，它提供了ACID（原子性、一致性、隔离性、持久性）事务支持，具备高并发性能和自动故障恢复能力。它支持行级锁定和外键约束等高级特性，适用于大型应用和高并发环境。
2.  MyISAM引擎：MyISAM是MySQL的另一个常用引擎，它提供了较快的读取速度和高效的全文索引功能。但是，MyISAM不支持事务和行级锁定，对于频繁的写入操作性能相对较差，适用于读取为主的应用场景。
3.  MEMORY引擎：MEMORY引擎将数据存储在内存中，提供了快速的读写速度，适用于对性能要求极高的应用。然而，由于数据存储在内存中，一旦数据库重启或崩溃，所有数据都会丢失。因此，MEMORY引擎适用于缓存数据、临时表和其他临时性数据存储。
4.  NDB Cluster引擎：NDB Cluster引擎是MySQL集群中的一种专用引擎，它提供了高可用性和高扩展性的数据存储解决方案。它能够将数据分布在多个节点上，并提供了实时数据复制和故障恢复功能。

除了以上几种常见的引擎，MySQL还提供了其他一些引擎，如ARCHIVE引擎、CSV引擎等，每个引擎有其独特的特点和用途。在选择适合的数据库引擎时，需要根据具体的需求考虑数据的读写模式、数据一致性要求、并发访问情况等因素。

### 1、InnoDB(MySQL默认引擎) ###

InnoDB是事务型存储引擎，提供了对数据库ACID事务的支持，并实现了SQL标准的四种**隔离级别**，具有行级锁定（这一点说明锁的粒度小，在写数据时，不需要锁住整个表，因此适用于**高并发**情形）及外键支持（所有数据库引擎中独一份，仅有它支持外键）该引擎的设计目标便是处理大容量数据的数据库系统，MySQL在运行时InnoDB会在内存中建立缓冲池，用于缓存数据及索引。

> InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键。**InnoDB是默认的MySQL引擎**。

SHOW ENGINES

> 我们可以看到所有的引擎

![b3a1060683584bd7a513939d479b5b54.png][]

> InnoDB（**行锁定**）
> 
> MyISAM（**表锁定**）：查询效率高

### 2、事务 ###

#### ACID事务 ####

1.  **原子性(Atomicity)**：事务是一个原子性质的操作单元，事务里面的对数据库的操作**要么都执行，要么都不执行**。
2.  **一致性(Consistency)**：在事务开始之前和完成之后，**数据都必须保持一致状态**，必须保证数据库的完整性。也就是说，数据必须符合数据库的规则。
3.  **隔离性(Isolation）**:一个事务的执行不能被其他事务干扰。即一个事务的内部操作及使用的数据对其他并发事务是隔离的，并发**执行的各个事务之间不能互相干扰**。
4.  **持久性(Durability）**:持久性也成为**永久性，**指一个事务一旦提交，它对数据库中数据的改变就应该是永久的。

#### 四种隔离级别 ####

1.   **读未提交（Read Uncommitted）**：在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）
2.   **读已提交（Read Committed）**：这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（NonrepeatableRead），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果
3.  **可重复读（Repeatable Read）**:这是MySQL的默认事务隔离级别，同一事务的多个实例在并发读取数据时，会看到同样的数据。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
4.  **可串行化（Serializable）**：这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争

#### 何为脏读，幻读，不可重复读？ ####

> 幻读：**再查询的那一刻查询的是多少，但是其实并不是那么多；有可能在查询的这一刻又更改了数据。**

##### 脏读 #####

脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

![225ac7e0b2f8483da5fffaf0b63f7f4c.png][]

> T2时刻，事务B把原来张三的成绩由原数据80改为了70，此后又被T3时刻的事务A读取到了，但是T4时刻事务B发生异常，进行了回滚操作。**这个过程，我们称70为脏数据，事务A进行了一次脏读。**

##### 不可重复读 #####

不可重复读，有时候也会说成“读已提交”。什么意思呢，就是在一个事务内，多次读取同一个数据，却返回了不同的结果。实际上，这是因为在该事务间隔读取数据的期间，有其他事务对这段数据进行了修改，并且已经提交，就会发生不可重复读事故。

![658a5688433c4d66890efe080a9e98ec.png][]

> 图示中事务A在T1和T4查询同一语句，却得到了不同的结果，这是因为T2~T3时刻事务B对该数据进行了修改，并提交。**这个过程，出现了在一个事务内两次读到的数据却是不一样的，我们称为是不可重复读**。

**不可重复读**和**脏读**的区别：前者是“读已提交”，后者是“读未提交”

##### 幻读 #####

幻读是指当事务不独立执行时，插入或者删除另一个事务当前影响的数据而发生的一种类似幻觉的现象。举个例子，某事务在检查表中的数据数count时，是10，过一段时间之后再查是11，这就发生了幻读，之前的检测获取到的数据如同幻觉一样。

出现幻读和不可重复读的原因很像，都是在多次操作数据的时候发现结果和原来的不一样了，出现了其他事务干扰的现象。**但是，幻读的偏重点是添加和删除数据，多次操作数据得到的记录数不一样；不可重复读的偏重点是修改数据，多次读取数据发现数据的值不一样了。**

![c7f7e3b1046d4c7eb13d6f760f9e50d3.png][]

> 事务B向表中新插入了一条数据，事务A在T3时刻后查询数据的时候，突然发现数据和以前查询的时候多出了一项，像产生了幻觉一样。

## 三、数据库授权 ##

> 授权是通过GRANT语句来实现的，它允许管理员分配给用户**特定的权限**，以便他们能够**执行特定的数据库操作**。

### 1、查看用户 ###

> 我们必须在mysql里面进行一个查询，也就是切换数据库
> 
>     use mysql;
>     SELECT * from user;

![822734f73d82441f87de7ba9cb2252da.png][]

### 2、创建用户、用户设置密码 ###

> 我们首先查看一下我们的数据库的版本，我用的是**8.0.18**版本，MySQL **5.7** 版本是不一样的。 **MySQL 5.7 版本不再使用 Password 来作为密码的字段，而改成了 authentication\_string。**
> 
>     查看版本
>     SELECT VERSION()
> 
>   
> ![41ebf026f94f42729010843ae8ccc002.png][]

创建用户
    create user test01;
    设置密码
    ALTER USER 'test01'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

创建成功我们测试连接

![e8fd64d4a5024ab6a4abbc0a67d8b29d.png][]

### 3、设置用户授权 ###

> **语法**：grant privileges on databasename.tablename to username@'host';  
> 给 test01用户 赋予 数据库mybatis\_ssm中的表t\_p1\_user 查询权限  
>  **grant SELECT on** mybatis\_ssm.t\_p1\_user **to** test01@'%';
> 
> 给 test01用户 赋予 数据库db\_xiaoli中的表t\_p1\_user 修改权限  
> **grant UPDATE on** mybatis\_ssm.t\_p1\_user **to** test01@'%';  
> 给 test01用户 赋予 数据库mybatis\_ssm中所有表 查询权限  
> **grant SELECT on** mybatis\_ssm.\* **to** test01@'%';  
> 给 test01用户 赋予 数据库mybatis\_ssm中所有表 所有权限  
>  **grant ALL on** mybatis\_ssm.\* **to** test01**@'%';**

> 我们运行后刷新一下test01可以看到我们的mybatis\_ssm已经赋予过去了

![259d6b1d75164d5caed92b23c2f93e2c.png][]

### 4、收回部分权限 (Revoke) ###

> **语法：**revoke privileges on databasename.tablename from username@'host';  
> 啥也不能回收,不会对GRANT ALL PRIVILEGES ON mybatis\_ssm.\* TO test01@%有任何影响
> 
>     revoke DELETE on mybatis_ssm.t_p1_user from test01@'%';
> 
>   
> 可以回收GRANT SELECT, UPDATE ON mybatis\_ssm.t\_p1\_user TO test01@%这条权限语句
> 
>     revoke all on mybatis_ssm.t_p1_user from test01@'%';
> 
>   
> 可以回收GRANT ALL PRIVILEGES ON mybatis\_ssm.\* TOtest01@%这条赋权语句带来的权限
> 
>     revoke all on mybatis_ssm.* from test01@'%';

> 我们运行后刷新一下test01可以看到我们的mybatis\_ssm已经被回收了

![db33588ae48349b9ba8e89957135a201.png][]

### 5、删除用户 ###

> 我们运行后重新连接就会出现以下的问题

DROP User test01

![55a7ec60ca174125925bf6f54a8f609a.png][]

![8a9d42c8c0054019bf603b6a10320a82.png][]

[b3a1060683584bd7a513939d479b5b54.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/d1c28de83751464fa99b57b2f2a7a2d0.png
[225ac7e0b2f8483da5fffaf0b63f7f4c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b96b41bb0cec4bc591ba52e8eb8a781f.png
[658a5688433c4d66890efe080a9e98ec.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/1ca3b31099c9470da1725489346cf217.png
[c7f7e3b1046d4c7eb13d6f760f9e50d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/48afe06a40ee471aa84bd11b97e57488.png
[822734f73d82441f87de7ba9cb2252da.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/1e9b3a86370840cfa6007b2d0f54a8d5.png
[41ebf026f94f42729010843ae8ccc002.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/4ea58691339746529412df8cd82492ea.png
[e8fd64d4a5024ab6a4abbc0a67d8b29d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/2248297da7ba498eb223e30347027260.png
[259d6b1d75164d5caed92b23c2f93e2c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/4d15c9e5d3c44f84b4db4dfb6b02b770.png
[db33588ae48349b9ba8e89957135a201.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/97e516a2724f45d1a05f86157c950834.png
[55a7ec60ca174125925bf6f54a8f609a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/31ca342d30e341b189a594f4e9993fda.png
[8a9d42c8c0054019bf603b6a10320a82.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/124266e873264ed083f7c69b281bf6da.png