MySQL入门指南5约束，索引，事务

用于定义主表与从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或者UNIQUE约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或者是为NULL。

-- 基本用法：

FOREIGN KEY (本表字段名)

REFERENCES 主表名（主表的主键名或UNIQUE字段名）

使用细节：

1. 外键指向的表的字段，要求是PRIMARY KEY或者是UNIQUE。

2. 表的存储引擎必须是INDODB ，这样的表才支持外键。

3. 外键字段的类型要与主键字段的类型保持一致（长度可不同）。

4. 外键字段的值，必须在主键字段中出现过，或者为NULL（未指定NOT NULL时）。

5. 一旦建立主外键关系，数据就不能随意删除。必须先删除从表才能再删除主表。

代码演示：

-- FOREIGN KEY 测试
-- 主表班级表
CREATE TABLE test9(
id INT PRIMARY KEY,
nam VARCHAR(23),
address VARCHAR(23));
INSERT INTO test9
VALUES(100,'java','北京'),(200,'web','上海');
-- 从表学生表
CREATE TABLE test10(
id INT PRIMARY KEY,
`name` VARCHAR(23) NOT NULL DEFAULT '',
class_id INT,
-- 此处指向主键或UNIQUE
FOREIGN KEY (class_id) REFERENCES test9(id));
INSERT INTO test10
VALUES(1,'tom',100),(2,'jack',200);
INSERT INTO test10
VALUES(3,'milan',300);
INSERT INTO test10
VALUES(4,'erson',NULL);
SELECT * FROM test10;
SELECT * FROM test9;
CREATE TABLE test11(
id INT PRIMARY KEY,
`name` VARCHAR(23) NOT NULL DEFAULT '',
class_id INT,
-- 此处指向的不是主键因此执行不成功
FOREIGN KEY (`name`) REFERENCES test9(`name`));
-- 删除时需要先删除从表
DROP TABLE test10;
-- 才能把删除主表
DROP TABLE test9;

学新通

5. CHECK

用于强制行数据必须满足的条件，若未满足条件则会提示出错。

MySQL5.7目前还不支持CHECK，只做语法校验。

在ORACLE和SQL SERVER 中均支持CHECK。

在MySQL实现CHECK的功能，一般时在程序中控制，或者通过触发器完成。

-- 基本用法:

字段字段类型 CHECK (检查条件)

代码演示：

-- CHECK 测试
CREATE TABLE test8(
id INT UNIQUE NOT NULL DEFAULT 0,
`name` VARCHAR(23),
sex CHAR(1) CHECK(sex IN('男','女')),
age INT CHECK(age>18&&age<60));
INSERT INTO test8
VALUES(1,'Lihua','中',78);

二、索引

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调sql，查询速度就可能提高千倍百倍。

1.索引的原理

        MySQL表进行查询时在没有索引的情况下，是进行全表扫描来查找。

        如果使用了索引，则会形成一个索引的数据结构，比如二叉树。这样在查询时，效率就会大大提升。

但在查询效率提升的同时，也需要付出一些代价：

比如：

                1. 会占用更多的磁盘空间

2. 会影响 dml ( update , delete , insert ) 语句的效率（因为每次在执行这些操作时都要重建索引）

2. 索引的类型

a. 主键索引，主键自动的为主索引（类型 PRIMARY KEY）

b. 唯一索引（UNIQUE）

c. 全文索引（FULLTEXT）[适用于MyISAM存储引擎]

一般开发，不适应MySQL自带的全文索引，而是使用：全文搜索Solr和ElasticSearch(ES)

3. 索引的使用

4. 适合使用索引的列

代码演示：

-- 首先我们创建了一个有八百万条数据的emp表,代码省略
-- 查询索引（三种方式）
-- 方式一
SHOW INDEX FROM emp;
SHOW INDEXES FROM emp;
-- 方式二
SHOW KEYS FROM emp;
-- 方式三
DESC emp;
-- 测试查询速度（未设置索引时）
SELECT * FROM emp
WHERE ename = 'tZOXxv'; -- 此时用时3秒
SELECT * FROM emp
WHERE empno = 100676; -- 此时用时3秒
-- 添加索引
-- 1.添加普通索引INDEX
-- 方式一
CREATE INDEX index_empno ON emp(empno);
-- 方式二
ALTER TABLE emp ADD INDEX index_empno(empno);
-- 测试查询速度（添加索引列）
SELECT * FROM emp
WHERE empno = 100676; -- 此时用时0.008秒
-- 测试查询速度（未添加索引列）
SELECT * FROM emp
WHERE ename = 'tZOXxv'; -- 此时用时3秒
-- 删除索引方式一
DROP INDEX index_empno ON emp; -- 用于删除索引
-- 删除索引方式二
ALTER TABLE emp DROP INDEX index_empno; -- 用于删除索引
-- 2.添加唯一索引（UNIQUE）
CREATE INDEX index_empno ON emp(empno);
ALTER TABLE emp ADD UNIQUE INDEX index_empno(empno);
-- 3.添加主键索引（比较特殊）
ALTER TABLE emp ADD PRIMARY KEY (empno);
-- 删除主键索引（比较特殊）
ALTER TABLE emp DROP PRIMARY KEY;

三、事务

1. 事务介绍

事务用于保证数据的一致性，它由一组相关的DML语句(INSERT ,DELETE ,UPDATE )组成，该组的DML语句要么全部成功，要么全部失败。如：转账就要用事务来处理，以保证数据的一致性。

