mysql函数使用的例子

有以下图片的一个sql需求

 

 

 

 我不是很理解，这样的操作为什么要用数据库语句来实现。但我还是试一试吧

ts_job里的每一条数据的dev_id都要计算它的包含几个shorter列中的字母。我实在想不出来怎样的语句可以一次查询出来。如果使用函数的话，应该可以。

函数的要求是提供一个字符串，返回它包含几个shorter列的字母。这里要设置变量来存储这个值，由于我没怎么了解sql函数，只好按自己的想法写。

先建两个表td_dev_type和ts_job

DROP TABLE IF EXISTS `td_dev_type`;
CREATE TABLE `td_dev_type`  (
  `TYPE_ID` smallint(6) NOT NULL AUTO_INCREMENT,
  `SHORTER` char(1) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`TYPE_ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of td_dev_type
-- ----------------------------
INSERT INTO `td_dev_type` VALUES (1, 'F');
INSERT INTO `td_dev_type` VALUES (2, 'D');
INSERT INTO `td_dev_type` VALUES (3, 'B');
INSERT INTO `td_dev_type` VALUES (4, 'C');
INSERT INTO `td_dev_type` VALUES (5, 'C');

DROP TABLE IF EXISTS `ts_job`;
CREATE TABLE `ts_job`  (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `DEV_ID` varchar(64) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,
  PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of ts_job
-- ----------------------------
INSERT INTO `ts_job` VALUES (1, 'G004B001');
INSERT INTO `ts_job` VALUES (2, 'G001F002B34Ds');
INSERT INTO `ts_job` VALUES (3, 'G001C001B001C');
INSERT INTO `ts_job` VALUES (4, 'G004BCCB222D');

现在考虑函数，我必须遍历一遍td_dev_type来获取一列shorter，每次都要与参数做是否包含的判断，判断成功就是计数变量加1

drop function if exists countShorters;
create function countShorters(dev_id varchar(64)) returns int #这里根据dev_id的类型设置
begin
set @num:=0;
return (select @num:=IF(instr(dev_id,shorter)>0,@num+1,@num) nums from (select distinct shorter from td_dev_type) a order by nums desc limit 1); -- distinct 控制重复的字符是否也算
end;

其中核心

set @num:=0;
select @num:=IF(instr('G001B001CFC',shorter)>0,@num+1,@num) num from (select distinct shorter from td_dev_type) a

这样在查询的过程中根据条件设置用户变量num的值，得出的num列的数值的最后一行是最终结果，我使用倒序排列再limit 1 的方法得到这个值 。使用distinct则字符串依次与F/D/B/C判断，这样结果不会重复，不加的话重复的字符会多判断；注意这与字符串中字符的重复无关。

使用函数要注意：return行和end行末尾要加分号，不然会报错。而且我遇到的一个难题是无法使用局部变量，使用局部变量后结果是错的，不得已使用了用户变量。

现在进行测试

select countShorters('FDBCccC464B615')   # 结果为4 
select countShorters('G004232001001910B0001')   # 结果为1

最终语句

select countShorters(dev_id) from ts_job tj

此时可以满足需求。

还有个问题，我不清楚sql函数的生命周期和作用范围，这个函数是在什么环境下创建一次还是每次执行语句时运行该函数（如果已存在其不会重新创建），这样的需求用数据库做是不合适的，仅仅把这个作为一个练手。