背景
最近在跟着学习一套开源的电商项目，建了一个项目库所需的数据库。表是有了，但是数据没有。一个电商项目怎么能没有多一点的数据呢，于是决定自己一个表造个几十万数据（总共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;
......
......
......
mysql
java
**粗体** _斜体_ [链接](http://example.com) `代码` - 列表 > 引用。你还可以使用@来通知其他用户。