小脚本实现自动生成数据库数据
2019-07-04 本文已影响0人
ZaleJ
emm... 差不多就是先拿到navicat中数据库表的CREATE语句，然后生成对应的INSERT语句。用于日常开发中生成一些简单的数据使用。几万条数据一般还是没问题的。
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>根据navicat的create语句随机生成insert语句</title>
<script type="text/javascript">
function generateInsertClums() {
// 获取文本内容
var createStr=document.getElementById('getCreateStr').value;
var colum = document.getElementById('insertNum').value;
// 分析处理create语句
var tableName = createStr.substring(createStr.indexOf('`')+1, createStr.indexOf('`', createStr.indexOf('`')+1));
// 获取需要分析的字符范围
var startIndex = createStr.indexOf('(');
var lastIndex = createStr.lastIndexOf(')');
// 遍历获取名字/类型键值对
var resultMapper = {};
var tagIsLeft = true;
var leftPos = -1;
var rightPos = -1;
var clomName = 'fuck';
for(var i = startIndex; i < lastIndex; i++) {
// 获取key
if (createStr[i]=='`') {
if (tagIsLeft) {
if (createStr[i-1] != ' ' || createStr[i-2] !=' ') {
continue;
}
leftPos = i;
tagIsLeft = false;
} else {
rightPos = i;
tagIsLeft = true;
}
if (tagIsLeft) {
clomName = createStr.substring(leftPos+1, rightPos);
// 获取type，此时leftPos为左开始点，rogitPos为右结束点，然后思路为获取结束点后第一个空格和第二个空格之间的内容
var spaceLeft = rightPos+1;
var spaceRight = createStr.indexOf(' ', spaceLeft+1);
// 将数据加入mapper
resultMapper[clomName] = createStr.substring(spaceLeft+1, spaceRight);
}
}
}
// return resultMapper;
// for(var i = 0; i < colum; i ++) {
// document.getElementById('insertResult').value += getInsertCloms(resultMapper, tableName)+"\r\n";
// }
document.getElementById('insertResult').value += getInsertCloms(resultMapper, tableName, colum)+"\r\n";
}
function getInsertCloms(resultMapper,tableName, insertNum) {
var insertStr = "INSERT INTO " + tableName;
var paraStr = getInsertParaStr(resultMapper);
// var valStr = getInsertValStr(resultMapper);
var valStr = "";
// console.log(valStr);
for(var i = 0; i < insertNum; i++) {
valStr+=getInsertValStr(resultMapper);
valStr+="\r\n,";
}
valStr=valStr.substring(0,valStr.length-1);
insertStr += " "+paraStr+" VALUES "+valStr+";";
return insertStr;
}
// 获取insert语句的字段值（除去主键id）
function getInsertParaStr(resultMapper) {
var paraStr = "(";
// 遍历resultMapper生成参数数据
for(var i in resultMapper) {
if (i=="id") {
continue;
}
paraStr+=i+",";
}
paraStr=paraStr.substring(0,paraStr.length-1);
paraStr+=")";
return paraStr;
}
// 获取insert语句的随机value值（除去主键id）
function getInsertValStr(resultMapper) {
var valStr = "(";
// 遍历resulet生成模板数据
for(var i in resultMapper) {
if (i=="id") {
continue;
}
// 在此处添加新的数据类型
// console.log(resultMapper[i]);
if (resultMapper[i].indexOf('int')>-1) {
// console.log(getRandomIntVal(5, 0));
if (i=="state" || i=="enabled" || i=="meter_type") {
valStr+=0;
} else {
valStr+=getRandomIntVal(5, 0);
}
} else
if(resultMapper[i].indexOf('varchar')>-1) {
// console.log(getRandomStringVal(12));
if (i.indexOf('name')>-1) {
valStr+="'"+getRandomChineseVal(5)+"'";
} else {
valStr+="'"+getRandomStringVal(5)+"'";
}
} else
if (resultMapper[i].indexOf('date')>-1) {
// console.log(getRandomDateVal("yyyy-MM-dd"));
valStr+="'"+getRandomDateVal("yyyy-MM-dd")+"'";
} else
if (resultMapper[i].indexOf('decimal')>-1) {
// console.log(getRandomIntVal(5, 2));
valStr+=getRandomIntVal(5, 2);
} else {
return "ERROR!!!!!!";
}
valStr+=",";
}
valStr=valStr.substring(0,valStr.length-1);
valStr+=")";
return valStr;
}
// 获取特定位数的int值
function getRandomIntVal(bit, pot) {
var power = 1;
for(var i = 0; i < bit; i++) {
power *= 10;
}
power = Math.random()*power;
return power.toFixed(pot);
}
function getRandomStringVal(bit) {
bit = bit || 5;
var $chars = 'ABCDEFGHJKMNPQRSTWXYZabcdefhijkmnprstwxyz2345678';
var maxPos = $chars.length;
var pwd = '';
for (i = 0; i < bit; i++) {
pwd += $chars.charAt(Math.floor(Math.random() * maxPos));
}
return pwd;
}
function getRandomChineseVal(bit) {
bit = bit || 5;
var $chars = '上海转运中心芜湖杭州武汉洛阳海盐东莞青岛千岛湖顺丰圆通韵达中通天天快递';
var maxPos = $chars.length;
var pwd = '';
for (i = 0; i < bit; i++) {
pwd += $chars.charAt(Math.floor(Math.random() * maxPos));
}
return pwd;
}
function getRandomDateVal(dateFormat) {
// 产生随机整数包含上限值与下限值
// function random(lower, upper) {
// return Math.floor(Math.random() * (upper - lower+1)) + lower;
// }
var year = Math.ceil(Math.floor(Math.random() * (2019 - 2018+1)) + 2018);
var month = Math.ceil(Math.floor(Math.random() * (12 - 1+1)) + 1);
var day = Math.ceil(Math.floor(Math.random() * (28 - 1+1)) + 1);
if (dateFormat == "yyyy-MM-dd") {
return year+"-"+month+"-"+day;
}
if (dateFormat == "yyyy-MM") {
return year+"-"+month;
}
}
</script>
</head>
<body>
create语句：
<textarea name="getCreateStr" id="getCreateStr" rows="4" cols="100"></textarea>
<br>
生成数据条数：
<input type="text" name="insertNum" id = "insertNum"></input>
<button type="submit" onclick="generateInsertClums()">生成按钮</button>
<br>
<textarea name="insertResult" id="insertResult" rows="10" cols="100"></textarea>
<p>
科技与信息中心-产品一部-转运中心智能化部-季兆伦©
</p>
</body>
</html>
