MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE categories (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                         category_name VARCHAR(64), 
                         parent_id INT UNSIGNED NOT NULL DEFAULT 0,
                         is_active CHAR(1) NULL,
                         UNIQUE INDEX idx_name_active (category_name));
CREATE TABLE source_data (path TEXT);
INSERT INTO source_data VALUES ('animals/domestic/dog'), ('animals/domestic/cat');

Records: 2  Duplicates: 0  Warnings: 0


CREATE PROCEDURE update_categories_table()
BEGIN
DECLARE cnt INT DEFAULT 0;
INSERT IGNORE INTO categories (category_name, parent_id, is_active)
SELECT SUBSTRING_INDEX(path, '/', 1), 0, '1'
FROM source_data;
iteration: LOOP
    SELECT COUNT(*) INTO cnt
    FROM source_data
    WHERE LOCATE('/', path);
    IF NOT cnt THEN 
        LEAVE iteration;
    END IF;
    INSERT IGNORE INTO categories (category_name, parent_id, is_active)
    SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(source_data.path, '/', 2), '/', -1),
           categories.id,
           '1'
    FROM source_data, categories
    WHERE SUBSTRING_INDEX(source_data.path, '/', 1) = categories.category_name;
    UPDATE source_data
    SET path = SUBSTRING(path FROM 1 + LOCATE('/', path));
END LOOP iteration;
TRUNCATE source_data;
END
call update_categories_table;
SELECT * FROM categories;


id
category_name
parent_id
is_active


1
animals
0
1

4
domestic
1
1

7
dog
4
1

8
cat
4
1