mysql存储过程

1.建表

CREATE TABLE test (
 id BIGINT (20) NOT NULL auto_increment COMMENT 'id',
 order_id BIGINT (20) NOT NULL COMMENT '订单ID',
 user_id BIGINT (64) NOT NULL COMMENT '用户编号',
 STATUS CHAR (1) NOT NULL COMMENT '状态（0交易成功 1交易失败）',
 order_no VARCHAR (64) DEFAULT NULL COMMENT '订单流水',
 CREATE_date datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
 PRIMARY KEY (id)
) ENGINE = INNODB COMMENT = '订单信息表';

2. 存过

-- 定义标识符为双斜杠
delimiter //  
-- 如果存在 my_procedure 存储过程则删除
DROP PROCEDURE IF EXISTS my_procedure ; 
-- 创建无参存储过程
CREATE PROCEDURE my_procedure () 
BEGIN

 -- 申明变量n
    DECLARE n INT DEFAULT 1 ; 
    set @execSql='insert into test (id,order_id,user_id,STATUS,order_no)  values';
    set @execdata = '';

    WHILE n <= 1001 DO
        set @execdata=concat(@execdata,"(",n,',',n,',',n,',',0,','"'order_no'",")");

        if n%1000=0
        then
            set @execSql = concat(@execSql,@execdata,";");
            #select @execSql;
            prepare stmt from @execSql;
            execute stmt;
            DEALLOCATE prepare stmt;
            commit;

            set @execSql='insert into test (id,order_id,`user_id`,`STATUS`,`order_no`)  values';
            set @execdata = '';
        ELSE
            set @execdata = concat(@execdata,',');
        end if;
  -- 循环一次,i加一
        SET n = n + 1 ; 
    END WHILE ; 
 -- 结束while循环
   
END
//　　　　　　　　　　　　　
delimiter ;

 -- 调用存储过程
call my_procedure();