How to use column data from a table in a mysql procedure?

Question

I am currently making a database table that separates a comma-separated list of tags from one table to a table containing every unique tag in the first table. Ultimately, this will be used to create a table where every tag is joined with every id that has it. I have created a procedure that separates a list of values based on commas and inserts each value into a table:

DELIMITER //
DROP PROCEDURE IF EXISTS InsertTags;
CREATE PROCEDURE InsertTags
SELECT (Tags) from RawRecipes;
    BEGIN
    DECLARE token TEXT DEFAULT NULL;
    DECLARE toklength INT DEFAULT NULL;
    DECLARE TempValue TEXT DEFAULT NULL;
    iterator:
    LOOP
        IF LENGTH(TRIM(Value)) = 0 OR Value IS NULL THEN
            LEAVE iterator;
        END IF;
        SET token = SUBSTRING_INDEX(Value,',',1);
        SET toklength = LENGTH(token);
        SET TempValue = TRIM(front);
        INSERT IGNORE INTO Tags (tag) VALUES (TempValue);
        SET Value = INSERT(Value,1,frontlen + 1,'');
    END LOOP;
    END //
DELIMITER ;

But I do not know how to call the procedure for each row of the first table.

In case it is needed, here is the information for my tables:

RawRecipes: rid INT (Primary Key)...tags VARCHAR(1000)

Tags: tid INT (Primary Key), tag VARCHAR (Unique)

A sample list of tags looks like:

['weeknight', 'time-to-make', 'course', 'preparation', 'occasion', 'low-protein', 'healthy', '5-ingredients-or-less', 'desserts', '1-day-or-more', 'easy', 'low-fat', 'summer', 'frozen-desserts', 'freezer', '
dietary', 'low-sodium', 'gluten-free', 'low-cholesterol', 'seasonal', 'low-saturated-fat', 'low-calorie', 'healthy-2', 'free-of-something', 'low-in-something', 'equipment']

tl:dr Every row has something similar to this and I would like to run the procedure on each one to separate every value into its own row

Answer 1

I had to rewrite your try. Your iteration loop gave errors, so i changed the code to remove the first added value

Further I added a cursor for RawRecipes because i beleive you want to add all the tags from every row.

you should should think about reduce the procedure, to a Input parameter that will split the tags, and call it in a AFTER INSERT TRIGGER, so that when you insert a row into RawRecipes the tags where automatically added

Then of course you should also check in an AFTER UPDATE Trigger if tag in RawRecipes has changed and then call the procedure, that separates the words

DELIMITER $$
DROP PROCEDURE IF EXISTS InsertTags;
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertTags`()
BEGIN

    DECLARE token TEXT DEFAULT NULL;
    DECLARE toklength INT DEFAULT NULL;
    DECLARE TempValue TEXT DEFAULT NULL;
    DECLARE Value VARCHAR(1000);
    DECLARE finished INTEGER DEFAULT 0;
        DEClARE curtags 
        CURSOR FOR 
            SELECT tags from RawRecipes;

        DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;

    OPEN curtags;
    gettags: LOOP
        FETCH curtags INTO Value;
        IF finished = 1 THEN 
            LEAVE gettags;
        END IF;
        iterator:
        LOOP
            IF LENGTH(TRIM(Value)) = 0 OR Value IS NULL THEN
                LEAVE iterator;
            END IF;
            SET token = SUBSTRING_INDEX(Value,',',1);
            SET TempValue = TRIM(token);
            INSERT IGNORE INTO tags (tag) VALUES (TempValue);
            SET Value = SUBSTRING_INDEX(Value,',',-(LENGTH(Value) - LENGTH(REPLACE(Value, ',', '')) ));
        END LOOP iterator;
    END LOOP gettags;
    CLOSE curtags;

END$$
DELIMITER ;

UPDATE

MYsql Ignores The INSERT IGNORE INTO AND also REPLACE and adds all the tags again

So that tags has also to be UNIQUE , that there no double entrys

Like

CREATE TABLE `tags` (
  `tid` int NOT NULL AUTO_INCREMENT,
  `tag` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`tid`),
  UNIQUE KEY `tid_unique` (`tid`)
  ,
  UNIQUE KEY `tag_unique` (`tag`)
) ENGINE=InnoDB AUTO_INCREMENT=14337 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
ALTER TABLE tags AUTO_INCREMENT = 1;

CREATE TABLE `rawrecipes` (
  `rid` int NOT NULL,
  `tags` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

# tags
weeknight, time-to-make, course, preparation, occasion, low-protein, healthy, 5-ingredients-or-less, desserts, 1-day-or-more, easy, low-fat, summer, frozen-desserts, freezer, 
dietary, low-sodium, gluten-free, low-cholesterol, seasonal, low-saturated-fat, low-calorie, healthy-2, free-of-something, low-in-something, equipment
aaa,bbb,ccc,ddd,eee,ddd,fff