mysql自定义函数实现

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