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
BEGIN
DECLARE 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 DO
FETCH 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 do
set 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(); --调用存储过程
