mysql操作语句与事务

数据库设计范式

数据库设计的三大范式

1. ‌第一范式（1NF）‌：要求数据库表的每一列都是不可分割的原子数据项，即列中的每个值都应该是单一的、不可分割的实体。例如，如果一个表中的“地址”列包含了省、市、区等多个信息，那么这就不符合第一范式，需要将地址拆分为多个列，如“省份”、“城市”和“详细地址”。

2. ‌第二范式（2NF）‌：在满足第一范式的基础上，要求数据库表中的每一列都必须完全依赖于主键，而不是仅仅依赖于主键的一部分。这意味着表中的每一行数据都可以被唯一标识，并且非主键列必须完全依赖于整个主键，而不是主键的某一部分。

3. ‌第三范式（3NF）‌：在满足第二范式的基础上，要求数据库表中的每一列数据都必须直接依赖于主键，而不能间接依赖。这有助于进一步减少数据冗余，提高数据的独立性和一致性。

数据库设计范式的作用和好处

• ‌减少数据冗余‌：通过范式设计，可以确保每个属性只存储一次，避免了数据的重复存储。
• ‌增加数据完整性‌：范式设计确保了数据的依赖关系清晰，减少了数据不一致的情况。
• ‌简化数据修改操作‌：通过合理的表结构设计，可以简化数据的插入、删除和更新操作，提高数据库的维护效率。

sql操作

sql指令有如下几种

1. DDL（Data Definition Language）‌：数据定义语言，用于定义数据库对象，包括数据库、表、字段等。主要操作包括创建、修改和删除数据库和表。例如，创建数据库的语句为CREATE DATABASE database_name，创建表的语句为CREATE TABLE table_name (column1 datatype, column2 datatype, ...)。‌

2. DML（Data Manipulation Language）‌：数据操作语言，用于对数据库表中的数据进行增删改操作。主要包括INSERT（插入）、DELETE（删除）、UPDATE（更新）等语句。例如，插入数据的语句为INSERT INTO table_name (column1, column2) VALUES (value1, value2)。

3. DQL（Data Query Language）‌：数据查询语言，用于查询数据库中的记录。主要语句为SELECT，用于从表中检索数据。例如，查询表中所有记录的语句为SELECT * FROM table_name。‌

4. DCL（Data Control Language）‌：数据控制语言，用于控制数据库的用户权限和安全设置。主要包括GRANT（授权）、REVOKE（撤销权限）等语句。例如，授权用户访问表的语句为GRANT SELECT ON database.table TO 'username'@'host';‌

DDL语句

新建表 create table 

