快捷導(dǎo)航

MySQL約束和事務(wù)知識(shí)點(diǎn)詳細(xì)歸納

更新時(shí)間：2023年04月12日 09:28:53 作者：小黎的培培筆錄

在關(guān)系型數(shù)據(jù)庫(kù)中,事務(wù)的重要性不言而喻,只要對(duì)數(shù)據(jù)庫(kù)稍有了解的人都知道事務(wù),下面這篇文章主要給大家介紹了關(guān)于MySQL約束和事務(wù)知識(shí)點(diǎn)歸納的相關(guān)資料,文中通過(guò)實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下

一、MySQL 約束

? 基本介紹

約束用于確保數(shù)據(jù)庫(kù)的數(shù)據(jù)滿足特定的商業(yè)規(guī)則。在mysql中，約束包括: not null，unique, primary key, foreign key, 和 check 五種。

1、主鍵 primary key

? 基本介紹

用于唯一的標(biāo)示表行的數(shù)據(jù),當(dāng)定義主鍵約束后，該列不能重復(fù)

? 細(xì)節(jié)

? primary key不能重復(fù)而且不能為null。

? 一張表最多只能有一個(gè)主鍵，但可以是復(fù)合主鍵。

? 主鍵的指定方式有兩種：

1、直接在字段名后指定 : 字段名 primakry key

2、在表定義最后寫 primary key(列名)
? 使用desc 表名, 可以看到primary key的情況

? 在實(shí)際開發(fā)中，每個(gè)表往往都會(huì)設(shè)計(jì)一個(gè)主鍵。

2、not null

如果在列上定義了not null，那么當(dāng)插入數(shù)據(jù)時(shí)，必須為列提供數(shù)據(jù)。

3、unique

當(dāng)定義了唯一約束后，該列值是不能重復(fù)的。

CREATE TABLE 表名(
     id INT UNIQUE , -- 表示 id 列是不可以重復(fù)的.
    `name` VARCHAR(32) ,
     email VARCHAR(32)
   );

? 細(xì)節(jié)

如果沒(méi)有指定 not null , 則 unique 字段可以有多個(gè) null 。如果一個(gè)列(字段)，是 unique not null 使用效果類似 primary key
一張表可以有多個(gè) unique 字段

4、外鍵 foreign key

? 基本介紹

用于定義主表和從表之間的關(guān)系:外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束，當(dāng)定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或是為null。

? 細(xì)節(jié)

外鍵指向的表的字段，要求是primary key 或者是unique
表的類型是innodb,這樣的表才支持外鍵
外鍵字段的類型要和主鍵字段的類型一致(長(zhǎng)度可以不同)
外鍵字段的值，必須在主鍵字段中出現(xiàn)過(guò), 或者為null前提是外健字段允許為null
一旦建立主外鍵的關(guān)系, 數(shù)據(jù)不能隨意刪除了

5、check

? 基本介紹

用于強(qiáng)制行數(shù)據(jù)必須滿足的條件,假定在數(shù)值列上定義了check約束并要求數(shù)列值在1000~2000之間，如果不再1000~2000之間就會(huì)提示出錯(cuò)。
oracle 和 sql.server 均支持check，但是mysq15.7目前還不支持chck，只做語(yǔ)法校驗(yàn)，但不會(huì)生效。

? 基本使用

CREATE TABLE 表名 (
    id INT PRIMARY KEY,
    `name` VARCHAR(32) ,
 
    sex VARCHAR(6) CHECK (sex IN('man','woman')),
 
    sal DOUBLE CHECK ( sal > 1000 AND sal < 2000)
);

二、MySQL應(yīng)用

1、自增長(zhǎng)

?基本介紹

? 細(xì)節(jié)

一般來(lái)說(shuō)自增長(zhǎng)是和 primary key 配合使用的
自增長(zhǎng)也可以單獨(dú)使用[但是需要配合一個(gè)unique]
自增長(zhǎng)修飾的字段為整數(shù)型的(雖然小數(shù)也可以但是非常非常少這樣使用）
自增長(zhǎng)默認(rèn)從1開始, 你也可以通過(guò)如下命令修改alter table 表名 auto increment=新的開始值
如果你添加數(shù)據(jù)時(shí)，給自增長(zhǎng)字段(列)指定的有值，則以指定的值為準(zhǔn)，如果指定了自增長(zhǎng)，一般來(lái)說(shuō).，就按照自增長(zhǎng)的規(guī)則來(lái)添加數(shù)據(jù)

? 自增長(zhǎng)使用

CREATE TABLE 表名 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(32) NOT NULL DEFAULT '',
    `name` VARCHAR(32) NOT NULL DEFAULT '');

