mysql自定义函数实现

环境准备

查看mysql版本

select version();

在这里插入图片描述
查看mysql存储引擎

show variables like '%storage_engine%';

在这里插入图片描述

创建测试表

CREATE TABLE `dsp_user_media_tag` (`id` int(11) NOT NULL AUTO_INCREMENT,`imei` varchar(50) COLLATE utf8_bin DEFAULT NULL,`tagname` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '标签名称',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin；
CREATE TABLE `dsp_user_interest_tag` (`id` int(11) NOT NULL AUTO_INCREMENT,`imei` varchar(50) COLLATE utf8_bin DEFAULT NULL,`tag` varchar(20) COLLATE utf8_bin DEFAULT NULL COMMENT '用户兴趣标签',PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin；

测试数据

select imei,tagname from test.dsp_user_media_tag limit 10;

在这里插入图片描述

创建函数

实现用户多标签的合并

drop FUNCTION  if EXISTS test.get_user_media_tag;
create FUNCTION test.get_user_media_tag(user_id VARCHAR(40))
RETURNS text
BEGINDECLARE tags text DEFAULT '';DECLARE tag VARCHAR(64) default '';DECLARE a int DEFAULT 0;DECLARE cur_tags CURSOR for select a.tagname from test.dsp_user_media_tag a where a.imei=user_id;DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET a=1; OPEN cur_tags;while a<>1 DOFETCH cur_tags into tag;set tags = CONCAT(tags,tag);END WHILE;CLOSE cur_tags;RETURN tags;
END;

调用函数

select imei,test.get_user_media_tag(imei) from (select DISTINCT imei from test.dsp_user_interest_tag) u;

附存储过程创建调用方式

drop PROCEDURE if EXISTS test.combine_tags;
create PROCEDURE test.combine_tags()
BEGIN
DECLARE uid VARCHAR(40) DEFAULT '';
DECLARE a int DEFAULT 0;
DECLARE ut text DEFAULT '';
DECLARE user CURSOR FOR  SELECT DISTINCT b.imei from test.dsp_user_interest_tag b;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET a=1; 
OPEN user;FETCH user into uid;while a<>1 doset ut = CONCAT(ut,test.get_user_media_tag(uid));select uid,ut;FETCH  user into uid;end while;
CLOSE user;
END;
call test.combine_tags();		--调用存储过程