mysql split string save rows

数据表，根据customer_id 以 ',' 进行分割，把数据拆成多条记录，并插入其他表

目标数据

处理之后的数据

小建议

调试存储过程中，可以试着把一些不知道的参数，存入表中，来查看存储过程执行过程中值的变化

SELECT @TEXT REGEXP '((,).*){N}';
N 的值在0-255 之间，否则报异常
Error Code: 1139. Got error 'invalid repetition count(s)' from regexp 0.000
https://dev.mysql.com/doc/refman/5.7/en/regexp.html

{1}, {2,3}

{n} or {m,n} notation provides a more general way of writing regular expressions that match many occurrences of the previous atom (or “piece”) of the pattern. m and n are integers.

a*

Can be written as a{0,}.

a+

Can be written as a{1,}.

a?

Can be written as a{0,1}.

To be more precise, a{n} matches exactly n instances of a. a{n,} matches n or more instances of a. a{m,n} matches m through n instances of a, inclusive.

m and n must be in the range from 0 to RE_DUP_MAX (default 255), inclusive. If both m and n are given, m must be less than or equal to n.

存储过程

CREATE  PROCEDURE `fetch_serial_number`()
BEGIN
        DECLARE done BOOLEAN DEFAULT 0;

        DECLARE _datatime VARCHAR(10); -- 定义局部变量
        DECLARE _serial_number VARCHAR(100); 
        DECLARE _customer_id mediumtext; 
        
        DECLARE serial_result cursor for select datatime, serial_number, customer_id  from TABLE_NAME;

        DECLARE continue handler for sqlstate '02000' set done = 1;
        
        OPEN serial_result;
        
        REPEAT
        
        FETCH serial_result INTO _datatime,_serial_number,_customer_id  ;
        #select _datatime,_serial_number,_customer_id ;
        
        IF done =0 THEN 
            SET @gather_data = _customer_id;
            SET @slice_data =  SUBSTRING_INDEX(SUBSTRING_INDEX(@gather_data,',',1),',',-1) ;
            
            IF LOCATE( ',', @gather_data )> 0  THEN
                loop1 : LOOP
                    IF @gather_data REGEXP CONCAT('((,).*){1}') THEN  
                    
                        # 得到','之前的结果    
                        # 1,2,3,4     得到   1
                        SET @slice_data =  SUBSTRING_INDEX(SUBSTRING_INDEX(@gather_data,',',1),',',-1) ;
                        
                        INSERT INTO `SAVE_TABLE_NAME`(`datatime`,`serial_number`,`customer_id`)
                         VALUES(_datatime,_serial_number,@slice_data);
                        
                        #重新给@gather_data 赋值，
                        # 1,2,3,4
                        # 2,3,4
                        # 3,4
                        # 4
                        
                        set @gather_data =SUBSTRING(@gather_data,length(SUBSTRING_INDEX(@gather_data,',',1)) +2);
                    ELSE
                        #切片到最后，没有',' 的情况下存储
                        INSERT INTO `SAVE_TABLE_NAME`(`datatime`,`serial_number`,`customer_id`)
                         VALUES(_datatime,_serial_number,@slice_data);
                        LEAVE loop1;
                    END IF;
                END LOOP;
            ELSE 
            # 字符串里只存在 一个结果
                INSERT INTO `SAVE_TABLE_NAME`(`datatime`,`serial_number`,`customer_id`)
                 VALUES(_datatime,_serial_number,@slice_data);
            END IF;
        END IF;
        UNTIL done END REPEAT;
        CLOSE serial_result;
        
        select 'SAVE_TABLE_NAME Rows' ,  count(1) from `SAVE_TABLE_NAME` ;
END

其他一些MYSQL语句

#插入表从查询结果
insert into table 
select * from table where  1=1;

#创建表从查询结果
create table TABLE_NAME
select * from table where 1=1 ;

#显示表的创建语句
show create table TABLE_NAME;