MySql multiple insert having multiple parent and child

Question

how to insert multiple parent having multiple child in mysql?

example:

parent - child/children
jane - jane1,jane2
denise - denise1,denise2,denise3
lovely - lovely1

-parent insert

insert into tbl_parent(id,name)
    values('1', 'jane'), ('2', 'denise'), ('3', 'lovely')

-child insert

Answer 1

Even as your question is incomplete and guessing is like looking into the crystal ball, I've made some assumptions:

• I've put your example in a table tbl_input
• I've created a table tbl_person to cover id,name and parent_id to fulfill a 1:n relation from the child to the parent, so parents an childs are in one table!

Table definition of tbl_person,tbl_input and some sample data:

CREATE TABLE `tbl_person` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) unsigned DEFAULT NULL,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `fk_parent_person` (`parent_id`),
  CONSTRAINT `fk_parent_person` FOREIGN KEY (`parent_id`) 
    REFERENCES `tbl_person` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE tbl_input(parent VARCHAR(20),childs VARCHAR(200));

INSERT INTO tbl_input(parent,childs) VALUES
('parent','child,children'),
('jane','jane1,jane2'),
('denise','denise1,denise2,denise3'),
('lovely','lovely1');

Definition of function str_split and procedure Input2person: (Which I have adapted from this answer)

DELIMITER $$

CREATE FUNCTION strSplit(x VARCHAR(20000), delim VARCHAR(12), pos INTEGER) 
RETURNS VARCHAR(20000)
BEGIN
  DECLARE output VARCHAR(20000);
  SET output = REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos)
                 , LENGTH(SUBSTRING_INDEX(x, delim, pos - 1)) + 1)
                 , delim
                 , '');
  IF output = '' THEN SET output = null; END IF;
  RETURN output;
END $$


CREATE PROCEDURE Input2person()
BEGIN
  DECLARE i INTEGER;

  INSERT INTO tbl_person (name) SELECT parent FROM tbl_input;

  SET i = 1;
  REPEAT
    INSERT INTO tbl_person (name,parent_id)
      SELECT strSplit(c.childs, ',', i), p.id
      FROM tbl_input c JOIN tbl_person p ON c.parent = p.name
      WHERE strSplit(c.childs, ',', i) IS NOT NULL;
    SET i = i + 1;
    UNTIL ROW_COUNT() = 0
  END REPEAT;
END $$

DELIMITER ;

Now you have to CALL Input2Person(); and SELECT * FROM tbl_person; to get your result. Hope that helps.