表结构

  1. -- auto-generated definition
  2. create table t_emc_service_order
  3. (
  4.     f_id                 int auto_increment comment '主键id'
  5.         primary key,
  6.     f_order_no           varchar(32)                           not null comment '服务单号',
  7.     f_user_id            varchar(45)                           not null comment '用户id',
  8.     f_user_tel           varchar(11)                           not null comment '用户手机号',
  9.     f_service_id         int                                   not null comment '服务id',
  10.     f_order_status       tinyint                               not null comment '服务单状态（1-初始化，2-未使用，3-使用中，4-已过期，5-退款中，6-已退款，9-作废）',
  11.     f_mall_order_no      varchar(32)                           null comment '商城主订单号',
  12.     f_mall_skucode       varchar(32)                           null comment '商城sku码',
  13.     f_service_rank       varchar(16) default ''                not null comment '会员服务类型（general-大众，silver-白银，gold-黄金，platinum-铂金，diamond-钻石）',
  14.     f_purchase_amount    decimal(9, 2)                         null comment '服务购买金额',
  15.     f_accruing_amount    decimal(9, 2)                         null comment '累计已省金额',
  16.     f_service_start_time datetime                              null comment '服务开始时间',
  17.     f_service_end_time   datetime                              null comment '服务结束时间',
  18.     f_created_time       datetime    default CURRENT_TIMESTAMP not null comment '创建时间',
  19.     f_modified_time      datetime    default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间'
  20. )
  21.     comment '用户购买服务单表';
  25. -- auto-generated definition
  26. create table t_emc_privilege_record
  27. (
  28.     f_id                   int auto_increment comment '主键id'
  29.         primary key,
  30.     f_user_id              varchar(45)                        not null comment '用户id',
  31.     f_order_no             varchar(32)                        not null comment '服务单号',
  32.     f_sub_service_id       int                                not null comment '子服务id',
  33.     f_item_id              int                                not null comment '服务明细单id',
  34.     f_privilege_id         int                                not null comment '权益id',
  35.     f_coupon_rule_id       varchar(32)                        not null comment '券规则id',
  36.     f_received_status      tinyint                            not null comment '权益状态（1-待下发，2-已下发，3-已领取，4-已使用，5-已失效）',
  37.     f_coupon_id            varchar(32)                        null comment '用户领取的券id',
  38.     f_acquire_time         datetime                           null comment '权益领取时间',
  39.     f_privilege_start_time datetime                           null comment '权益生效时间',
  40.     f_privilege_end_time   datetime                           null comment '权益到期时间',
  41.     f_created_time         datetime default CURRENT_TIMESTAMP not null comment '创建时间',
  42.     f_modified_time        datetime default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间'
  43. )
  44.     comment '用户权益兑换记录表';
  46. -- auto-generated definition
  47. create table t_emc_user_info
  48. (
  49.     f_id                int auto_increment comment '主键id'
  50.         primary key,
  51.     f_user_id           varchar(45)                           not null comment '用户id',
  52.     f_user_name         varchar(32) default ''                not null comment '用户姓名',
  53.     f_is_member         tinyint(1)                            not null comment '是否会员（0-否，1-是）',
  54.     f_user_rank         varchar(16) default ''                not null comment '会员身份（super-超级会员，general-大众会员，silver-白银会员，gold-黄金会员，platinum-铂金会员，diamond-钻石会员）',
  55.     f_member_start_time datetime                              not null comment '会员开始时间',
  56.     f_member_end_time   datetime                              not null comment '会员结束时间',
  57.     f_created_time      datetime    default CURRENT_TIMESTAMP not null comment '创建时间',
  58.     f_modified_time     datetime    default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment '修改时间',
  59.     constraint idx_userinfo_userid
  60.         unique (f_user_id)
  61. )
  62.     comment '用户信息表';

用户信息表通过userId和订单表关联
订单表通过订单号orderNo和权益记录表关联

