mysql触发器中定义一个函数_mysql 触发器和自定义函数

首先自定义一个函数

DROP FUNCTION IF EXISTS spark.GetSplitString;

CREATE FUNCTION spark.`GetSplitString`(pageid varchar(16), v1 double(16,13), v2 double(16,13), v3 tinyint(4))

RETURNS varchar(16) CHARSET utf8

BEGIN

declare result varchar(16) CHARSET gbk DEFAULT '';

SET result = pageid;

insert into spark.userbehavior(page, number) values (pageid, v1 * 0.8 + v2 * 0.8 + v3);

RETURN result;

END;

创建触发器

CREATE TRIGGER spark.triggertest

before INSERT ON spark.userbehaviordatasource

FOR EACH ROW

BEGIN

set new.page = spark.GetSplitString(new.page, new.v1, new.v2, new.v3);

END;

向spark.userbehaviordatasource插入一条数据

insert into spark.userbehaviordatasource(page, v1, v2, v3) values ("page04", 1.6, 1.3, 1)

查询spark.userbehavior

select * from spark.userbehavior

sorry，不小心插入了两条✧(≖ ◡ ≖✿)嘿嘿