mysql 存储过程 - 创建使用示例

● 测试需求场景描述

  使用存储过程循环插入 N 条数据

● 创建一张表

CREATE TABLE `t_user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `real_name` varchar(20) NOT NULL COMMENT '流水号',
  `sex` tinyint(4) DEFAULT '1' COMMENT '性别：0-女,1-男',
  `mobile` varchar(20) DEFAULT NULL COMMENT '联系电话',
  `email` varchar(64) DEFAULT NULL COMMENT '电子邮箱',
  `address` varchar(128) DEFAULT NULL COMMENT '地址',
  `money` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '存款',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';

-- UNIQUE KEY `unidx_mobile` (`mobile`)

● 创建一个插入数据的存储过程

-- 删除存储过程，防止重复： DROP PROCEDURE IF EXISTS procedure_name;
DROP PROCEDURE IF EXISTS batchInsertProcedure;

-- 创建
delimiter ;;
create procedure batchInsertProcedure (in amount int)
begin
    declare realName varchar(20) default '张三';
    declare sex tinyint(4) default '1';
    declare mobile varchar(20) default '177';
    declare email varchar(64) default '@sina.cn';
    declare address varchar(128) default '上海';
    declare money decimal(12,2) default '1000.00';
    declare insert_result int default 0;
    declare i int default 1;
    -- declare insert_log varchar(255) default 'insert log:';
    declare continue_handler int default 0;
    declare continue handler for SQLEXCEPTION set continue_handler = 1;
    -- declare exit handler for SQLEXCEPTION rollback;
    set i = 1;
    start transaction;
    while i <= amount do

      set realName = concat('张三', i);
      set sex = i%2;
      set mobile = concat('177', i);
      set email = concat(i, '@sina.cn');
      set money = i + 1000;

      if i%2=0 then
       set address = "上海";
      else
       set address = "北京";
      end if;

      insert into `t_user`(`real_name`,`sex`,`mobile`,`email`,`address`,`money`) 
      values (realName, sex, mobile, email, address, money);

      set i = i + 1;
    end while;
    -- 异常逻辑
    if continue_handler = 1 then
       rollback;
    else
       commit;
    end if;
end ;;
delimiter ;


● 调用存储过程
call batchInsertProcedure (10);

● 查询调用结果
select * from `t_user`;

● 查看存储过程状态
show procedure status like 'batchInsertProcedure';

● 性能参数查看：

SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';