存储过程

  1. delimiter $
  2. create function generateTestJobData(number int) #--创建一个函数名为bigInsto  入参为(参数名,数据类型)
  3.     returns varchar(200)#--返回数据，数据类型
  4. begin
  5.     #--函数体开始
  6.     declare rand_str varchar(150) default 'qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM0123456789'; #--定义一个rand_str的变量 数据类型，默认值
  7.     declare rand_orderStatus varchar(100) default '23456';
  8.     declare coupon_ids varchar(100) default '594741,594746,594749,594750,594751,594753,594754,594757,594760,594774,594782';
  9.     declare countNumber int default 1;
  10.     declare perNumber int default 1;
  11.     declare orderNo varchar(150) default NULL;
  12.     declare userId varchar(150) default NULL;
  13.     declare serviceId int(4) default NULL;
  14.     declare statusId int(4) default NULL;
  15.     declare preStatus int default 5;
  16.     declare coupon_id varchar(32) default null;
  17.     declare memberMark tinyint(1) default 0;
  19.     WHILE countNumber <= number
  20.         DO
  21.             SET userId = REPLACE(UUID(), '-', '');
  22.             SET orderNo = CONCAT('EMC-JOB', SUBSTRING(UPPER(REPLACE(UUID(), '-', '')), 1, 15));
  23.             SET serviceId = CEILING(RAND() * 500000 + 500000);
  24.             SET statusId = SUBSTRING(rand_orderStatus, ceil(rand() * 4), 1);
  26.             IF statusId = 2 THEN
  27.                 SET memberMark = 0;
  28.             ELSEIF statusId = 3 THEN
  29.                 SET memberMark = 1;
  30.             ELSEIF statusId = 5 THEN
  31.                 SET memberMark = 1;
  32.             ELSEIF statusId = 4 THEN
  33.                 SET memberMark = 0;
  34.             ELSEIF statusId = 6 THEN
  35.                 SET memberMark = 0;
  36.             END IF;
  37.             insert into t_emc_user_info(f_user_id, f_user_name, f_is_member, f_user_rank, f_member_start_time,
  38.                                         f_member_end_time)
  39.             VALUES (userId, 'JOB', memberMark, 'super', now(), now());
  40.             #---insert 语句
  41.             insert into t_emc_service_order(f_order_no, f_user_id, f_user_tel, f_service_id, f_order_status,
  42.                                             f_mall_order_no, f_mall_skucode, f_service_rank, f_purchase_amount,
  43.                                             f_accruing_amount, f_service_start_time, f_service_end_time, f_created_time,
  44.                                             f_modified_time)
  45.             VALUES (orderNo, userId,
  46.                     concat('1', substring(cast(3 + (rand() * 10) % 7 AS char(50)), 1, 1),
  47.                            right(left(trim(cast(rand() AS char(50))), 11), 9)),
  48.                     serviceId, statusId, orderNo, concat('sku-', SUBSTRING(rand_str, ceil(rand() * 6), 6)), 'super',
  49.                     null,
  50.                     null,
  51.                     now(), now(), now(), now());
  52.             ## 金融劵
  53.             insert into t_emc_privilege_record(f_user_id, f_order_no, f_sub_service_id, f_item_id, f_privilege_id,
  54.                                                f_coupon_rule_id, f_received_status, f_coupon_id, f_acquire_time,
  55.                                                f_privilege_start_time, f_privilege_end_time, f_created_time,
  56.                                                f_modified_time)
  57.             VALUES (userId, orderNo, serviceId, countNumber, countNumber, '1156', preStatus, '594568', now(), now(),
  58.                     now(), now(), now());
  59.             # 生活劵
  60.             loop_name:
  61.             loop
  62.                 IF perNumber = 7 THEN
  63.                     SET perNumber = 1;
  64.                     leave loop_name; -- 判断条件成立则结束循环  好比java中的 boeak
  65.                 ELSEIF perNumber != 7 THEN
  66.                     SET perNumber = perNumber + 1;
  67.                 END IF;
  69.                 IF statusId = 2 THEN
  70.                     SET preStatus = 1;
  71.                 ELSEIF statusId = 3 THEN
  72.                     SET preStatus = floor(2 + rand() * (5 - 2));
  73.                 ELSEIF statusId = 5 THEN
  74.                     SET preStatus = floor(2 + rand() * (5 - 2));
  75.                 ELSEIF statusId = 4 THEN
  76.                     SET preStatus = 5;
  77.                 ELSEIF statusId = 6 THEN
  78.                     SET preStatus = 5;
  79.                 END IF;
  81.                 IF preStatus = 4 THEN
  82.                     SELECT id
  83.                     INTO coupon_id
  84.                     FROM (
  85.                              SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(coupon_ids, ',', help_topic_id + 1), ',',
  86.                                                     -1) AS id
  87.                              FROM mysql.help_topic
  88.                              WHERE help_topic_id < LENGTH(coupon_ids) - LENGTH(REPLACE(coupon_ids, ',', '')) + 1
  89.                          ) AS str
  90.                     order by RAND()
  91.                     LIMIT 1;
  93.                 END IF;
  94.                 insert into t_emc_privilege_record(f_user_id, f_order_no, f_sub_service_id, f_item_id,
  95.                                                    f_privilege_id, f_coupon_rule_id, f_received_status, f_coupon_id,
  96.                                                    f_acquire_time, f_privilege_start_time, f_privilege_end_time,
  97.                                                    f_created_time, f_modified_time)
  98.                 VALUES (userId, orderNo, serviceId, countNumber, countNumber, CEILING(RAND() * 500000 + 500000),
  99.                         preStatus, coupon_id, now(), now(), now(), now(), now());
  100.             end loop; -- 循环结束
  101.             SET countNumber = countNumber + 1;
  102.         END WHILE;
  103.     return countNumber; #--返回值
  104. end;
  105. #--函数体结束
  107. #查看log_bin_trust_function_creators是否开启：默认关闭 批量插入数据必须为开启状态
  108. show variables like 'log_bin_trust_function_creators';
  109. # 开启
  110. set global log_bin_trust_function_creators = 1;
  112. # 查当前创建的所有自定义函数
  113. show function status;
  114. # 删除自定义的函数
  115. drop function generateTestJobData;
  117. # 生成数据
  118. select generateTestJobData(20000);
  120. # truncate table t_emc_service_order;