MySQL 简单造数

造数背景

主要造出一张时间字段的相关的数据，时间字段不能是一个固定值，数据范围需要随机分散在90天内的。

构建测试表

-- 测试表结构
CREATE TABLE `demo`.`order_info` (
  `ID` bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID主键',
  `ORDER_NO` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '订单编号',
  `GOODS_ID` int DEFAULT NULL COMMENT '商品编号',
  `USER_ID` bigint DEFAULT NULL COMMENT '用户ID',
  `ORDER_AMOUNT` decimal(14,2) DEFAULT NULL COMMENT '订单金额',
  `CREATE_DATE` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '订单创建时间',
  `MODIFY_DATE` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '订单修改时间',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_ORDER_NO` (`ORDER_NO`),
  KEY `NK_MODIFY_DATE` (`MODIFY_DATE`)
);

构建插数存储过程

• 其中ID自增无需指定字段插入
• 订单编号生成唯一值
• 商品编号和用户ID均随机生成，允许生成重复值
• 商品金额随机，保留2位小数
• 创建时间取当前时间减去90天，这个值可根据实际情况调整，一般与修改时间保持联动
• 修改时间取90天内任意值，这个值可根据实际情况调整，一般与创建时间保持联动

-- 插入数据的存储过程
delimiter ;;
  create procedure proc_insert_order_data(in n int)
  begin
    declare i int;
    set i=1;
    while(i<=n)do
      insert into demo.order_info(ORDER_NO,GOODS_ID,USER_ID,ORDER_AMOUNT,CREATE_DATE,MODIFY_DATE) select concat('NET_',uuid_short()) as order_no,
       rpad(floor(rand()*1000),6,0) as GOODS_ID,
       rpad(floor(rand()*100000),8,0) as USER_ID,
       round(rand()*1000,2) as ORDER_AMOUNT,
       date_SUB(now(),interval 90 day) as CREATE_DATE,
       DATE_SUB(now(),interval floor(rand()*90) day) as MODIFY_DATE;
      set i=i+1;
    end while;
  end;;
delimiter ;

根据需要的数据量指定插数

-- 调用存储过程插入需要的数据量
call proc_insert_order_data(100000);

-- 生成的数据示例
select * from order_info limit 10;
+----+--------------------------+----------+----------+--------------+---------------------+---------------------+
| ID | ORDER_NO                 | GOODS_ID | USER_ID  | ORDER_AMOUNT | CREATE_DATE         | MODIFY_DATE         |
+----+--------------------------+----------+----------+--------------+---------------------+---------------------+
|  1 | NET_10764588660109082634 |   658000 | 10694000 |       558.76 | 2022-08-28 11:43:16 | 2022-10-15 11:43:16 |
|  2 | NET_10764588660109082635 |   688000 | 24510000 |        56.45 | 2022-08-28 11:43:38 | 2022-11-08 11:43:38 |
|  3 | NET_10764588660109082636 |   874000 | 74479000 |       101.42 | 2022-08-28 11:43:38 | 2022-11-02 11:43:38 |
|  4 | NET_10764588660109082637 |   590000 | 47827000 |       213.53 | 2022-08-28 11:43:38 | 2022-10-01 11:43:38 |
|  5 | NET_10764588660109082638 |   523000 | 71922000 |        25.52 | 2022-08-28 11:43:38 | 2022-08-31 11:43:38 |
|  6 | NET_10764588660109082639 |   772000 | 95517000 |       456.94 | 2022-08-28 11:43:38 | 2022-10-20 11:43:38 |
|  7 | NET_10764588660109082640 |   724000 | 36747000 |       662.40 | 2022-08-28 11:43:38 | 2022-11-08 11:43:38 |
|  8 | NET_10764588660109082641 |   600000 | 67484000 |       192.07 | 2022-08-28 11:43:38 | 2022-09-03 11:43:38 |
|  9 | NET_10764588660109082642 |   102000 | 70681000 |       225.51 | 2022-08-28 11:43:38 | 2022-11-26 11:43:38 |
| 10 | NET_10764588660109082643 |   359000 | 77415000 |       793.43 | 2022-08-28 11:43:38 | 2022-09-29 11:43:38 |
+----+--------------------------+----------+----------+--------------+---------------------+---------------------+