当执行事务操作时（DML语句），MySQL会在表上加锁，防止其他用户更改表的数据。这对用户来讲非常重要。

MySQL数据库控制台事务的几个重要操作

1. start transaction -- 开始一个事务

2. savepoint 保存点名 -- 设置保存点

3. rollback to 保存点名 -- 回退事务

4. rollback -- 回退全部事务

5. commit -- 提交事务，所有的操作生效，不能回退

事务操作代码演示：

-- 事务测试
-- 开始事务
START TRANSACTION;
-- 执行DML语句
INSERT INTO test
VALUES(5,'乐乐',18);
-- 设置保存点
SAVEPOINT a;
-- 执行DML语句
UPDATE test
SET age = 23
WHERE NAME ='浩浩';
-- 设置保存点
SAVEPOINT b;
-- 执行DML语句
DELETE FROM test
WHERE id=3;
-- 设置保存点
SAVEPOINT c;
-- 回退到指定保存点
ROLLBACK TO a;
-- 回退全部事务：回到事务开始时的状态
ROLLBACK;
-- 提交事务，所有操作生效，保存点失效
COMMIT;

回退事务：

在介绍回退事务前，先介绍一下保存点（savepoint）。保存点是事务中的点，用于取消部分事务，当结束事务时（commit），会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的状态。

提交事务：

使用 commit 语句可以提交事务。当执行了 commit 语句后，会确认事务的变化、结束事务、删除保存点、释放锁、数据生效。当使用 commit 语句结束事务后，其它会话（其他连接）将可以查看到事务变化的新数据（所有数据就此生效）。

学新通

事务细节讨论：

1. 如果不开始事务，默认情况下，DML操作是自动提交的，不能回滚。

2. 如果开始一个事务，但没有创建保存点，可以执行 rollback，默认就是回退到你事务你开始的状态。

3. 可以在事务中创建多个保存点（提交事务前）。

4. 可以选择回退到你设置的保存点（提交事务前且未回到比该保存点更早的保存点前）

5. MySQL的事务机制需要INNODB存储引擎才可以使用。

2. 事务隔离级别

多个连接开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个连接在获取数据时的准确性。

如果不考虑隔离性，可能会引发如下问题：

1. 脏读：一个事务读取到另一个事务尚未提交的改变（DML操作），此时发生脏读。

2. 不可重复读：一个事务多次查询操作，由于其他事务提交所做的修改或删除，每次查询到不同的结果，此时发生不可重复读。

3. 幻读：一个事务多次查询操作，由于其他事务提交所作的插入操作，每次查询到不同的结果，此时发生幻读。

在多连接事务操作中，一般我们期望的是：当前事务只能读取到当前事务开始时的事务状态，而不能读取到其他操作产生的影响。

四种隔离级别

概念：MySQL隔离级别定义了事务与事务之间的隔离程度。

MySQL隔离级别 脏读 不可重复读 幻读 加锁读

读未提交（Read uncommitted） 是是是 不加锁

读已提交（Read committed） 否是是 不加锁

可重复读（Repeatable read） 否否否 不加锁

可串行化（Serializable） 否否否加锁

设置事务隔离级别

1. 查看当前会话隔离级别
SELECT @@tx_isolation;
2. 查看系统当前隔离级别
SELECT @@global.tx_isolation;
3. 设置当前对话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL 指定隔离级别;
4. 设置系统当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 指定隔离级别;
5. 全局修改，修改 my.ini 配置文件，在最后加上
transaction-isolation = 指定隔离级别
6. MySQL默认的事务隔离级别是Repeatable read，一般情况下，没有特殊要求，没有必要修改，因为该级别可以满足绝大部分项目需求。

温馨提示：可以试着多开几个mysql对话然后设置成不同的隔离级别，去做一些DML操作，看看四种隔离级别的差别，体会体会脏读，不可重复读，幻读的发生造成的困扰，理解理解可串行化（Serializable）加锁操作的利与弊（设置为可串行化时，一个事务未提交，另一个事务将无法开始）。

3. 事务ACID

1. 原子性（Atomicity）

原子性是指事务时一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2. 一致性（Consistency）

事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

3. 隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

4. 持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

四、最后的话

✨ 原创不易，还希望各位大佬支持一下

👍  点赞，你的认可是我创作的动力！

⭐️  收藏，你的青睐是我努力的方向！

✏️  评论，你的意见是我进步的财富！

这篇好文章是转载于：学新通技术网

MySQL入门指南5约束，索引，事务

一、约束

1. PRINARY KEY

2. NOT NULL

3. UNIQUE

4. FOREIGN KEY

5. CHECK

二、索引

三、事务

1. 事务介绍

2. 事务隔离级别

3. 事务ACID

四、最后的话

photoshop保存的图片太大微信发不了怎么办

photoshop扩展功能面板显示灰色怎么办

word里面弄一个表格后上面的标题会跑到下面怎么办

《学习通》视频自动暂停处理方法

TikTok加速器哪个好免费的TK加速器推荐

Android 11 保存文件到外部存储，并分享文件

excel下划线不显示怎么办

微信公众号没有声音提示怎么办

excel图片置于文字下方的方法

微信运动停用后别人还能看到步数吗

MySQL隔离级别	脏读	不可重复读	幻读	加锁读
读未提交（Read uncommitted）	是	是	是	不加锁
读已提交（Read committed）	否	是	是	不加锁
可重复读（Repeatable read）	否	否	否	不加锁
可串行化（Serializable）	否	否	否	加锁