inserting values from arrays in mysql

Question

I have multiple user_roles. Each user_role has multiple privileges and each privileges has multiple values. I need to create a procedure with user_role_name,description,priviliges_fk(array),values(arrayofstring) as inputs. This is the procedure I have written.

DELIMITER $$
DROP PROCEDURE IF EXISTS `save_role`$$
CREATE DEFINER=`event_admin`@`%` PROCEDURE `save_role`(IN p_role_name INT, 
                           IN p_description INT, 
                           IN p_privilege_fk INT(), 
                           IN p_values VARCHAR(1000)
                          )
BEGIN
      DECLARE i int default 0;
      DECLARE V_ROLE_FK int;
      DECLARE counter INT DEFAULT 0;

      INSERT INTO ROLE (ROLE_NAME,DESCRIPTION) VALUES(p_role_name,p_description);
      SELECT ROLE_PK INTO V_ROLE_FK FROM ROLE WHERE ROLE_NAME=p_role_name AND DESCRIPTION=p_description;

simple_loop:LOOP 
SET counter = counter + 1;
     INSERT INTO ROLE_PRIVILEGE_BRIDGE (ROLE_FK,PRIVILEGE_FK,VALUE) VALUES(V_ROLE_FK,p_privilege_fk(i),p_values);
END LOOP simple_loop;
END;

Andreas Wederbrand · Accepted Answer · 2013-11-26 07:20:02Z

2

You can't. There are two workarounds that would work

Call the procedure one time per element in the array
Concatenate the array elements into one string separated by something (ie |, ;, :) and then split that string internally in the procedure.

I would go with the first alternative. It's cleaner, easier to understand and easier to test.

answered Nov 26, 2013 at 7:20

Andreas Wederbrand

40.5k12 gold badges71 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Devart · Accepted Answer · 2013-11-26 07:33:42Z

I'd suggest you to use AUTO_INCREMENT option for primary keys, it will help to work with them. Then use auto-incremented primary key values to insert new rows into a child table - one by one, not using array as a string parameter.

For example (data is simplified):

CREATE TABLE ROLE(
  ID INT(11) NOT NULL AUTO_INCREMENT,
  ROLE_NAME INT,
  DESCRIPTION INT,
  PRIMARY KEY (ID)
)
ENGINE = INNODB;

CREATE TABLE ROLE_PRIVILEGE_BRIDGE(
  ID INT(11) NOT NULL AUTO_INCREMENT,
  PRIVILEGE_FK INT(11) DEFAULT NULL,
  VALUE INT(11) DEFAULT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT FK FOREIGN KEY (PRIVILEGE_FK) REFERENCES ROLE (ID)
)
ENGINE = INNODB;


INSERT INTO ROLE(ROLE_NAME, DESCRIPTION) VALUES(1, 1);
SET @new_id = LAST_INSERT_ID();

INSERT INTO ROLE_PRIVILEGE_BRIDGE(PRIVILEGE_FK, VALUE) VALUES (@new_id, 1);
INSERT INTO ROLE_PRIVILEGE_BRIDGE(PRIVILEGE_FK, VALUE) VALUES (@new_id, 2);
INSERT INTO ROLE_PRIVILEGE_BRIDGE(PRIVILEGE_FK, VALUE) VALUES (@new_id, 3);

Collectives™ on Stack Overflow

inserting values from arrays in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related