工作日常-mysql触发器

最近碰到一个业务是，需要将我们某几个表的数据实时推送到第三方数据库。于是打算使用mysql触发器来进行实时增量同步。

本人使用的是mysql 5.7

1、开启federated引擎

进入mysql命令窗口，Federated是NO，说明没有安装

在my.ini(windows)中的mysqld下添加federated这一个属性就可开启.（linux系统则是在my.cnf文件下）

添加完成之后重启mysql服务

2.1、在建表语句中加入Federated

federated引擎

声明引擎：ENGINE=FEDERATED
连接属性:  CONNECTION='mysql://用户名:密码@ip地址:端口号/数据库名/表名';
连接属性：CONNECTION='mysql://root:root@123@192.168.10.2:3306/test/s_user';

在slave中创建表结构的时候加入federated引擎

CREATE TABLE `user` (
  `id` varchar(255) NOT NULL COMMENT 'id',
  `name` varchar(255) DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  `mobile` varchar(40) DEFAULT NULL COMMENT '手机号码',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `sex` int(1) DEFAULT NULL COMMENT '性别（0：男，1：女）',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='用户表'
-- 声明引擎         连接属性            
ENGINE =FEDERATED CONNECTION='mysql://root:root@123@192.168.10.2:3306/test/s_user';

然而在建表时我的建表语句报错了。

[Err] 1432 - Can't create federated table. The data source connection string mysql://root:root@123@192.168.10.2:3306/test/s_user' is not in the correct format

由于我的密码包含了@与格式冲突了。

2.2.1、使用 CREATE SERVER 创建 FEDERATED table

上面说到我connection方式格式不正确只能使用 Create Server 这种方法

格式：

CREATE SERVER fedlink
FOREIGN DATA WRAPPER mysql
-- 依次是远程数据库的用户名、密码、地址、端口、数据库名
OPTIONS (USER 'root', PASSWORD 'root@123',HOST '192.168.10.2', PORT 3306, DATABASE 'test');

创建create server 需要管理员权限
若写错了可以使用

-- 删除语句
DROP SERVER fedlink；

2.2.2、在建表语句中加入Federated

在建表连接中使用刚建好的create server

CREATE TABLE `s_user` (
  `id` varchar(255) NOT NULL COMMENT 'id',
  `name` varchar(255) DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) DEFAULT NULL COMMENT '密码',
  `mobile` varchar(40) DEFAULT NULL COMMENT '手机号码',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  `sex` int(1) DEFAULT NULL COMMENT '性别（0：男，1：女）',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='用户表'
-- 声明引擎         连接属性    Federated/slave表
ENGINE=FEDERATED CONNECTION = 'fedlink/s_user';

3、创建触发器

语法格式如下：

CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

NEW与OLD关键字详解

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

① 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

② 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；

③ 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

3.1、新增触发器

-- 插入触发器
drop trigger if exists user_insert_trigger;
delimiter $
create trigger user_insert_trigger after insert 
on `user` for each row 
begin
	insert into s_user(`id`,`name`,`password`,`mobile`,`email`,`sex`)
    values (new.id, new.name, new.password, new.mobile, new.email, new.sex);
end $;
delimiter ;

3.2、新增触发器

-- 修改触发器
drop trigger if exists user_update_trigger;
delimiter $
create trigger user_update_trigger after update
on `user` for each row 
begin
	update s_user set `name`=new.name, `password`=new.password,`mobile`=new.mobile,`email`=new.email,`sex`=new.sex where `id`=old.id;
end $;
delimiter ;

3.3、删除触发器

-- 删除触发器
drop trigger if exists user_delete_trigger;
delimiter $
create trigger user_delete_trigger after delete
on `user` for each row 
begin
	delete from s_user where `id` = old.id;
end $;
delimiter ;