存储过程建立临时数据

\d $$
  DROP PROCEDURE IF EXISTS insert_test_data;

  ##num_limit 要插入数据的数量,max_user_id 最大随机的数值
  CREATE PROCEDURE insert_test_data(in num_limit int,in max_user_id int)

  BEGIN
 
  DECLARE id int default 1;
  DECLARE asso_type int default 1;
  DECLARE asso_id int default 1;
  DECLARE user_id int default 1;
  DECLARE notice_type int default 1;
  DECLARE is_read int default 1;
  DECLARE create_ts int default 0;
  DECLARE update_ts int default 0;
 
  WHILE id <= num_limit do

  set asso_type = FLOOR(1 + rand() * 3);
  set asso_id = FLOOR(1 + rand() * 692051);
  set user_id = FLOOR(1 + rand() * max_user_id);
  set notice_type = FLOOR(21 + rand() * 3);
  set is_read = FLOOR(rand() * 2);
  set create_ts = unix_timestamp();
  set update_ts = unix_timestamp();

  INSERT INTO test . notices values (id, asso_type, asso_id, user_id, notice_type, is_read, create_ts, update_ts);

  set id = id + 1;
   
  END WHILE;
 
END$$
\d;

call insert_test_data(86556571, 113702);