mysql存储过程字符串分割

最新推荐文章于 2024-02-07 09:28:32 发布

晓猫

最新推荐文章于 2024-02-07 09:28:32 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql 文章标签： mysql 存储返回结果集

本文链接：https://blog.youkuaiyun.com/WuXianYuYongHeng/article/details/76521036

mysql 专栏收录该内容

5 篇文章

订阅专栏

本文介绍了一种在SQL中实现字符串分割的方法，包括获取分割后的字符串数量及具体字符串的功能函数，并通过一个存储过程示例展示了如何利用这些函数处理包含多个字段的数据记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

获得所有以“某个符号“分割的字符串的个数

CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string_total`(f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(110)
BEGIN
  -- Get the total number of given string.
    #获得所有以“某个符号“分割的字符串的个数
  return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END

按分割取出字符串

CREATE DEFINER=`root`@`%` FUNCTION `func_get_split_string`(
f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
  -- Get the separated number of given string.
#
  declare result varchar(255) default '';
  set result = reverse(substring_index(reverse(substring_index(f_string,f_delimiter,f_order)),f_delimiter,1));
  return result;
END

测试

@以豆号分割，取分割后的第一个数
select func_get_split_string("a,b,c",",",1);
==>a
@以逗号分割，计算分割后的总数
select func_get_split_string_total("a,b,c",",");
==>3

例子：返回结果集

DROP PROCEDURE IF EXISTS `VehicleTrackProcedure`;

CREATE DEFINER = `root`@`%` PROCEDURE `VehicleTrackProcedure`(in begintimes VARCHAR(30),in endtimes varchar(30),in platenos varchar(30),in pages varchar(30))
BEGIN
    DECLARE id BIGINT DEFAULT 0;
    DECLARE begintime datetime DEFAULT NULL; 
    DECLARE endtime datetime DEFAULT null;
    DECLARE plateno VARCHAR(15) DEFAULT NULL;
    DECLARE path LONGTEXT DEFAULT null;
    DECLARE updatetime datetime DEFAULT null;
    DECLARE oriroadcode INT default 0;
    DECLARE desroadcode INT DEFAULT 0;
    DECLARE devicecodes LONGTEXT DEFAULT null;
    DECLARE passtimes LONGTEXT DEFAULT null;
    DECLARE devicetype VARCHAR(25) DEFAULT NULL;
    DECLARE algotype VARCHAR(25) DEFAULT NULL;
    DECLARE devicename VARCHAR(50) DEFAULT NULL;
    DECLARE totalcount INT DEFAULT 0;
    DECLARE finished INT DEFAULT 0;
    declare cnt int default 0;
    declare i int default 0;
    DECLARE k INT DEFAULT 0;
    DECLARE pagebegin INT DEFAULT 0 ;
    DECLARE pageend INT DEFAULT 0;
    DECLARE aa INT DEFAULT 0;
    DECLARE bb INT DEFAULT 0;
    DECLARE dcode VARCHAR(30) DEFAULT NULL;
    DECLARE roadcode VARCHAR(50) DEFAULT NULL;
    DECLARE vehicle_cur CURSOR FOR SELECT ID,BEGIN_TIME,END_TIME,PLATE_NO,PATH,UPDATETIME,ORI_ROAD_CODE,DES_ROAD_CODE,DEVICE_CODES,PASS_TIMES,DEVICE_TYPE,ALGO_TYPE FROM result_path_vhc;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET finished = 1;
    DROP TABLE IF EXISTS vehicle_temp;
    CREATE TEMPORARY TABLE vehicle_temp(
        `ID` bigint(20) DEFAULT NULL,
        `BEGIN_TIME` datetime DEFAULT NULL COMMENT '开始时间',
        `END_TIME` datetime DEFAULT NULL COMMENT '结束时间',
        `PLATE_NO` varchar(15) DEFAULT NULL COMMENT '车牌号',
        `PATH` longtext COMMENT '路径（路段编号的逗号表达式）',
        `UPDATETIME` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '入库时间',
        `ORI_ROAD_CODE` int(11) DEFAULT NULL COMMENT '开始路段编号',
        `DES_ROAD_CODE` int(11) DEFAULT NULL COMMENT '结束路段编号',
        `DEVICE_CODES` longtext COMMENT '经过卡口的设备编号（逗号表达式）',
        `PASS_TIMES` longtext COMMENT '经过每条路段所花时间（逗号表达式）',
        `DEVICE_TYPE` varchar(25) DEFAULT NULL COMMENT '设备类型',
        `ALGO_TYPE` varchar(25) DEFAULT NULL COMMENT '算法类型',
        `DEVICE_NAME` VARCHAR(50) DEFAULT NULL COMMENT '设备名称',
        `TOTAL_COUNT` INT(11) DEFAULT 0,
        `ROAD_CODE` VARCHAR(50) DEFAULT NULL
    );
    #select ID,BEGIN_TIME,END_TIME,PLATE_NO,PATH,UPDATETIME,ORI_ROAD_CODE,DES_ROAD_CODE,DEVICE_CODES,PASS_TIMES,DEVICE_TYPE,ALGO_TYPE from result_path_vhc;
    #INSERT into vehicle_temp(ID,BEGIN_TIME,END_TIME,PLATE_NO,PATH,UPDATETIME,ORI_ROAD_CODE,DES_ROAD_CODE,DEVICE_CODES,PASS_TIMES,DEVICE_TYPE,ALGO_TYPE) select ID,BEGIN_TIME,END_TIME,PLATE_NO,PATH,UPDATETIME,ORI_ROAD_CODE,DES_ROAD_CODE,DEVICE_CODES,PASS_TIMES,DEVICE_TYPE,ALGO_TYPE from result_path_vhc;
    #select * from vehicle_temp; 
    OPEN vehicle_cur;
    FETCH vehicle_cur INTO id,begintime,endtime,plateno,path,updatetime,oriroadcode,desroadcode,devicecodes,passtimes,devicetype,algotype;
    WHILE finished = 0 DO
        IF devicecodes IS NOT NULL THEN
            set cnt = func_get_split_string_total(devicecodes,",");
            WHILE i < cnt DO
                SET i = i + 1;
                SET k = k + 1;
                SET dcode = func_get_split_string(devicecodes,",",i);
                SELECT DEVICE_NAME,ROAD_CODE INTO devicename,roadcode FROM info_bay WHERE DEVICE_CODE = dcode;
                INSERT INTO vehicle_temp(ID,BEGIN_TIME,END_TIME,PLATE_NO,PATH,UPDATETIME,ORI_ROAD_CODE,DES_ROAD_CODE,DEVICE_CODES,PASS_TIMES,DEVICE_TYPE,ALGO_TYPE,DEVICE_NAME,ROAD_CODE) VALUES(id,begintime,endtime,plateno,path,updatetime,oriroadcode,desroadcode,dcode,passtimes,devicetype,algotype,devicename,roadcode);
                #select * from vehicle_temp;
        END WHILE;
        ELSE
            SET k = k + 1;
            INSERT INTO vehicle_temp(ID,BEGIN_TIME,END_TIME,PLATE_NO,PATH,UPDATETIME,ORI_ROAD_CODE,DES_ROAD_CODE,DEVICE_CODES,PASS_TIMES,DEVICE_TYPE,ALGO_TYPE) VALUES(id,begintime,endtime,plateno,path,updatetime,oriroadcode,desroadcode,devicecodes,passtimes,devicetype,algotype);
        END IF;
        FETCH vehicle_cur INTO id,begintime,endtime,plateno,path,updatetime,oriroadcode,desroadcode,devicecodes,passtimes,devicetype,algotype;
        SET i = 0;
        SET cnt = 0;
    END WHILE;
    UPDATE vehicle_temp SET TOTAL_COUNT = k;
    SET pagebegin = func_get_split_string(pages,",",1);
    SET pageend = func_get_split_string(pages,",",2);
    #select begintimes;
    IF begintimes IS NULL && endtimes  IS NULL && platenos IS NULL THEN
        SELECT * FROM vehicle_temp LIMIT pagebegin,pageend;
    ELSEIF begintimes IS NULL && endtimes  IS NULL && platenos IS NOT NULL THEN
        SELECT * FROM vehicle_temp WHERE PLATE_NO=platenos LIMIT pagebegin,pageend;
    ELSEIF begintimes IS NOT NULL && endtimes IS NOT NULL && platenos IS NOT NULL THEN
        SELECT * FROM vehicle_temp WHERE PLATE_NO=platenos AND begintimes>BEGIN_TIME AND endtimes<END_TIME LIMIT pagebegin,pageend;
    ELSEIF begintimes IS NOT NULL && endtimes IS NOT NULL && platenos IS NULL THEN
        SELECT * FROM vehicle_temp WHERE begintimes>BEGIN_TIME AND endtimes<END_TIME LIMIT pagebegin,pageend;
    END IF; 
    CLOSE vehicle_cur;
END;