1

I have the following query:

INSERT INTO major (key_1, key_2, ... , key_N) 
VALUES (v_1_1, v_1_2, v_1_3, ... , c_1_N)
       (v_2_1, v_2_2, v_2_3, ... , c_2_N)
       ...
       (v_K_1, v_K_2, v_K_3, ... , c_K_N)

where key_i is name of column, and v_i_j is random value from 1 to L. N,L and K are positive integers (our input).

I am going to write mysql procedure that create random (with values from 1 to L) matrices* (KxN) and place it to database using query presented above.


Now It is realised by code in php and doctrine but I want to achieve the same result in pure SQL procedure that as input gets (N,L,K). Theres is php code:

$conn = DriverManager::getConnection($connectionParams);
$conn->beginTransaction();
try {
    for ($i = 1; $i <= $k; $i++) {     // row in table major
        $content=[];
        for ($j = 1; $j <= $n; $j++) {   // foreign key of row
            $content['key_' . $j] = rand(1, $l);
        }
        $conn->insert('major', $content);
    }
    $conn->commit();
} catch(\Exception $e) {
    $conn->rollBack();
    throw $e;
}

*I wrote matrices but I know that without order of rows it is not matrix in strict sense.


So I do not want to make query inside loop, but loop inside query. I know it is bad practice, but I am thinking about concatenating and evaluating query inside of procedure? It is possible? Or is these better solution?

1
  • 2
    Stored Procedure code can do looping, but it is messier than using PHP (etc). With MariaDB 10, you could have a pseudo table seq_1_to_20 which contains all the integers from 1 to 20. (etc) That might be much simpler. – Rick James Jan 12 '17 at 5:27
1

I solved this writhing the following code:

drop procedure if exists populate;
delimiter $$
CREATE PROCEDURE populate (IN n INT, IN l INT, IN k INT)
BEGIN
  declare _k int unsigned DEFAULT 0;
  declare _n int unsigned DEFAULT 0;
  declare _l int unsigned DEFAULT 0;
  DECLARE _stmt TEXT DEFAULT 'INSERT INTO major_1 (';

  WHILE _n < n DO set _n=_n+1;
    set _stmt = CONCAT(_stmt,'minor_',_n,'_id');
    IF _n<n THEN set _stmt = CONCAT(_stmt,','); END IF;
  END WHILE;
  set _stmt = CONCAT(_stmt,') VALUES ');

  while _k < k do set _k=_k+1;
    set _stmt = CONCAT(_stmt,'(');
    set _n=0; WHILE _n < n do set _n=_n+1;
      SET _stmt = CONCAT(_stmt,CEIL(RAND()*l));
      IF _n<n THEN set _stmt = CONCAT(_stmt,','); END IF;
    END WHILE;
    IF _k<k THEN set _stmt = CONCAT(_stmt,'),');
    ELSE set _stmt = CONCAT(_stmt,');'); END IF;
  end while;

# code to debug:
# SELECT n,l,k,_stmt;

  SET @STMT = _stmt;
  PREPARE stmt FROM @stmt;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$
delimiter ;

And this can be used (for N=20,L=25,K=3) by command:

CALL populate(20,25,3);

Sources:

Loops in MySQL

https://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql

Execution of text variable as query

https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.