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;" 之间的东西是独立的. 所以, 如果将触发器的脚本和视图的脚本放在一个脚本中去执行, 是能够执行的.

如果是单个执行, 就不需要加那个了.