在表设计过程中，由于经常会碰到字段通过逗号分隔开的多个子串，例如：
a,b,c,d，但是：在有些需求需要用到单个子串，
并且又不想在业务代码中实现字符串的切割，于是我就想着，通过自定义存储过程和函数实现以上功能。下面将介绍我如何一步步实现整个过程的。
1、字符串通通过下标获取子串函数
drop function if exists `SPLIT_STR`;
delimiter;;
create function `SPLIT_STR`(str varchar(255), delimiter varchar(20), position INT) returns varchar(255) charset utf8mb4
return SUBSTRING_INDEX(SUBSTRING_INDEX(str,delimiter,position),delimiter,-1);
;;
delimiter;
# 测试
select SPLIT_STR('a,b,v', ',', 2);
2、字符子串计数函数
drop function if exists `SPLIT_SIZE`;
delimiter;;
create function `SPLIT_SIZE`(str varchar(255), delimiter varchar(20)) returns int
return 1+(LENGTH(str)-LENGTH(replace(str,delimiter,'')));
;;
delimiter;
# 测试
select SPLIT_SIZE('a,b,v', ',');
3、通过传参实现SQL动态执行存储过程
表结构
DROP TABLE IF EXISTS `t_ent_enterprise_mold`;
CREATE TABLE `t_ent_enterprise_mold` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`create_date` datetime NULL DEFAULT NULL COMMENT '创建日期',
`update_date` datetime NULL DEFAULT NULL COMMENT '修改日期',
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '类别名称',
`intercept_certificates` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL COMMENT '拦截证件',
`is_deleted` tinyint(1) NULL DEFAULT 0 COMMENT '是否删除',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '企业类别' ROW_FORMAT = DYNAMIC;
表数据
INSERT INTO `t_ent_enterprise_mold` VALUES (1, '2022-03-03 09:55:50', '2022-03-03 09:55:51', '医药批发企业', '二类医疗器械备案,发票模版,法人委托书,化妆品生产卫生许可证,营业执照,GMP证书,GSP证书', 0);
INSERT INTO `t_ent_enterprise_mold` VALUES (2, '2022-03-03 09:56:08', '2022-03-03 09:56:09', '医药零售企业', '二类医疗器械备案,发票模版', 0);
INSERT INTO `t_ent_enterprise_mold` VALUES (22, '2022-04-20 13:22:46', '2022-04-20 13:22:46', '888', '二类医疗器械备案,发票模版,全国工业产品生产许可证', 0);
INSERT INTO `t_ent_enterprise_mold` VALUES (23, '2022-04-30 10:02:12', '2022-04-30 10:02:12', '医疗美容企业', '化妆品生产卫生许可证,开户许可证', 0);
创建动态表名分页存储过程
drop procedure if exists `PAGE`;
delimiter;;
create procedure page(in tableName varchar(50), in pageNo int, in pageSize int)
begin
declare startIndex int;
set startIndex = pageSize * (pageNo - 1);
set @sqlx = concat("select * from ",tableName," limit ",startIndex,",",pageSize);
prepare stmt from @sqlx;
execute stmt;
deallocate prepare stmt;
end;
;;
delimiter;
# 测试
call PAGE('t_ent_enterprise_mold', 1, 2);
4、字符串转列存储过程
drop procedure if exists SPLIT;
delimiter;;
create procedure SPLIT(in tableName varchar(50), in columnName varchar(50), in delimiter varchar(20), in alias varchar(50))
begin
set @sqlx = concat("SELECT id,SPLIT_STR(t.", columnName,", '", delimiter,"', ht.help_topic_id + 1) AS ", alias," FROM ", tableName ," t inner join mysql.help_topic ht on ht.help_topic_id < SPLIT_SIZE(t.",columnName,", '",delimiter,"')");
prepare stmt from @sqlx;
execute stmt;
deallocate prepare stmt;
end
;;
delimiter;
# 测试
call SPLIT('t_ent_enterprise_mold', 'intercept_certificates', ',', 'cert');
5、拓展：排名问题
创建表结构
drop table if exists score;
create table score (
id int(11) not null auto_increment primary key comment '编号',
score decimal(10,1) not null comment '分数'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '分数表';
表数据
insert into score(score) values(90),(80),(88),(60),(80),(90);
并列排名（子查询）
select id, score, (select count(distinct(score)) from score as b where b.score > a.score) + 1 as rank from score as a order by rank;
顺序排名(变量)
select id, score, @row := @row + 1 as rank from score,(select @row := 0) as t order by score desc;