2、MySQL索引

? 基本介紹

說(shuō)起提高數(shù)據(jù)庫(kù)性能，索引是最物美價(jià)廉的東西了。不用加內(nèi)存, 不用改程序, 不用調(diào)sql, 查詢速度就可能提高百倍干倍。

? 索引的原理

沒(méi)有索引為什么會(huì)慢?因?yàn)槿頀呙?/li>
使用索引為什么會(huì)快?形成一個(gè)索引的數(shù)據(jù)結(jié)構(gòu)，比如二叉樹
索引的代價(jià)：

① 磁盤占用

② 對(duì)dml(update delete insert)語(yǔ)句的效率影響

? 索引的類型

主鍵索引，主鍵自動(dòng)的為主索引(類型Primary key)
唯一索引(UNIQUE)
普通索引(INDEX)
全文索引(FULLTEXT) [適用于MyISAM]
一般開發(fā), 不使用mysql自帶的全文索引，而是使用:全文搜索Solr和 ElasticSearch(ES)

create table 表(
    id int primary key, --主鍵，同時(shí)也是索引，稱為主鍵索引。
    name varchar(32));
 
create table 表(
    id int unique-- id是唯一的，同時(shí)也是索引，稱為unique索引
    );

? 索引的使用

? 如果使用索引

? 較頻繁的作為查詢條件字段應(yīng)該創(chuàng)建索引

            select * from emp where empno=1

? 唯一性太差的字段不適合單獨(dú)創(chuàng)建索引，即使頻繁作為查詢條件

            select * from emp where sex='男'

? 更新非常頻繁的字段不適合創(chuàng)建索引

            select * from emp where logincount= 1

? 不會(huì)出現(xiàn)在WHERE子句中字段不該創(chuàng)建索引

3、MySQL事務(wù)

? 基本介紹

事務(wù)用于保證數(shù)據(jù)的一致性,它由一組相關(guān)的dml語(yǔ)句組成，該組的dml活句要么全部成功, 要么全部失敗。如:轉(zhuǎn)賬就要用事務(wù)來(lái)處理，用以保證數(shù)據(jù)的一致性。

? 事務(wù)和鎖

當(dāng)執(zhí)行事務(wù)操作時(shí)(dml語(yǔ)句) ,mysql會(huì)在表上加鎖,防止其它用戶改表的數(shù)據(jù)這對(duì)用戶來(lái)講是非常重要的。

-- 1. 創(chuàng)建一張測(cè)試表
CREATE TABLE t27 ( 
    id INT,
    `name` VARCHAR(32));
 
-- 2. 開始事務(wù)
START TRANSACTION
 
-- 3. 設(shè)置保存點(diǎn)
SAVEPOINT a
-- 執(zhí)行 dml 操作
INSERT INTO t27 VALUES(100, 'tom');
 
SAVEPOINT b
-- 執(zhí)行 dml 操作
INSERT INTO t27 VALUES(200, 'jack');???????
 
-- 回退到 b
ROLLBACK TO b
 
-- 繼續(xù)回退 a
ROLLBACK TO a
 
-- 如果這樣, 表示直接回退到事務(wù)開始的狀態(tài).
ROLLBACK
COMMIT

? 回退事務(wù)

在介紹回退事務(wù)前，先介紹一下保存點(diǎn)(savepoint).保存點(diǎn)是事務(wù)中的點(diǎn)，用于取消部分事務(wù)，當(dāng)結(jié)束事務(wù)時(shí)(commit)，會(huì)自動(dòng)的刪除該事務(wù)所定義的所有保存點(diǎn)當(dāng)執(zhí)行回退事務(wù)時(shí)，通過(guò)指定保存點(diǎn)可以回退到指定的點(diǎn)。

? 提交事務(wù)

使用commit語(yǔ)包可以提交事務(wù).當(dāng)執(zhí)行了commit語(yǔ)句子后,會(huì)確認(rèn)事務(wù)的變化、結(jié)束事務(wù)、刪除保存點(diǎn)、釋放鎖，數(shù)據(jù)生效。當(dāng)使用commit語(yǔ)句結(jié)束事務(wù)子后，其它會(huì)話[其他連接]將可以查看到事務(wù)變化后的新數(shù)據(jù)[所有數(shù)據(jù)就正式生效]

