-- 创建表,多加了几个字段为了占用空间填充
CREATE TABLE `t_loan_order` (
`app_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '工单ID',
`customer_id` bigint(20) NOT NULL COMMENT '客户ID',
`customer_name` varchar(20) DEFAULT NULL COMMENT '客户姓名',
`customer_certid` varchar(18) DEFAULT NULL COMMENT '客户身份证号',
`phone_no` varchar(15) DEFAULT NULL COMMENT '手机号码',
`purpose` varchar(30) DEFAULT NULL COMMENT '借款用途',
`app_status` int(11) DEFAULT NULL COMMENT '工单状态',
`product_name` varchar(255) DEFAULT NULL COMMENT '产品名称',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`app_id`),
KEY `union_idx_id_status` (`app_id`,`app_status`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='工单表';
-- 批量造100万数据
DROP PROCEDURE IF EXISTS `batch_insert`;
CREATE PROCEDURE `batch_insert`(IN `num` int)
BEGIN
declare i int default 0;
set autocommit= 0;
while i<num do
set i=i+1;
insert into t_loan_order(`customer_id`,`customer_name`,`customer_certid`,`phone_no`,`purpose`,`app_status`,`product_name`,`create_time`) values(RAND()*100000+1000,'用户姓名占位','110128199111112325','15650000000','冲动消费',5,'消费贷',now()-interval i second);
end while;
commit;
END;
-- 将状态打乱
UPDATE t_loan_order t SET t.app_status=6 where t.app_id%2=0;