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

2次阅读

共计 9825 个字符，预计需要花费 25 分钟才能阅读完成。

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

创立工夫要比当初工夫小，并且在肯定的范畴随机
更新工夫要比当初工夫大，并且在肯定的范畴随机
数据不能是变化无穷的，要有随机变动的特点
例如 status 这种 char(1)，数据只有 0 或者 1 的字段，不能呈现非法数据
……

在数据库服务器上安装 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 这一行，并在文件最初加上一行 文件结尾标识

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;
......
......
......

正文完

mysql

发表至： mysql

2022-09-18

0

关于mysql:MySQL-8-远程-root-访问qbit

关于mysql:Mysql8-递归查询

关于mysql:给新手学习MySQL的建议

关于mysql:数据库系统概论王珊第一章绪论习题

关于android:Android中Activity的工作过程二

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

背景

开始

创立长期表 tmp_table

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

编写 Java 代码，生成 sql 语句

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

站内搜索

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

背景

开始

创立长期表 tmp_table

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

编写 Java 代码，生成 sql 语句

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

站内搜索

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