?事務(wù)細(xì)節(jié)

如果不開始事務(wù)，默認(rèn)情況下，dml操作是自動(dòng)提交的, 不能回滾
如果開始一個(gè)事務(wù)，你沒(méi)有創(chuàng)建保存點(diǎn).你可以執(zhí)行rollback,默認(rèn)就是回退到你事務(wù)開始的狀態(tài)
你也可以在這個(gè)事務(wù)中(還沒(méi)有提交時(shí)),創(chuàng)建多個(gè)保存點(diǎn).比如: savepoint aaa; 執(zhí)行dml, savepoint bbb;
你可以在事務(wù)沒(méi)有提交前，選擇回退到哪個(gè)保存點(diǎn)
mysql的事務(wù)機(jī)制需要innodb的存儲(chǔ)引擎才可以使用,myisam不好使
開始一個(gè)事務(wù) start transaction, set autocommit=off

? 基本介紹

多個(gè)連接開啟各自事務(wù)操作數(shù)據(jù)庫(kù)中數(shù)據(jù)時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)要負(fù)責(zé)隔離操作，以保證各個(gè)連接在獲取數(shù)據(jù)時(shí)的準(zhǔn)確性。

如果不考慮隔離性，可能會(huì)引發(fā)如下問(wèn)題: ① 臟讀；② 不可重復(fù)讀； ③ 幻讀

? 解釋

臟讀(dirty read) : 當(dāng)一個(gè)事務(wù)讀取另一個(gè)事務(wù)尚未提交的改變(update,insert,delete)時(shí)，產(chǎn)生臟讀
不可重復(fù)讀(nonrepeatable read) : 同一查詢?cè)谕皇聞?wù)中多次進(jìn)行，由于其他提交事務(wù)所做的修改或刪除, 每次返回不同的結(jié)果集, 此時(shí)發(fā)生不可重復(fù)讀。

幻讀(phantom read) : 同一查詢?cè)谕皇聞?wù)中多次進(jìn)行，由于其他提交事務(wù)所做的插入操作, 每次返回不同的結(jié)果集，此時(shí)發(fā)生幻讀。

? 隔離級(jí)別

概念 : MySQL隔離級(jí)別定義了事務(wù)與事務(wù)之間的隔離程度。

? 設(shè)置隔離級(jí)別

? 查看當(dāng)前會(huì)話隔離級(jí)別

        set transaction.sql select @@tx isolation

? 查看系統(tǒng)當(dāng)前隔離級(jí)別

        select @@global.tx isolation

? 設(shè)置當(dāng)前會(huì)話隔離級(jí)別

        set session transaction isolation level repeatable read

? 設(shè)置系統(tǒng)當(dāng)前隔離級(jí)別

        set global transaction isolation leve repeatable read:

? mysql默認(rèn)的事務(wù)隔離級(jí)別是 repeatable read一般情況下，沒(méi)有特殊要求，沒(méi)有必要修改(因?yàn)樵摷?jí)別可以滿足絕大部分項(xiàng)目需求)

? 事務(wù) ACID

? 原子性(Atomicity)

原子性是指事務(wù)是一個(gè)不可分割的工作單位，事務(wù)中的操作要么都發(fā)生，要么都不發(fā)生

? 致性(Consistency)

事務(wù)必須使數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)變換到另外一個(gè)一致性狀態(tài)

? 隔離性 (lsolation)

事務(wù)的隔離性是多個(gè)用戶#發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，數(shù)據(jù)庫(kù)為每一個(gè)用戶開啟的事務(wù)，不能被其他事務(wù)的操作數(shù)據(jù)所干擾，多個(gè)并發(fā)事務(wù)之間要相互隔離。

? 持久性(Durability)

持久性是指一個(gè)事務(wù)一#被提交，它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就是永久性的，接下來(lái)即使數(shù)據(jù)庫(kù)發(fā)生故障也不應(yīng)該對(duì)其有任何影響

5、表類型和存儲(chǔ)引擎

? 基本介紹

