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 textBEGINDECLARE 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 bine_tags;create PROCEDURE bine_tags()BEGINDECLARE 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 bine_tags();--调用存储过程