mysql快速生成百万级测试数据

来源：恒创科技编辑：恒创科技编辑部

2023-12-22 09:40:59

背景

最近在跟着学习一套开源的电商项目，建了一个项目库所需的数据库。表是有了，但是数据没有。一个电商项目怎么能没有多一点的数据呢，于是决定自己一个表造个几十万数据（总共71个表）。对于假数据只有几点简单的要求：

创建时间要比现在时间小，并且在一定的范围随机更新时间要比现在时间大，并且在一定的范围随机数据不能是一成不变的，要有随机变化的特点例如status这种char(1)，数据只有0或者1的字段，不能出现非法数据......开始创建临时表tmp_table在数据库服务器上安装python环境

在数据库服务器上执行命令，生成内容为1~100000的文件(

# 这里100000是你一个表的数据量级，你可以自行设定
python -c "for i in range(1, 1+100000): print(i)" > mall.txt

登录mysql，并执行

-- 选择自己的数据库
use mall
-- 加载mall.txt中的数据到tmp_table表中
load data infile 'mall.txt' replace into table tmp_table;

导出数据库所有表、字段信息

执行sql语句

SELECT

 TABLE_NAME AS '表名',

 COLUMN_NAME AS '列名',

 COLUMN_DEFAULT AS '默认值',

 COLUMN_TYPE AS 列类型

FROM

 information_schema.`COLUMNS`

WHERE

 TABLE_SCHEMA = 'mall'

ORDER BY

 TABLE_NAME,

 ORDINAL_POSITION
INTO OUTFILE '/var/lib/mysql-files/mallFour.text';

其中TABLE_SCHEMA=后修改为自己的库名，INTO OUTFILE '/var/lib/mysql-files/mallFour.text'中的mallFour.text修改为自己的文件名称（但是路径不要修改）。

下面我生成的数据文件：

ums_role    id    \N    bigint(20)
ums_role    name    \N    varchar(100)
ums_role    description    \N    varchar(500)
ums_role    admin_count    \N    int(11)
ums_role    status    1    int(1)
ums_role    sort    0    int(11)
ums_role    create_user    \N    varchar(50)
ums_role    create_time    \N    datetime
ums_role    update_user    \N    varchar(50)
ums_role    update_time    \N    datetime
ums_role    is_delete    0    char(1)
ums_role_permission_relation    id    \N    bigint(20)
ums_role_permission_relation    role_id    \N    bigint(20)
ums_role_permission_relation    permission_id    \N    bigint(20)
ums_role_permission_relation    create_user    \N    varchar(50)
ums_role_permission_relation    create_time    \N    datetime
ums_role_permission_relation    update_user    \N    varchar(50)
ums_role_permission_relation    update_time    \N    datetime
ums_role_permission_relation    is_delete    0    char(1)
......
......
......

将生成的表、字段信息文件mallFour.text，下载到本地删除mallFour.text文件中的tmp_table这一行，并在文件最后加上一行文件结尾标识编写Java代码，生成sql语句

package com.liboshuai.mall.tiny.utils;

import cn.hutool.core.io.FileUtil;
import com.liboshuai.mall.tiny.common.enums.AutoMysqlEnum;

import java.util.*;
import java.util.stream.Collectors;

/**
 * @Author: liboshuai
 * @Date: 2022-09-17 15:51
 * @Description: 随机生成mysql数据工具类
 */
public class AutoMysqlUtil {

    private static final String TMP_TABLE = "tmp_table";
    private static final String FILE_END_FLAG = "文件结尾标识";

    public static void main(String[] args) {
        // 刚才下载到本地的mysql表、字段数据文件地址
        List<String> readLines = FileUtil.readLines("C:\\Users\\李博帅\\Desktop\\mall03.txt", "UTF-8");
        // 拼接 "INSERT INTO ? SELECT content FROM tmp_table" + tableName 部分
        List<String> tableNameList = readLines.stream()
                .map(readLine -> readLine.split("\\s+")[0])
                .collect(Collectors.toList());
        Map<String, List<String>> tableNameMap = tableNameList.stream().collect(Collectors.groupingBy(String::toString));
        Set<String> tableNameSet = tableNameMap.keySet();
        List<StringBuilder> truncateTableMysqlList = tableNameSet.stream()
                .filter(tableName -> !Objects.equals(tableName, TMP_TABLE) && !tableName.startsWith(FILE_END_FLAG))
                .map(tableName -> new StringBuilder("truncate table ")
                        .append(tableName).append(";"))
                .collect(Collectors.toList());
        List<String> columnNameList = readLines.stream()
                .map(readLine -> readLine.split("\\s+")[1])
                .collect(Collectors.toList());
        List<String> typeList = readLines.stream()
                .map(readLine -> readLine.split("\\s+")[3])
                .collect(Collectors.toList());
        List<StringBuilder> mysqlList = new ArrayList<>();
        String tableName = tableNameList.get(0);
        int flag = 0;
        for (int i = 0; i < tableNameList.size(); i++) {
            String tableNameTemp = tableNameList.get(i);
            boolean equalsOne = Objects.equals(tableNameTemp, tableName);
            boolean equalsThree = Objects.equals(tableNameTemp, TMP_TABLE);
            if (!equalsOne &&!equalsThree) {
                    List<String> columnNameTempList = new ArrayList<>();
                    List<String> typeTempList = new ArrayList<>();
                    for (int j = flag; j < i; j++) {
                        String columnName = columnNameList.get(j);
                        String type = typeList.get(j);
                        columnNameTempList.add(columnName);
                        typeTempList.add(type);
                    }
                    StringBuilder mysql = new StringBuilder()
                            .append("INSERT INTO ").append(tableName)
                            .append(" SELECT ");
                    for (int j = 0; j < columnNameTempList.size(); j++) {
                        String columnName = columnNameTempList.get(j);
                        String type = typeTempList.get(j);
                        if (Objects.equals(columnName, AutoMysqlEnum.ID.getKey())) {
                            mysql.append(AutoMysqlEnum.ID.getValue());
                        } else if (Objects.equals(columnName, AutoMysqlEnum.CREATE_TIME.getKey())) {
                            mysql.append(AutoMysqlEnum.CREATE_TIME.getValue());
                        } else if (Objects.equals(columnName, AutoMysqlEnum.CREATE_USER.getKey())) {
                            mysql.append("CONCAT( '").append(AutoMysqlEnum.CREATE_USER.getValue()).append("', id ), ");
                        } else if (Objects.equals(columnName, AutoMysqlEnum.UPDATE_USER.getKey())) {
                            mysql.append("CONCAT( '").append(AutoMysqlEnum.UPDATE_USER.getValue()).append("', id ), ");
                        } else if (Objects.equals(columnName, AutoMysqlEnum.UPDATE_TIME.getKey())) {
                            mysql.append(AutoMysqlEnum.UPDATE_TIME.getValue());
                        } else {
                            if (type.startsWith(AutoMysqlEnum.BIGINT.getKey())) {
                                mysql.append(AutoMysqlEnum.BIGINT.getValue());
                            } else if (type.startsWith(AutoMysqlEnum.VARCHAR.getKey())
                                    || type.startsWith(AutoMysqlEnum.VARBINARY.getKey())
                                    || type.startsWith(AutoMysqlEnum.TEXT.getKey())) {
                                mysql.append("CONCAT( '").append(columnName).append("', id ), ");
                            } else if (type.startsWith(AutoMysqlEnum.INT1.getKey())) {
                                mysql.append(AutoMysqlEnum.INT1.getValue());
                            } else if (type.startsWith(AutoMysqlEnum.INT2.getKey())) {
                                mysql.append(AutoMysqlEnum.INT2.getValue());
                            } else if (type.startsWith(AutoMysqlEnum.INT.getKey())) {
                                mysql.append(AutoMysqlEnum.INT.getValue());
                            } else if (type.startsWith(AutoMysqlEnum.DECIMAL.getKey())) {
                                mysql.append(AutoMysqlEnum.DECIMAL.getValue());
                            } else if (type.startsWith(AutoMysqlEnum.DATE.getKey())) {
                                mysql.append(AutoMysqlEnum.DATE.getValue());
                            } else if (type.startsWith(AutoMysqlEnum.DATETIME.getKey())) {
                                mysql.append(AutoMysqlEnum.DATETIME.getValue());
                            } else if (type.startsWith(AutoMysqlEnum.TIME.getKey())) {
                                mysql.append(AutoMysqlEnum.TIME.getValue());
                            } else if (type.startsWith(AutoMysqlEnum.CHAR.getKey())) {
                                mysql.append(AutoMysqlEnum.CHAR.getValue());
                            }
                        }
                    }
                    mysqlList.add(mysql);
                    tableName = tableNameList.get(i);
                    flag = i;
            }
        }
        mysqlList = mysqlList.stream()
                .map(mysql -> mysql.substring(0, mysql.length() - 2))
                .map(mysql -> new StringBuilder(mysql).append(" FROM tmp_table;"))
                .collect(Collectors.toList());
        truncateTableMysqlList.addAll(mysqlList);
        // mysql文件生成的地址
        FileUtil.writeLines(truncateTableMysqlList, "C:\\Users\\李博帅\\Desktop\\mallMysql.sql", "UTF-8");
    }
}

package com.liboshuai.mall.tiny.common.enums;

import lombok.AllArgsConstructor;
import lombok.Getter;

/**
 * @Author: liboshuai
 * @Date: 2022-09-17 18:51
 * @Description: 自动生成mysql
 */
@Getter
@AllArgsConstructor
@SuppressWarnings("AlibabaEnumConstantsMustHaveComment")
public enum AutoMysqlEnum {
//    [date, int(2), int(1), varchar(128), varchar(8), char(1), int(11), varchar(16), varchar(32), bigint(11), varchar(50), datetime, bigint(20), varchar(64), varbinary(500), time, decimal(10,2), text, varchar(1000), varchar(100), varchar(255), varchar(200), int(3), varchar(500)]

    // 按照类型
    BIGINT("bigint", "FLOOR( Rand() * 100000), "),
    INT1("int(1)", "FLOOR(0 +  Rand() * 2), "),
    INT2("int(2)", "FLOOR( Rand() * 100), "),
    INT("int", "FLOOR( Rand() * 10000), "),
    DECIMAL("decimal", "FLOOR( Rand() * 100000), "),
    DATE("date", "ADDDATE(ADDDATE(ADDDATE('2018-01-01', INTERVAL FLOOR(RAND() * 8) YEAR),INTERVAL FLOOR(RAND() * 12) MONTH),INTERVAL FLOOR(RAND() * 31) DAY), "),
    DATETIME("datetime", "ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE('2018-01-01 00:00:00', INTERVAL FLOOR(RAND() * 8) YEAR),INTERVAL FLOOR(RAND() * 12) MONTH),INTERVAL FLOOR(RAND() * 31) DAY), INTERVAL FLOOR(RAND() * 24) HOUR),INTERVAL FLOOR(RAND() * 60) MINUTE),INTERVAL FLOOR(RAND() * 60) SECOND), "),
    TIME("time", "'00:00:00', "),
    VARCHAR("varchar", "NULL, "),
    CHAR("char", "'0', "),
    VARBINARY("varbinary", "NULL, "),
    TEXT("text", "NULL, "),

    // 按照列名
    ID("id", "id, "),
    CREATE_TIME("create_time", "ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE('2022-09-17 22:00:00', INTERVAL FLOOR(1 -(RAND() * 4)) YEAR),INTERVAL FLOOR(1 - (RAND() * 12)) MONTH),INTERVAL FLOOR(1 - (RAND() * 31)) DAY),INTERVAL FLOOR(1 - (RAND() * 24)) HOUR),INTERVAL FLOOR(1 - RAND() * 60) MINUTE),INTERVAL FLOOR(1 - (RAND() * 60)) SECOND), "),
    CREATE_USER("create_user", "admin"),
    UPDATE_TIME("update_time", "ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE('2022-09-17 22:00:00', INTERVAL FLOOR(1 + (RAND() * 4)) YEAR),INTERVAL FLOOR(1 + (RAND() * 12)) MONTH),INTERVAL FLOOR(1 + (RAND() * 31)) DAY),INTERVAL FLOOR(1 + (RAND() * 24)) HOUR),INTERVAL FLOOR(1 + RAND() * 60) MINUTE),INTERVAL FLOOR(1 + (RAND() * 60)) SECOND), "),
    UPDATE_USER("update_user", "lbs");


    private String key;
    private String value;

}

执行刚才生成的mysql语句文件mallMysql.sql，最终生成数据

......
......
......
truncate table pms_brand;
truncate table pms_comment;
truncate table ums_member;
truncate table ums_admin_login_log;
truncate table oms_cart_item;
truncate table sms_home_recommend_subject;
truncate table sms_home_new_product;
truncate table sms_flash_promotion_session;
truncate table cms_subject_product_relation;
truncate table pms_product_attribute_value;
truncate table oms_order_operate_history;
truncate table ums_member_login_log;
INSERT INTO cms_help SELECT id, FLOOR( Rand() * 100000), CONCAT( 'icon', id ), CONCAT( 'title', id ), FLOOR(0 +  Rand() * 2), FLOOR(0 +  Rand() * 2), CONCAT( 'content', id ), CONCAT( 'admin', id ), ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE('2022-09-17 22:00:00', INTERVAL FLOOR(1 -(RAND() * 4)) YEAR),INTERVAL FLOOR(1 - (RAND() * 12)) MONTH),INTERVAL FLOOR(1 - (RAND() * 31)) DAY),INTERVAL FLOOR(1 - (RAND() * 24)) HOUR),INTERVAL FLOOR(1 - RAND() * 60) MINUTE),INTERVAL FLOOR(1 - (RAND() * 60)) SECOND), CONCAT( 'lbs', id ), ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE('2022-09-17 22:00:00', INTERVAL FLOOR(1 + (RAND() * 4)) YEAR),INTERVAL FLOOR(1 + (RAND() * 12)) MONTH),INTERVAL FLOOR(1 + (RAND() * 31)) DAY),INTERVAL FLOOR(1 + (RAND() * 24)) HOUR),INTERVAL FLOOR(1 + RAND() * 60) MINUTE),INTERVAL FLOOR(1 + (RAND() * 60)) SECOND), '0' FROM tmp_table;
INSERT INTO cms_help_category SELECT id, CONCAT( 'name', id ), CONCAT( 'icon', id ), FLOOR( Rand() * 10000), FLOOR( Rand() * 100), FLOOR( Rand() * 10000), CONCAT( 'admin', id ), ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE('2022-09-17 22:00:00', INTERVAL FLOOR(1 -(RAND() * 4)) YEAR),INTERVAL FLOOR(1 - (RAND() * 12)) MONTH),INTERVAL FLOOR(1 - (RAND() * 31)) DAY),INTERVAL FLOOR(1 - (RAND() * 24)) HOUR),INTERVAL FLOOR(1 - RAND() * 60) MINUTE),INTERVAL FLOOR(1 - (RAND() * 60)) SECOND), CONCAT( 'lbs', id ), ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE(ADDDATE('2022-09-17 22:00:00', INTERVAL FLOOR(1 + (RAND() * 4)) YEAR),INTERVAL FLOOR(1 + (RAND() * 12)) MONTH),INTERVAL FLOOR(1 + (RAND() * 31)) DAY),INTERVAL FLOOR(1 + (RAND() * 24)) HOUR),INTERVAL FLOOR(1 + RAND() * 60) MINUTE),INTERVAL FLOOR(1 + (RAND() * 60)) SECOND), '0' FROM tmp_table;
......
......
......

本文地址：https://www.henghost.com/jishu/12218/