背景
最近在跟着学习一套开源的电商我的项目，建了一个我的项目库所需的数据库。表是有了，然而数据没有。一个电商我的项目怎么能没有多一点的数据呢，于是决定本人一个表造个几十万数据（总共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_POSITIONINTO 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 datetimeums_role update_user \N varchar(50)ums_role update_time \N datetimeums_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 datetimeums_role_permission_relation update_user \N varchar(50)ums_role_permission_relation update_time \N datetimeums_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;..................