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();--调用存储过程