MySQLで複数件のINSERTを実行する #MySQL

自分用。プロシージャを初めて使ったので


DELIMITER //
CREATE PROCEDURE bulk_inserter(
  IN myName VARCHAR(128),
  IN dt VARCHAR(128),
  IN x INT
)
BEGIN
  DECLARE i INT;
  DECLARE dy VARCHAR(128);
  DECLARE hr VARCHAR(128);

  SET i = 0;
  SET dy = DATE_FORMAT(dt, '%d');
  SET hr = TIME_FORMAT(dt, '%H');

  WHILE i < x do
    set i = i + 1;
    INSERT INTO my_table_name (
      name, created_at, updated_at
    )
    VALUES (
      myName, dt, dt
    );
  END WHILE;
END
//

DELIMITER ;

BEGIN;
CALL bulk_inserter('hello world','2020-09-06 16:25:00',10);
COMMIT;

DROP PROCEDURE bulk_inserter;