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

48次阅读

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

背景

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

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

开始

创立长期表 tmp_table

  1. 在数据库服务器上安装 python 环境
  2. 在数据库服务器上执行命令,生成内容为 1~100000 的文件(

    # 这里 100000 是你一个表的数据量级,你能够自行设定
    python -c "for i in range(1, 1+100000): print(i)" > mall.txt
  3. 登录 mysql,并执行

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

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

  1. 执行 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)
    ......
    ......
    ......
    
  2. 将生成的表、字段信息文件 mallFour.text,下载到本地
  3. 删除 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;
......
......
......

正文完
 0