这篇文章，主要介绍MySQL中的自定义函数和触发器的使用。

目录

一、自定义函数

1.1、创建函数

1.2、案例代码

二、触发器

2.1、创建测试表

2.2、插入触发器

2.3、更新触发器

2.4、删除触发器

2.5、字段触发器

一、自定义函数

1.1、创建函数

MySQL中自定义函数使用【CREATE FUNCTION】来创建，具体的语法格式如下所示：

create function 函数名称(参数名称 参数类型,......)
returns 函数返回值数据类型
begin
    这里写具体的函数体内容
    return 返回值;
end

上面语法中，函数必须使用return返回一个值。

1.2、案例代码

-- 删除函数
drop function if exists demo_first_func;

-- 创建函数
create function demo_first_func(name varchar(10), age int)
returns VARCHAR(100)
BEGIN
	return concat('hello world, my name is ', name, ', age is ', age);
END

-- 调用函数
select demo_first_func('Tom', 20);

运行结果如下：

二、触发器

MySQL中支持行级触发器，但是不支持某个字段的触发器，Oracle才支持某个字段的触发器。触发器有三种类型：

• insert插入数据触发器：当表中新插入数据的时候，就会触发一个事件。
• update更新数据触发器：当表中某条数据被更新的时候，就会触发一个事件。
• delete删除数据触发器：当表中删除数据的时候，就会触发一个事件。

MySQL总共有6种触发器，分别是：插入之前触发、插入之后触发、更新之前触发、更新之后触发、删除之前触发、删除之后触发。触发器创建语法如下所示：

-- 创建触发器
create trigger 触发器名称
before/after  -- 触发器执行的时机，before表示之前执行，after表示之后执行
insert/update/delete on 表名称  -- 触发器类型，对某一张表进行哪种操作时候，会触发事件
for each row  -- 每一行数据设置触发器
BEGIN
	-- 具体触发器执行的代码
END

2.1、创建测试表

为了演示三种触发器的使用，这里创建两张数据表用于测试。

CREATE TABLE `t_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t_test02` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.2、插入触发器

对于insert插入触发器来说，可以在触发器代码块中，使用【new】属性获取到新插入的数据记录，换句话说，【new】就表示新插入的那一条数据。

-- 删除触发器
drop trigger if exists insert_demo_trigger;

-- 创建触发器
delimiter $
create trigger insert_demo_trigger
before  -- 触发器执行的时机，before表示之前执行，after表示之后执行
insert on t_test  -- 触发器类型，对某一张表进行哪种操作时候，会触发事件
for each row  -- 每一行数据设置触发器
BEGIN
	-- 具体触发器执行的代码
	-- 可以通过 【new】 属性获取到新插入的数据记录
	-- 将新插入的数据，修改之后，插入到 【t_test02】表中
	insert into t_test02 values( new.id + 1000, concat(new.name, '_bak'), new.age + 10);
END $
delimiter ;

下面向【t_test】表中插入一条数据，然后查看【t_test02】表中的记录。

-- 插入数据
insert into t_test values(1, '测试insert触发器', 20);

执行结果如下所示：

2.3、更新触发器

更新触发器可以通过【new】属性获取到更新之后的数据，通过【old】属性获取到更新之前的数据。

• new属性：更新之后或者即将要更新的数据记录。
• old属性：更新之前的数据记录。

为了测试方便，可以把前面创建的insert插入触发器先删除掉，在创建update更新触发器进行测试。

-- 删除触发器
drop trigger if exists update_demo_trigger;

-- 创建触发器
delimiter $
create trigger update_demo_trigger
before  -- 触发器执行的时机，before表示之前执行，after表示之后执行
update on t_test  -- 触发器类型，对某一张表进行哪种操作时候，会触发事件
for each row  -- 每一行数据设置触发器
BEGIN
	-- 具体触发器执行的代码
	-- 可以通过 【new】 属性获取到即将要更新的数据记录
	-- 可以通过 【old】 属性获取到更新之前的数据记录
	-- 将新更新的数据、更新之前的数据，插入到 【t_test02】表中
	insert into t_test02 values( new.id + 2000, concat(new.name, '_new'), new.age + 20);
	insert into t_test02 values( old.id + 3000, concat(old.name, '_old'), old.age + 30);
END $
delimiter ;

更新数据记录，查看两个测试表的结果。

-- 更新数据
update t_test set name = '测试update触发器' where id = 1;

执行结果如下所示：

2.4、删除触发器

删除触发器可以通过【old】属性获取到删除之前的数据记录。

-- 删除触发器
drop trigger if exists delete_demo_trigger;

-- 创建触发器
delimiter $
create trigger delete_demo_trigger
before  -- 触发器执行的时机，before表示之前执行，after表示之后执行
delete on t_test  -- 触发器类型，对某一张表进行哪种操作时候，会触发事件
for each row  -- 每一行数据设置触发器
BEGIN
	-- 具体触发器执行的代码
	-- 可以通过 【old】 属性获取到删除之前的数据记录
	-- 将删除的数据，插入到 【t_test02】表中
	insert into t_test02 values( old.id + 3000, concat(old.name, '_old'), old.age + 30);
END $
delimiter ;

执行一条删除语句，查看【t_test02】测试表中的数据。

-- 删除数据
delete from t_test where id = 1;

执行结果如下所示：

2.5、字段触发器

前面三种触发器都是针对每一行数据记录的，在实际的一些开发过程中，可能会存在某些需求是根据指定的字段来执行触发器，但是MySQL中并没有提供针对字段的触发器，Oracle中提供了字段触发器，可以对某个字段进行触发事件的监听。

虽然MySQL没有提供字段触发器，但是通过update更新触发器可以实现字段触发器的功能，基本思想就是：在update更新触发器中，通过【new】和【old】属性判断某个字段前后的数据是否一致，如果不一致，则表示这个字段发生了修改，那么此时就可以执行触发逻辑。

-- 删除触发器
drop trigger if exists update_field_demo_trigger;

-- 创建触发器
delimiter $
create trigger update_field_demo_trigger
before  -- 触发器执行的时机，before表示之前执行，after表示之后执行
update on t_test  -- 触发器类型，对某一张表进行哪种操作时候，会触发事件
for each row  -- 每一行数据设置触发器
BEGIN
	-- 具体触发器执行的代码
	-- 可以通过 【old】和【new】 属性对比字段前后的值
	-- 将数据，插入到 【t_test02】表中
	if new.name <> old.name then
		-- name字段发生了改变，则执行触发逻辑
		insert into t_test02 values( new.id + 3000, concat(new.name, '_field'), new.age + 30);
	end if;
END $
delimiter ;

更新数据的name字段，查看测试表的记录变化。

-- 更新数据
update t_test set name = '测试update字段触发器' where id = 1;

执行结果如下：

到此，MySQL自定义函数和触发器就介绍完啦。

综上，这篇文章结束了，主要介绍MySQL中的自定义函数和触发器的使用。