mysql 触发自定义函数 mysql触发器表自动更新

0.前言

背景：一个业务需要数据库的两个表的某些字段同步。

需求：数据库两个表之间实现同步的变化。

本文：同一服务器，同一个数据库，从tableA表同步部分数据到tableB表。

同一个服务器，不同数据库之间同步tableA与tableB，点这里。

不同服务器，不同数据库之间同步tableA与tableB，点这里。

1.触发器的基本结构

首先，触发器叫TRIGGER。基本结构如下：

CREATEorREPLACETRIGGER 名字

时间（after|before）

动作（INSERT|UPDATE|DELETE）

onA

for each row

begin

 

sql语句，表B;

 

end;

2.插入操作（insert）

首先创建数据表，默认两张表是同一个结构，下面还有不同结构的。

-- 创建表A(触发表)

DROPTABLEIFEXISTS`tableA`;
CREATETABLE`tableA` (
`id`40) CHARACTERSETCOLLATENOTNULL,
`code`18) CHARACTERSETCOLLATENOTNULL,
`name`50) CHARACTERSETCOLLATENULLDEFAULTNULL,
`address`128) CHARACTERSETCOLLATENULLDEFAULTNULL,
`center`1) NULLDEFAULTNULL,
`fax`20) CHARACTERSETCOLLATENULLDEFAULT'',
`phone`45) CHARACTERSETCOLLATENULLDEFAULTNULL,
PRIMARYKEY`id`) USINGBTREE,
UNIQUEINDEX`pen_only_one`(`code`) USINGBTREE
) ENGINECHARACTERSETCOLLATE = utf8_general_ci ROW_FORMAT = Compact;
-- 创建表B(同步数据的表)
DROPTABLEIFEXISTS`tableB`;
CREATETABLE`tableB` (
`id`40) CHARACTERSETCOLLATENOTNULL,
`code`18) CHARACTERSETCOLLATENOTNULL,
`name`50) CHARACTERSETCOLLATENULLDEFAULTNULL,
`address`128) CHARACTERSETCOLLATENULLDEFAULTNULL,
`center`1) NULLDEFAULTNULL,
`fax`20) CHARACTERSETCOLLATENULLDEFAULT'',
`phone`45) CHARACTERSETCOLLATENULLDEFAULTNULL,
PRIMARYKEY`id`) USINGBTREE,
UNIQUEINDEX`pen_only_one`(`code`) USINGBTREE
) ENGINECHARACTERSETCOLLATE = utf8_general_ci ROW_FORMAT = Compact;
创建insert类型的触发
drop triggerexists insert_tableB;
-- 如果有这个触发器就先删除
createtrigger insert_tableB 
-- 触发表名称 insert_tableB
after insert
-- 触发条件，在insert操作之后 
on tableA 
-- 需要在哪个表触发
for each row
begin 
insertinto`code`,`id`)
 values(
`code`,
`id`
 );
-- sql语句 
end;

首先A表是空表，没有数据。

开始在表Ａ中插入一条数据。

insertinto`code`,`id`)

'12345','54321');

效果，已经同步了。

下面尝试一下，不同表，不同字段能否同步（类型要一致）

我们删除表B，重新创建

-- 创建表B(同步数据的表)

DROPTABLEIFEXISTS`tableB`;
CREATETABLE`tableB` (
`id_id`40) CHARACTERSETCOLLATENOTNULL,
`code_code`18) CHARACTERSETCOLLATENOTNULL,
`name`50) CHARACTERSETCOLLATENULLDEFAULTNULL,
`address`128) CHARACTERSETCOLLATENULLDEFAULTNULL,
`center`1) NULLDEFAULTNULL,
`fax`20) CHARACTERSETCOLLATENULLDEFAULT'',
`phone`45) CHARACTERSETCOLLATENULLDEFAULTNULL,
PRIMARYKEY`id_id`) USINGBTREE,
UNIQUEINDEX`pen_only_one`(`code_code`) USINGBTREE
) ENGINECHARACTERSETCOLLATE = utf8_general_ci ROW_FORMAT = Compact;

然后修改触发器

drop triggerexists insert_tableB;

-- 如果有这个触发器就先删除

createtrigger insert_tableB

-- 触发表名称 insert_tableB

after insert

-- 触发条件，在insert操作之后

on tableA

-- 需要在哪个表触发

for each row
begin 
insertinto`code_code`,`id_id`)
 values(
`code`,
`id`
 );

-- sql语句

end;

重新在A表中插入数据

insertinto`code`,`id`)

'aaaaa','bbbbbb');

结果，同步成功。

结论：不同字段的相同类型可以同步，后面不再详细测试。

关于mysql中new和old的区别，请看这篇文章。

3.更新操作

我们修改触发器，当tableA表更新时，触发

drop triggerexists update_tableB;

-- 如果有这个触发器就先删除

createtrigger update_tableB

-- 触发表名称 update_tableB

after update

-- 触发条件，在update操作之后

on tableA

-- 需要在哪个表触发

for each row

begin

update tableB

set

`code`,

`id`

WHERE=old.`code`;

-- sql语句

end;

然后在表A更新数据：

UPDATE'43'

WHERE'aaaaa'

tableB表效果同步更新。

4.删除操作

创建删除的触发器

drop triggerexists delete_tableB;

-- 如果有这个触发器就先删除

createtrigger delete_tableB

-- 触发表名称 delete_tableB

after DELETE

-- 触发条件，在delete操作之后

on tableA

-- 需要在哪个表触发

for each row

begin

DELETEfrom tableB

WHERE=old.`code`;

-- sql语句

end;

在tableA表执行删除操作。

DELETEfrom tableA

WHERE`code`='43';

tableB表同步删除。