学习mysql存储过程--如何处理字符串字符个数

# 需求分析：

 实现代码：

 1、创建表格

CREATE TABLE label_table (
    company varchar(200)PRIMARY KEY ,
    label varchar(255),
    label_num varchar(255)
);

 2、定义存储过程

delimiter $$
drop procedure if exists  insert_update_table;
create procedure insert_update_table(In in_company varchar(200), In in_label varchar(255)) 
BEGIN
    declare selectedCOUNT int default 0;
    declare newLabel varchar(200) default '';
    declare oldLabel varchar(200);
    declare newLabelNum varchar(400);
    declare tmpStr varchar(200);
    declare newLabelCopy varchar(200);
    declare t int;    
    # 声明一个自定义的变量
    select count(*) into selectedCOUNT
    from label_table 
    where label_table.company=in_company;
    if (selectedCOUNT>0) then 
        select label into oldLabel
        from label_table 
        where label_table.company=in_company;
        set oldLabel =  IFNULL(oldLabel, '');
        set newLabel =  CONCAT(oldLabel , in_label);
    else 
        set newLabel =  in_label;
    end if;
    set newLabelNum = '';
    set newLabelCopy = newLabel;
    while length(newLabelCopy)>0 DO
        set tmpStr = left(newLabelCopy,1);
        set t = length(newLabelCopy)-length(replace(newLabelCopy,tmpStr,''));
        set newLabelNum =  CONCAT(newLabelNum , tmpStr , concat(t,''));
        set newLabelCopy = replace(newLabelCopy,tmpStr,'');     
    end while;
    if (selectedCOUNT>0) then 
        update label_table SET label=newLabel, label_num=newLabelNum  where company = in_company;
    else
        BEGIN
            insert into label_table(company,label,label_num) values(in_company,newLabel,newLabelNum);
        END;
    end if;
    COMMIT;
END$$
delimiter ;

 3. 测试

INSERT INTO label_table(company,label) VALUES('A', 'tyutyt');
INSERT INTO label_table(company,label) VALUES('B', 'werwe');
select * from label_table;
call insert_update_table('a','');
select * from label_table;
call insert_update_table('b','');
select * from label_table;

效果