MySQL的表類型由存儲(chǔ)引擎(Storage Engines) 決定，主要包括MylSAM，innoDB、 Memory等。
MySQL 數(shù)據(jù)表主要支持六種類型，分別是:CSV、 Memory，ARCHIVE，MRG MYISAM、 MYISAM，InnoBDB。
這六種又分為兩類, 一類是”事務(wù)安全型”(transaction-safe)，比如：InnoDB。其余都屬于第二類，稱為”非事務(wù)安全型”(non-transaction-safe)[mysiam 和 memory]。

? 特點(diǎn)

? 細(xì)節(jié)

MylSAM不支持事務(wù)、也不支持外鍵，但其訪問(wèn)速度快，對(duì)事務(wù)完整性沒(méi)有要求
InnoDB存儲(chǔ)引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是比MyISAM存儲(chǔ)引擎，InnoDB寫的處理效率差一些并且會(huì)占用更多的磁盤空間以保留數(shù)據(jù)和索引。
MEMORY存儲(chǔ)引擎使用存在內(nèi)存中的內(nèi)容來(lái)創(chuàng)建表。每個(gè)MEMORY表只實(shí)際對(duì)應(yīng)個(gè)個(gè)磁盤文件。 MEMORY類型的表訪問(wèn)非常得快, 因?yàn)樗臄?shù)據(jù)是放在內(nèi)存中的,并且默認(rèn)使用HASH索引。但是一旦MySQL服務(wù)關(guān)閉，表中的數(shù)據(jù)就會(huì)丟失掉，表的結(jié)構(gòu)還在

? 如何選擇存儲(chǔ)引擎

如果你的應(yīng)用不需要事務(wù), 處理的只是基本的CRUD操作，那么MyISAM是不二選擇,速度快
如果需要支持事務(wù)，選擇InnoDB。Memory 存儲(chǔ)引擎就是將數(shù)據(jù)存儲(chǔ)在內(nèi)存中，由于沒(méi)有磁盤I/0的等待。速度極快。但由于是內(nèi)存存儲(chǔ)引擎，所做的任何修改在服務(wù)器重啟后都將消失。(經(jīng)典用法，用戶的在線狀態(tài))

? 修改存儲(chǔ)引擎

6、視圖

? 基本介紹

視圖是一個(gè)虛擬表，其內(nèi)容由查詢定義。同真實(shí)的表一樣，視圖包含列，其數(shù)據(jù)來(lái)自對(duì)應(yīng)的真實(shí)表(基表)

? 基本使用

create view視圖名 as select語(yǔ)句
alter view視圖名 as select語(yǔ)句 --更新成新的視圖
SHOW CREATE VIEW 視圖名
drop view 視圖名1,視圖名2

? 細(xì)節(jié)

創(chuàng)建視圖后, 到數(shù)據(jù)庫(kù)去看, 對(duì)應(yīng)視圖只有一個(gè)視圖結(jié)構(gòu)文件(形式 : 視圖名.frm)視圖的數(shù)據(jù)變化會(huì)影響到基表，基表的數(shù)據(jù)變化也會(huì)影響到視圖[insert update delete]
視圖中可以再使用視圖，數(shù)據(jù)仍然來(lái)自基表..

? 視圖最佳實(shí)踐

安全。一些數(shù)據(jù)表有著重要的信息。有些字段是保密的，不能讓用戶直接看到。這時(shí)就可以創(chuàng)建一個(gè)視圖，在這張視圖中只保留一部分字段。這樣，用戶就可以查自己需要的字段，不能查看保密的字段。
性能。關(guān)系數(shù)據(jù)庫(kù)的數(shù)據(jù)常常會(huì)分表存儲(chǔ)，使用外鍵建立這些表的之間關(guān)系。這時(shí)，數(shù)據(jù)庫(kù)查詢通常會(huì)用到連接(UOIN)。這樣做不但麻煩，效率相對(duì)也比較低。如果建立一個(gè)視圖，將相關(guān)的表和字段組合在一越，就可以避免使用JOIN查詢數(shù)據(jù)。
靈活。如果系統(tǒng)中有一張舊的表，這張表由于設(shè)計(jì)的問(wèn)題，即將被廢棄。然而，很多應(yīng)用都是基于這張表，不易修改。這時(shí)就可以建立一張視圖，視圖中的數(shù)據(jù)直接映射到新建的表。這樣，就可以少做很多改動(dòng)，也達(dá)到了升級(jí)數(shù)據(jù)表的目的。