Split string in mysql inside procedure-CodePudding

I have resumeids as string which i want to save in ResumeActivityLog table one by one row so total 6 rows.

var resumeids = "R2W5D16T8BRYWZKRF28,RHL1B05ZTQ546JKF0RG,RD95SY747Z89M7GN8TP,R2N54T767CZTBY79N00,RHT2GR6K0FTYTPFG985,RDH4KV5Z3WX7SV2850N";

For this im using procedure SaveMultipleResumeActivityLog to save multiple enteries. How do i break this string of resumedid in single entry in every while loop iteration to generate 6 unique enteries.

SaveMultipleResumeActivityLog(resumeids, 6);

CREATE  PROCEDURE `SaveMultipleResumeActivityLog`(
    _resumeDIDs           Text,
    _totalRecords          int(11),
)
BEGIN
    DECLARE i INT  DEFAULT 0;
    
    WHILE i < _totalRecords DO 
    INSERT INTO `ResumeActivityLog`
    ( `ResumeDID`, `CreatedOn`)
    VALUES(resumeDID, NOW());
    SET i =i  1;
    ENDWHILE;
END

CodePudding user response：

-- create table
CREATE TABLE ResumeActivityLog ( ResumeDID VARCHAR(255), CreatedOn DATETIME);

-- create procedure
CREATE  PROCEDURE `SaveMultipleResumeActivityLog`(
    _resumeDIDs           Text
)
    INSERT INTO `ResumeActivityLog` ( `ResumeDID`, `CreatedOn`)
    SELECT ResumeDID, CURRENT_TIMESTAMP
    FROM JSON_TABLE(CONCAT('["', REPLACE(_resumeDIDs, ',', '","'), '"]'),
                    '$[*]' COLUMNS (ResumeDID VARCHAR(255) PATH '$')) jsontable;

-- call procedure with CSV as a parameter
CALL SaveMultipleResumeActivityLog('R2W5D16T8BRYWZKRF28,RHL1B05ZTQ546JKF0RG,RD95SY747Z89M7GN8TP,R2N54T767CZTBY79N00,RHT2GR6K0FTYTPFG985,RDH4KV5Z3WX7SV2850N');

-- check for the result
SELECT * FROM ResumeActivityLog;

ResumeDID	CreatedOn
R2W5D16T8BRYWZKRF28	2022-07-18 09:50:28
RHL1B05ZTQ546JKF0RG	2022-07-18 09:50:28
RD95SY747Z89M7GN8TP	2022-07-18 09:50:28
R2N54T767CZTBY79N00	2022-07-18 09:50:28
RHT2GR6K0FTYTPFG985	2022-07-18 09:50:28
RDH4KV5Z3WX7SV2850N	2022-07-18 09:50:28

db<>fiddle here

PS. I'd define CreatedOn column as auto-utilized.

this fails for mysql version 5.7.2 – art

-- create procedure
CREATE  PROCEDURE `SaveMultipleResumeActivityLog`(
    _resumeDIDs           Text
)
BEGIN
REPEAT
    INSERT INTO `ResumeActivityLog` ( `ResumeDID`, `CreatedOn`)
    SELECT SUBSTRING_INDEX(_resumeDIDs, ',', 1), CURRENT_TIMESTAMP;
    SET _resumeDIDs = SUBSTRING(_resumeDIDs FROM LENGTH(SUBSTRING_INDEX(_resumeDIDs, ',', 1)) 2);
UNTIL _resumeDIDs = '' END REPEAT;
END

db<>fiddle here