Mysql - 觸發器/視圖

觸發器在之前的項目中, 應用的著實不多, 沒有辦法的時候, 才會去用這個. 因為這個東西在後期並不怎麼好維護, 也容易造成紊亂.

我最近的項目中, 由於資料庫設計(別人設計的)原因, 導致一些最簡單功能, 查詢起來, 都很麻煩和複雜. 牽涉表非常多, 表與表之間又互有部分關係. 我想說, 這是我見過的最糟糕的資料庫設計了. 最後沒辦法, 公司架構師給了觸發器的解決方案.

一、觸發器

在項目中, 我新建了一張關係表, 把一些必要的, 有效的關係, 通過觸發器的方式, 更新到一張表中, 併在這張表裡面建了索引. 然後讀取數據的時候, 就通過連接這張關係表, 去得到最後的有效數據. 看上去, 有點類似於讀寫分離的趕腳, 不過這並不是多台資料庫伺服器間的.

由於工作的關係, 我不能使用項目中的資料庫來做記錄, 那就自己搞幾個表來玩玩吧. 先建三張表

CREATE TABLE `tch_teacher` (
    `Id` INT (11) NOT NULL AUTO_INCREMENT,
    `Sex` SMALLINT (6) DEFAULT NULL,
    `BId` VARCHAR (36) DEFAULT NULL,
    `No` VARCHAR (20) DEFAULT NULL,
    `Name` VARCHAR (30) DEFAULT NULL,
    `IsDeleted` bit (1) DEFAULT b '0' PRIMARY KEY (`Id`),
    KEY `Index_Sex` (`Sex`) USING BTREE,
    KEY `Index_BId` (`BId`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 21 DEFAULT CHARSET = latin1;

CREATE TABLE `tch_contact` (
    `Id` INT (11) NOT NULL AUTO_INCREMENT,
    `TId` INT (11) DEFAULT NULL,
    `QQ` VARCHAR (15) DEFAULT NULL,
    `Weixin` VARCHAR (50) DEFAULT NULL,
    `Phone` VARCHAR (15) DEFAULT NULL,
    PRIMARY KEY (`Id`),
    KEY `Index_TId` (`TId`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 11 DEFAULT CHARSET = latin1 COMMENT = '聯繫方式表';

CREATE TABLE tch_all (
    Id INT NOT NULL,
    Sex SMALLINT,
    BId VARCHAR (36),
    NO VARCHAR (20),
    NAME VARCHAR (30),
    QQ VARCHAR (15),
    Weixin VARCHAR (50),
    Phone VARCHAR (15)
) COMMENT '完整表';

我這裡就通過觸發器的方式, 來維護tch_all這張表. 例子不好, 主要是介紹功能, 見諒.

delimiter $
drop trigger if EXISTS tg_insert_all;
create trigger tg_insert_all after insert on tch_teacher for each ROW
BEGIN
　　insert into tch_all(Sex,BId,NO,NAME) values(new.sex, new.bid, new.no, new.name);
end $
delimiter;

1. 語法

create trigger 觸發器名 before/after insert/update/delete on 表名 for each row

begin

end

1. 觸發時機 before/after

這裡的觸發器, 觸發的時機是在tch_teacher表數據插入之後. 也就是說, tch_teacher插入成功了之後, 才會向tch_all表插入數據. 這裡有一個點需要註意下. 在tch_teacher插入成功後, 向tch_all插入的時候報錯, 那麼tch_teacher的新插數據就回被回滾.

有插入後觸發, 自然就有插入前觸發, 只需要將after改成before即可. 

before觸發, 則會先想tch_all插入數據, 再向tch_teacher插入數據. 插入過程中, 不管哪一步失敗, 都會回滾數據. 所以不需要擔心, 觸發不成功的情況下, 會不會造成冗餘或者錯誤數據.

2. 觸發方式 insert/update/delete

觸發方式, 有插入/修改/刪除 時觸發. 例子中我只寫了插入觸發別的兩種方式的使用方法是和這個一樣的.

3. 原數據引用  old/new

這裡有一個問題, 我修改了數據, 那麼我怎麼引用他們呢? 既然修改了數據, 那肯定是有 修改前數據和修改後新數據 的引用的, 

這裡直接使用 old 來指向修改前的數據, new 指向修改後的數據. 這裡的指向, 是指向的tch_teacher中的數據, 不是tch_all的數據.

4. 註

例子中, 我只用了一句話, 例子嘛, 簡單就好. 其實在實際使用過程之中, 不會是這麼簡單的. 舉個例子說吧.

很多時候, 由於數據重要性, 不會直接刪除數據. 而是選擇更新數據狀態來表示其已不再使用. 這裡就用 isdeleted來表示, 0表示能用, 1表示不再使用.

當我更新tch_teacher的isdeleted的值為1的時候, 觸發修改觸發器, 在觸發器中, 我就需要判斷 new.isdeleted的值, 從而選擇是否刪除關係表中的數據.

delimiter $
DROP TRIGGER IF EXISTS tg_update_all ; 
CREATE TRIGGER tg_update_all AFTER UPDATE ON tch_teacher FOR EACH ROW
BEGIN
IF new.isdeleted = 1 THEN
    DELETE FROM tch_all WHERE id = old.id ;
ELSE
    UPDATE tch_all set sex=new.sex, bid=new.bid, NO=new.NO, NAME=new.NAME where id = old.id;
END IF ;
END$
delimiter ;

到這裡, 我發現好像沒有繼續這個例子的必要了, 好吧, 那就這樣了.

二、視圖

視圖的作用: 簡化查詢, 提升查詢速度.

老版本的mysql, 並不支持視圖子查詢, 但是新版本的mysql, 已經能支持了.

就上面這個例子而言, 其實也可以使用視圖的方式, 去解決複雜的邏輯.

單是就查詢性能上來說, 我覺得還是觸發器的方式快一些. 畢竟觸發器維護了一張新表, 而且新表能夠建索引來提升查詢速度. 就是維護起來比較麻煩.

delimiter $
drop view if EXISTS v_all; -- 刪除視圖
create view v_all AS    -- 新建視圖
select tch_teacher.*,tch_contact.QQ,tch_contact.Weixin,tch_contact.Phone from tch_teacher 
left join tch_contact on tch_teacher.Id=tch_contact.TId where tch_teacher.IsDeleted=0 $
delimiter;

這裡我使用到了一個東西:delimiter, 這個在mysql中, 是用來分割的. 

"delimiter $" 到 "$ delimiter;" 之間的東西是獨立的. 所以, 如果將觸發器的腳本和視圖的腳本放在一個腳本中去執行, 是能夠執行的.

如果是單個執行, 就不需要加那個了.