CREATE TABLE `test_db`  (
`id` bigint(20) UNSIGNED NOT NULL COMMENT 'id',
`price` int(8) NOT NULL COMMENT '价格',
`name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名字',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '测试表' ROW_FORMAT = Compact;

新建表后的结构

修改表

增加列“test” 类型为varchar

ALTER TABLE test_db
ADD COLUMN test VARCHAR(255);

结构

可以看到多了个test列 类型为 varchar

修改字段类型 

把表test_db test列修改成 int类型

ALTER TABLE test_db
MODIFY COLUMN test INT;

结构

可以看到test列类型修改成了int类型

修改列的名字且修改类型成varchar

ALTER TABLE test_db
CHANGE COLUMN test test_update VARCHAR(255);

修改后结构

删除列

ALTER TABLE test_db
DROP COLUMN test_update;

删除后结构

删除表

现在表结构及数据

删除表中所有的数据
TRUNCATE TABLE test_db

TRUNCATE TABLE test_db;

清除后

表还存在但是之前的数据全部被清除

删除表结构及数据

原表结构及数据

DROP TABLE IF EXISTS test_db;

再次查看表数据

可以看到表已被删除

DML语句

首先新建一张表

DROP TABLE IF EXISTS `test_db`;
CREATE TABLE `test_db`  (
`id` bigint(20) UNSIGNED NOT NULL COMMENT 'id',
`price` int(8) NOT NULL COMMENT '价格',
`name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名字',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '测试表' ROW_FORMAT = Compact;

插入新增数据

INSERT INTO test_db (id, price,name) VALUES (1, 3, "测试");

插入后数据

修改数据

UPDATE test_db SET id = 3, price = 30, name="33" WHERE id = 1;

修改后表数据

删除部分数据

DELETE FROM test_db where id=3;

删除后表数据

事务

特点

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

而且事务有个很重要的特点，简称ACID

ACID代表原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）

1. ‌原子性（Atomicity）‌：原子性确保事务中的所有操作要么全部完成，要么全部不执行。事务是不可分割的最小工作单元，如果事务中的任何操作失败，整个事务都会回滚到事务开始之前的状态‌。

2. ‌一致性（Consistency）‌：一致性确保事务将数据库从一个一致的状态转变到另一个一致的状态。这意味着数据库在事务开始之前和完成之后都必须遵守所有的业务规则、完整性约束等。如果事务违反了这些规则，整个事务将被回滚，数据库将保持之前的一致状态‌12。

3. ‌隔离性（Isolation）‌：隔离性确保并发事务的执行不会互相干扰。事务应该独立于彼此运行，使得它们不能看到彼此的中间状态。数据库系统通常通过锁定对象来实现隔离性，根据不同的隔离级别，事务可以看到其他事务的提交或未提交的更改‌。

4. ‌持久性（Durability）‌：持久性确保一旦事务被提交，它对数据库的改变就是永久性的。即使系统出现故障，如崩溃或电源故障，事务的效果也不会丢失。数据库通过将事务日志记录到非易失存储介质来实现持久性‌。

这些特性共同作用，确保数据库在面对各种操作时能够保持数据的完整性和一致性。ACID属性是关系型数据库管理系统（RDBMS）的基石，使得数据库能够在出现故障或错误时保持数据的完整性和可靠性‌

常见事务问题

在开发过程中，有多个线程同时对db操作时会出现并发事务问题：脏读、不可重复读、幻读

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据。
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

为了应对这种情况，mysql有如下几类事务隔离级别

1. ‌读未提交（Read Uncommitted）‌：最低级别的隔离，允许事务读取未提交的数据，可能会导致脏读、不可重复读和幻读。
2. ‌读已提交（Read Committed）‌：允许事务读取已经提交的数据，可以避免脏读，但可能会导致不可重复读和幻读。
3. ‌可重复读（Repeatable Read）‌：确保在同一事务中多次读取相同的数据，可以避免脏读和不可重复读，但可能会出现幻读。这是MySQL的默认隔离级别。
4. ‌串行化（Serializable）‌：最高的隔离级别，强制事务串行执行，避免脏读、不可重复读和幻读，但可能会影响并发性能。

除了设置事务隔离级别，还有其它的解决办法

解决脏读：

还可以加锁

解决不可重复读：

1，可以加表锁和行锁使得给定行数据不会被其它的事务修改，但是但在高并发的场景下可能会影响性能

START TRANSACTION;
SELECT * FROM table_name WHERE condition FOR UPDATE; 行级锁
-- 在此期间，其他事务无法修改这些行
COMMIT;LOCK TABLES table WRITE; 表级锁
-- 进行一些操作 在此期间，其他事务无法修改这张表
UNLOCK TABLES;

2,乐观锁，版本号，在表中添加一个版本号字段，每次数据更新时增加版本号。在读取数据时获取版本号，更新数据时检查版本号是否改变

START TRANSACTION;
SELECT version FROM table WHERE id = 1; -- 获取当前版本号
UPDATE table SET data = 'new_data', version = version + 1 WHERE id = 1 AND version = <当前版本号>;
IF ROW_COUNT() = 0 THEN-- 版本号已改变，处理冲突或回滚ROLLBACK;
ELSECOMMIT;
END IF;

3，时间戳，类似于版本号，使用时间戳字段来检测数据是否被其他事务修改。更新时检查时间戳是否相同

START TRANSACTION;
SELECT timestamp FROM table WHERE id = 1; -- 获取当前时间戳
UPDATE table SET data = 'new_data', timestamp = NOW() WHERE id = 1 AND timestamp = <当前时间戳>;
IF ROW_COUNT() = 0 THEN-- 时间戳已改变，处理冲突或回滚ROLLBACK;
ELSECOMMIT;
END IF;

解决幻读：

1，排他锁 SELECT ... FOR UPDATE

2，共享锁 SELECT ... LOCK IN SHARE MODE

3，使用间隙锁（Gap Locks）和临键锁（Next-Key Locks）

操作演示

首先看看mysql当前默认隔离级别

SELECT @@transaction_isolation;

可以看到隔离级别为‌可重复读（Repeatable Read）

读未提交

修改事务隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT @@transaction_isolation;

脏读

1，会话1session 开启事务查询数据

START TRANSACTION;
select * from test_db;

2，会话2session 开启事务增加条数据

START TRANSACTION;
INSERT INTO test_db (id, price,name) VALUES (133, 1, "增加条数据");

3，会话1session 查询表数据

此时会话1看到了事务2未提交的数据13，出现了脏读

不可重复读

1，事务1开启查询id=13的数据

START TRANSACTION;
select * from test_db where id=13;

2，事务2修改id=13的price=30

START TRANSACTION;
UPDATE test_db SET price = 30 WHERE id = 13;

3，事务1查看id=13的表数据

此时出现了不可重复读：前后两次查询数据不一致

幻读

1，事务1开启 进行范围查询

START TRANSACTION;
select * from test_db where id>32;

2，事务2插入新数据id=161

START TRANSACTION;
INSERT INTO test_db (id, price,name) VALUES (161, 1, "增加条数据");

3，事务1再次范围查询 id>32的数据

此时出现了幻读，本来没有id=161的数据，再次查询出现了id=161的数据

读已提交

修改事务隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED ;
SELECT @@transaction_isolation;

脏读

1，事务1开启查询数据

START TRANSACTION;
select * from test_db;

2，事务2开启增加表数据 id=15 不提交事务

START TRANSACTION;
INSERT INTO test_db (id, price,name) VALUES (191, 39, "增加条数据");

3，事务1再次查看数据

可以看到未读取到事务2未提交的数据

4，事务2提交

commit;

5，事务1再次查看数据

可以看到当事务2提交了，事务1才可以看到新增的数据

所以当前隔离级别没有脏读问题

不可重复读

1，事务1开启查看数据id=13

START TRANSACTION;
select * from test_db where id=13;

2，事务2开启修改id=13的数据 price=13

START TRANSACTION;
UPDATE test_db SET price = 13 WHERE id = 13;
commit;

3，事务1再次查看数据id=13

此时两次数据不一致，出现了不可重复读

幻读

1,事务1开启范围查询

START TRANSACTION;
select * from test_db where id>30 and id<35;

2，事务2插入id=33数据

START TRANSACTION;
INSERT INTO test_db (id, price,name) VALUES (33, 39, "增加条数据");

3，事务1再次进行范围查询

此时同一事务范围查询中出现了原来没有的数据行，即幻读

可重复读

现在来演示下在可重复读下各类事务问题

脏读：

会话1session 开启事务插入一条数据

START TRANSACTION;
INSERT INTO test_db (id, price,name) VALUES (3, 1, "测试");

会话1session查看表数据

可以看到多了条数据

会话2session查看表数据

可以看到会话2看不到会话1未提交的事务插入的数据

会话1提交事务

commit;

会话2查看表数据

可以看到会话2可以看到会话1提交事务后插入的数据

所以可重复读（Repeatable Read）解决了脏读的问题

不可重复读：

会话1session开启事务且读且表数据

START TRANSACTION;
select * from test_db;

会话2session开启事务且进行插入数据再查看数据提交事务

START TRANSACTION;
INSERT INTO test_db (id, price,name) VALUES (7, 1, "3");
INSERT INTO test_db (id, price,name) VALUES (11, 1, "3");
select * from test_db;
commit

可以看到会话2多了2条数据

会话1再次查看数据 此时会话1还未提交事务

可以看到会话1在事务哪看到的数据还是3条和原来保持相同

这样即解决了不可重复读（事务内两次看到的数据不同的问题）

幻读：

原表数据

业务操作：当id=31的表数据不存在的时候插入一条数据id=31

正常情况下：

会话1session开启事务条件查询 id=31的数据

START TRANSACTION;
select * from test_db where id=31;

查询表数据为空

此时会话1增加一条数据

INSERT INTO test_db (id, price,name) VALUES (31, 1, "正常情况下增加表数据");

多线程多事务操作：当id=32的表数据不存在的时候插入一条数据id=32

1，会话1session查询id=32的数据

START TRANSACTION;
select * from test_db where id=32;

数据为空

2，事务2开启插入id=32的数据

START TRANSACTION;
INSERT INTO test_db (id, price,name) VALUES (32, 1, "多事务情况下增加表数据");
select * from test_db where id=32;

事务2看到新增的数据 成功新增数据

3，事务1插入数据

INSERT INTO test_db (id, price,name) VALUES (32, 1, "多事务情况下增加表数据");

此时出现了幻读现象

select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

因此可重复读会出现幻读现象

串行化

修改数据隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@transaction_isolation;

幻读

1，事务1开启查询数据

START TRANSACTION;
select * from test_db;

2，事务2开启插入数据

此时事务2被锁住

3，事务1提交

commit;

4，此时锁被释放，事务2插入数据执行成功

此时，在事务1开启的情况下，事务2无法进行修改和删除操作，脏读、不可重复读、幻读均被解决

实际应用场景

• ‌读未提交‌：适用于对数据一致性要求不高的场景，如某些实时分析系统。
• ‌读已提交‌：适用于对数据一致性要求较高的场景，如银行系统的查询操作。
• ‌可重复读‌：适用于需要确保数据一致性的场景，如银行系统的转账操作。
• ‌串行化‌：适用于对数据一致性要求极高且可以接受较低并发性能的场景。

springboot事务传播机制

在Spring Boot中，事务管理是一个非常重要的特性，它可以帮助你确保数据的一致性和完整性。Spring Boot使用Spring框架的声明式事务管理功能，这使得通过注解的方式来管理事务变得非常简单和直观。Spring事务的传播行为（Propagation behavior）是指在同一个方法的不同调用中，如何共享事务的上下文。

Spring事务的传播行为类型

Spring定义了多种事务传播行为，每种行为定义了事务应该如何开始，以及如何在不同的事务边界之间进行交互。以下是一些常见的事务传播行为：

1. REQUIRED（默认）：支持当前事务，如果当前没有事务，就新建一个事务。这是最常见的选择。

2. SUPPORTS：支持当前事务，如果当前没有事务，就以非事务方式执行。

3. MANDATORY：支持当前事务，如果当前没有事务，就抛出异常。

4. REQUIRES_NEW：总是新建一个新的事务，并且挂起当前事务（如果存在的话）。

5. NOT_SUPPORTED：不使用事务，如果当前存在事务，则挂起该事务。

6. NEVER：不使用事务，如果当前存在事务，则抛出异常。

7. NESTED：如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则其行为与REQUIRED类似。

DEFAULT(-1),READ_UNCOMMITTED(1),READ_COMMITTED(2),REPEATABLE_READ(4),SERIALIZABLE(8);

@Transactional(isolation=Isolation.DEFAULT) //以全局数据库隔离级别为主
@Transactional(isolation=Isolation.READ_UNCOMMITTED) //读未提交
@Transactional(isolation=Isolation.READ_COMMITTED) //读已提交
@Transactional(isolation=Isolation.REPEATABLE_READ) //可重复读
@Transactional(isolation=Isolation.SERIALIZABLE) //串行化

REQUIRED（默认）

情况一：

@Override@Transactional(propagation = Propagation.REQUIRED)public Result methodA(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("测试下");user_test.setPhone("131");user_test.setPassword("3");save(user_test);methodB(phone,session);int value = 1/0;
}public Result methodB(String phone, HttpSession session) {return Result.ok();}

这里methodA出现了异常导致数据库回滚

表中不会增加数据

此时把异常的语句改到未加入事务的方法中

情况二：

@Override@Transactional(propagation = Propagation.REQUIRED)public Result methodA(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("测试下");user_test.setPhone("131");user_test.setPassword("3");save(user_test);methodB(phone,session);
}public Result methodB(String phone, HttpSession session) {int value = 1/0;return Result.ok();}

这里methodB出现了异常，由于事务机制设置了REQUIRED（默认）：支持当前事务，如果当前没有事务，就新建一个事务。这是最常见的选择。methodB无事务，但被有事务的methodA调用了，所以methodB加入methodA事务，所以methodB和methodA处于同个事务

情况三：修改事务注解方法

@Overridepublic Result methodA(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("测试下");user_test.setPhone("131");user_test.setPassword("3");save(user_test);methodB(phone,session);
}@Transactional(propagation = Propagation.REQUIRED)
public Result methodB(String phone, HttpSession session) {int value = 1/0;return Result.ok();}

此时数据仍然会插入成功，methodA无事务而且无事务注解，所以无论是否有异常，都不会回滚，methodB判断无事务所以会开启事务，此时methodB抛出异常了，不会影响到methodA，所以数据会插入成功

这两个函数处于同个事务，无论在哪里异常了，都会回滚不插入数据

情况四：

@Override
@Transactional(propagation = Propagation.REQUIRED)public Result methodA(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("A");user_test.setPhone("133333333");user_test.setPassword("3");save(user_test);methodB(phone,session);
}@Transactional(propagation = Propagation.REQUIRED)
public Result methodB(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("B");user_test.setPhone("1333333331");user_test.setPassword("3");save(user_test);int value = 1/0;return Result.ok();}

此时数据不会插入，methodA无事务但有事务注解，会新建个事务，methodB有事务注解且判断当前有事务，会加入事务和methodB共用同个事务，此时methodB抛出异常了，两条数据均不会插入

这两个函数处于同个事务，无论在哪里异常了，都会回滚不插入数据

SUPPORTS

情况一：

@Override
@Transactional(propagation = Propagation.SUPPORTS)public Result methodA(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("A");user_test.setPhone("133333333");user_test.setPassword("3");save(user_test);methodB(phone,session);
}@Transactional(propagation = Propagation.SUPPORTS)
public Result methodB(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("B");user_test.setPhone("1333333331");user_test.setPassword("3");save(user_test);int value = 1/0;return Result.ok();}

methodA无事务但有事务注解，会已无事务来执行函数，methodB无事务但有事务注解，会已无事务来执行函数，则会插入两条数据

情况二：

@Override
@Transactional(propagation = Propagation.REQUIRED)public Result methodA(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("A");user_test.setPhone("133333333");user_test.setPassword("3");save(user_test);methodB(phone,session);
}@Transactional(propagation = Propagation.SUPPORTS)
public Result methodB(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("B");user_test.setPhone("1333333331");user_test.setPassword("3");save(user_test);int value = 1/0;return Result.ok();}

此时不会插入数据，methodA有事务注解判断无事物则新建事务，methodB判断当前有事务则加入当前事务，抛出异常了，两个方法都会回滚

MANDATORY

情况一：

@Override
@Transactional(propagation = Propagation.REQUIRED)public Result methodA(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("A");user_test.setPhone("133333333");user_test.setPassword("3");save(user_test);methodB(phone,session);
}@Transactional(propagation = Propagation.MANDATORY)
public Result methodB(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("B");user_test.setPhone("1333333331");user_test.setPassword("3");save(user_test);int value = 1/0;return Result.ok();}

此时不会插入数据，methodA有事务注解判断无事物则新建事务，methodB判断当前有事务则加入当前事务，抛出异常了，两个方法都会回滚

情况二：

@Overridepublic Result methodA(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("A");user_test.setPhone("133333333");user_test.setPassword("3");save(user_test);methodB(phone,session);int value = 1/0;}@Transactional(propagation = Propagation.MANDATORY)
public Result methodB(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("B");user_test.setPhone("1333333331");user_test.setPassword("3");save(user_test);return Result.ok();}

此时不会插入数据，methodA有事务注解判断无事物则新建事务，methodB判断当前有事务则加入当前事务，抛出异常了，两个方法都会回滚

情况三：

@Override
@Transactional(propagation = Propagation.SUPPORTS)public Result methodA(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("A");user_test.setPhone("133333333");user_test.setPassword("3");save(user_test);methodB(phone,session);int value = 1/0;}@Transactional(propagation = Propagation.MANDATORY)
public Result methodB(String phone, HttpSession session) {User user_test = new User();user_test.setUserName("B");user_test.setPhone("1333333331");user_test.setPassword("3");save(user_test);return Result.ok();}

此时都会插入数据，methodA有事务注解判断无事物则以无事务来执行，methodB有事务注解判断当前无事务

REQUIRES_NEW

总是创建一个新的事务，如果当前存在事务，则挂起当前事务

NOT_SUPPORTED

以非事务的方式执行操作，如果当前存在事务，则挂起当前事务。这种传播类型说明方法都是非事务的，不管外层有没有事务

NEVER

以非事务的方式执行操作，如果当前存在事务，则抛出异常

NESTED

如果当前存在事务，则在当前事务中创建一个新的嵌套事务；如果当前没有事务，则创建一个新的任务