Mysql 存储过程 自动插入数据

参考:https://blog.csdn.net/yuzfengxu/article/details/79260365

 

1. 表结构

CREATE TABLE `zc2_ticket_orderticket` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `acid` int(11) NOT NULL COMMENT '商户ID',
  `order_no` varchar(64) NOT NULL DEFAULT '' COMMENT '订单号',
  `ticket_no` varchar(64) NOT NULL DEFAULT '' COMMENT '车票号',
  `ticket_type` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1全票；2半票；3儿童票；9网上成人单程;10网上成人双程;13网上小童双程;14网上小童单程；17网上长者 65 岁单程；18网上长者 65 岁双程',
  `passenger_name` varchar(64) NOT NULL DEFAULT '' COMMENT '乘客姓名',
  `passenger_id` varchar(64) DEFAULT NULL COMMENT '乘客证件号',
  `passenger_id_type` tinyint(4) DEFAULT NULL COMMENT '1身份证2港澳回乡证3台湾通行证4护照5军官证6居住证',
  `passenger_tel` varchar(13) NOT NULL DEFAULT '' COMMENT '手机号码',
  `status` tinyint(11) NOT NULL COMMENT '0已锁票；1已购票；2已退票；3已检票；4已过期；5已撤销',
  `fee` decimal(10,2) DEFAULT '0.00' COMMENT '票价',
  `discount_fee` decimal(10,2) DEFAULT '0.00' COMMENT '中港通接口返回的折扣价',
  `order_time` int(10) NOT NULL COMMENT '购票时间，unix时间戳',
  `refund_time` int(10) DEFAULT NULL COMMENT '退票时间，unix时间戳',
  `refund_time2` int(11) NOT NULL DEFAULT '0' COMMENT '退款到账时间',
  `refund_fee` decimal(10,2) DEFAULT NULL COMMENT '退款金额',
  `refund_rate` varchar(10) NOT NULL DEFAULT '' COMMENT '退票费率',
  `insurance_refund_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '保险退款金额',
  `check_in_channel` char(1) NOT NULL DEFAULT '' COMMENT '检票渠道:1车载机 2后台 3乘客端',
  `check_in_user` varchar(32) NOT NULL DEFAULT '' COMMENT '检票渠道 车载机:车牌号 后台:管理员姓名 乘客端:乘客姓名',
  `check_in_time` int(11) NOT NULL DEFAULT '0' COMMENT '检票时间',
  `start_station_id` varchar(32) DEFAULT NULL COMMENT '出发站编号',
  `start_station` varchar(32) DEFAULT NULL COMMENT '出发站名称',
  `end_station_id` varchar(32) DEFAULT NULL COMMENT '目的站编号',
  `end_station` varchar(32) DEFAULT NULL COMMENT '目的站名称',
  `sch_date` varchar(10) DEFAULT NULL COMMENT '班次日期',
  `sch_time` varchar(8) DEFAULT NULL COMMENT '班次时间',
  `sch_code` varchar(64) DEFAULT NULL COMMENT '班次编号',
  `trip` int(11) DEFAULT NULL COMMENT '0.去程 1.返程',
  `seat` int(10) DEFAULT '0' COMMENT '座位号',
  `gate` varchar(64) DEFAULT '' COMMENT '检票口',
  `insurance_number` varchar(50) DEFAULT '' COMMENT '保险单号',
  `insurance_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '保险金额',
  `refund_insurance_price` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '退保金额',
  `insurance_status` tinyint(4) DEFAULT '0' COMMENT '保险状态 0：未投保 1：已投保 2：已退保',
  `havechilden` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '是否携带免票儿童：0 未携带  1：携带',
  `children_name` varchar(50) DEFAULT '' COMMENT '儿童姓名',
  `children_card_no` varchar(32) DEFAULT '' COMMENT '儿童证件号码',
  `children_card_type` tinyint(4) DEFAULT '2' COMMENT '儿童证件类型 身份证:2、港澳回乡证:3、台湾通行证:4、护照:5、军官证:6、居住证:7',
  PRIMARY KEY (`id`),
  KEY `acid_ticketno` (`acid`,`ticket_no`) USING BTREE,
  KEY `order_no` (`order_no`)
) ENGINE=InnoDB AUTO_INCREMENT=2847 DEFAULT CHARSET=utf8;

2.  创建存储过程

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `autoinsert_orderticket`$$
 
CREATE PROCEDURE `autoinsert_orderticket`(IN insertCount INT)
BEGIN
  DECLARE count INT DEFAULT 0;
   WHILE(count < insertCount)
    DO
        INSERT INTO `zc2-dev`.`zc2_ticket_orderticket` (`acid`, `order_no`, `ticket_no`, `ticket_type`, `passenger_name`, `passenger_id`, `passenger_id_type`, `passenger_tel`, `status`, `fee`, `discount_fee`, `order_time`, `refund_time`, `refund_time2`, `refund_fee`, `refund_rate`, `insurance_refund_fee`, `check_in_channel`, `check_in_user`, `check_in_time`, `start_station_id`, `start_station`, `end_station_id`, `end_station`, `sch_date`, `sch_time`, `sch_code`, `trip`, `seat`, `gate`, `insurance_number`, `insurance_price`, `refund_insurance_price`, `insurance_status`, `havechilden`, `children_name`, `children_card_no`, `children_card_type`) VALUES ('6', '201902131040404214yy', '0007068748yt', '1', '1ytutyu', '3463496794654', '5', '18252072', '1', '42.00', '0.00', '1025640', NULL, '0', NULL, '', '0.00', '', '', '0', '102114', '南汽车站', '10284', '北汽车总站', '2019-02-16', '18:10', '1102504', NULL, '31', '3号检票口', '', '0.00', '0.00', '0', '0', '', '', '0');
        SET count = count+1;
    END WHILE;
    END$$
DELIMITER;

 

3.调用

call autoinsert_orderticket(10000);

 

4.查看存储过程或者函数信息

SELECT	* FROM information_schema.Routines

 

5.删除存储过程

DROP PROCEDURE IF EXISTS `autoinsert_orderticket`