根据提交的二维数据得到mysql建表和插入数据实用工具,这是重构版本(之前有过)。
会通过数据的长度，类型，是否数字，是否唯一等做判断，且每千条一个插入语句以优化性能。
<?php
//整理与分享：yujianyue<15058593138@qq.com>
setlocale(LC_ALL, 'C'); //window:删除行首双斜杠
$ti = "csv2sql";
$filename = 'data.csv'; // CSV 文件路径function analyzeCSV($filename) {
if (!file_exists($filename) || !is_readable($filename)) {
die("CSV file not found or not readable.");
}
$handle = fopen($filename, 'r');
$header = fgetcsv($handle,0,"\t"); // 获取表头
$data = []; $Info = []; // 用于存储字段分析结果
while (($row = fgetcsv($handle,0,"\t")) !== false) {
$data[] = $row; $ii=0;
foreach ($row as $index => $value) {
$fName = $header[$index];
$length = strlen($value); $ii++;
if (!isset($Info[$fName])) {
$Info[$fName] = [
'maxLen' => 8,
'mixLen' => 2,
'iseng' => false,
'isNum' => true,
'keNull' => false,
'values' => [],
'isFix' => true
];
}
// 更新字段长度信息
$Info[$fName]['maxLen'] = max($Info[$fName]['maxLen'], $length);
$Info[$fName]['mixLen'] = max($Info[$fName]['mixLen'], $length);
// 检查是否为数字
if (!is_numeric($value) ) $Info[$fName]['isNum'] = false;
// 检查是否为6+英文数字(username)
if (preg_match('/^[a-zA-Z0-9]{6,18}$/', $value)) $Info[$fName]['iseng'] = true;
// 检查是否有空值
if ($value === '') { $Info[$fName]['keNull'] = true; }
// 检查是否等长
if ($Info[$fName]['isFix'] && $length !== $Info[$fName]['maxLen'] && $value !== '') {
$Info[$fName]['isFix'] = false;
}
$Info[$fName]['values'][] = $value;
}
}
fclose($handle);
foreach ($Info as $fName => &$info) {
$info['isUni'] = count(array_unique($info['values'])) === count($info['values']);
}
$tableName = basename($filename, ".csv");
$cTab = "CREATE TABLE `$tableName` (\n";
$cTab .= "`chalide_id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n";
$oSQL = "\n\n-- -可能需要修改:表名称等\n\n";
$oSQL .= "INSERT INTO `$tableName` (" . implode(", ", array_map(function($field) { return "`$field`"; }, $header)) . ") VALUES\n";
$iSuo = [];
foreach ($Info as $fName => $info) {
// 确定字段类型
if ($info['isNum']) {
$type = "INT(" . $info['maxLen'] . ")"; // $type = "FLOAT";
} else {
$type = "VARCHAR(" . $info['maxLen'] . ")";
}
// 添加 NULL 或 NOT NULL
$nullability = $info['keNull'] ? "" : "NOT NULL";
// 添加唯一性约束
$unique = "";
if($info['iseng']){
$unique = $info['isUni'] ? "UNIQUE" : "";
$iSuo[] = " INDEX ".$fName." (`".$fName."`)";
}
$cTab .= " `$fName` $type $nullability $unique COMMENT '$fName',\n";
}
if(count($iSuo)>0){
$cTab .= join(",\n", $iSuo) . "\n);\n";
}else{
$cTab = rtrim($cTab, ",\n"). "\n);\n";
}
$cTab .= "\n-- -以下可删除:供优化修改参考\n";
$cTab .= "-- -可能需要修改表名;字段长度;默认值等 及 以下索引优化\n";
$cTab .= "-- -字段名称别符号,可英文字母开头字母数字可中文\n";
$cTab .= "-- -普通索引参考: INDEX ".$fName." (`".$fName."`),\n";
$cTab .= "-- -全文索引参考: FULLTEXT (`".$fName."`),\n";
$cTab .= "-- -唯一索引参考: UNIQUE KEY `".$fName."` (`".$fName."`),\n";
$cTab .= "-- -索引参考: KEY `".$fName."` (`".$fName."`),\n";
// 生成插入数据的 SQL 语句
$values = []; $ii=0; $iSQL="";
foreach ($data as $row) {
$ii++;
$rowValues = array_map(function($value) {
return $value === '' ? "NULL" : "'" . addslashes($value) . "'";
}, $row);
$values[] = "(" . implode(", ", $rowValues) . ")";
if(stristr("$ii|","000|")){$iSQL .= $oSQL . implode(",\n", $values) . ";\n"; $values = [];
}
}
if(count($values)>0)$iSQL .= $oSQL . implode(",\n", $values) . ";\n";return [
'cTab' => $cTab,
'iSQL' => $iSQL
];
}
function txtarea($nb,$ti="结果展示",$ht="88vh") {$nb = htmlspecialchars($nb);$ti = "<p>$ti</p>";return "<div class='ban'>$ti<textarea style='height:$ht;'>$nb</textarea></div>";
}
if($_GET["x"] == "cha"){
$txts = isset($_POST['tips']) ? Trim($_POST['tips']) : '';
file_put_contents($filename,$txts);
$result = analyzeCSV($filename);
echo txtarea($result['cTab'],"CREATE TABLE SQL:");
echo txtarea($result['iSQL'],"INSERT SQL:");
exit();
}
?>
<!DOCTYPE html>
<html>
<head>
<title><?php echo $ti;?></title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width,minimum-scale=1.0,maximum-scale=1.0">
<meta name="apple-mobile-web-app-capable" content="yes" />
<script type="text/javascript">
console.log("问题反馈电话:","15058593138");
console.log("问题反馈邮件:","admin@12391.net");
function $(objId){
return document.getElementById(objId);
}
function loadcha(xid) {
var xmlhttp;
var Stxt= "nums=aa";
Stxt+="&tips="+ encodeURIComponent($("tips").value);
//$("tips").innerHTML = "正在加载...";
if (window.XMLHttpRequest) {
xmlhttp = new XMLHttpRequest();
} else {
xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange = function() {
if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
var btxt = xmlhttp.response;
if(btxt == "err01"){ $("tipx").innerHTML = "!"; return false;}
$('tipx').innerHTML = xmlhttp.response;
}
}
xmlhttp.open("POST", "?x=cha&tt="+Math.random(), true);
xmlhttp.setRequestHeader("Content-type", "application/x-www-form-urlencoded");
xmlhttp.send(Stxt);
}</script>
<style>
#tipx{display:flex;width:99.7%;border:0;margin-top:5px;}
.ban{width:49%;border:0;margin:0;}
textarea{display:block;width:99.7%;min-width:360px;border:1px solid #ccc;height:160px;}
table{margin:10px auto;border-left:1px solid #a2c6d3;border-top:3px solid #0180CF;width:99.7%;}
table td{border-right:1px solid #a2c6d3;border-bottom:1px solid #a2c6d3;word-wrap:break-word;word-break:break-all;}
.tt{background-color: #f2f2f2;}
b{color:red;}
#submit{ height:35px;}
</style>
</head>
<body><form class="form" id="form" method="POST" act="?act=cha" ><h3><?php echo $ti;?></h3>
<textarea id="tips">
姓名 学号 身份证号 科目1 科目2 科目3 科目4 科目5 科目. 科目N
李一 10001001 90001001 87 84 75 91 83 76 87
李二 10001002 90001002 95 81 81 71 60 82 99
李三 10001003 90001003 68 80 65 79 68 71 91
李四 10001004 90001004 82 80 75 90 87 64 81
李五 10001005 90001005 60 64 61 71 73 85 61
李六 10001006 90001006 62 69 77 73 96 67 82
李七 10001007 90001007 98 72 68 59 84 92 92
李一 10001001 90001001 87 84 75 91 83 76 87
李二 10001002 90001002 95 81 81 71 60 82 99
李三 10001003 90001003 68 80 65 79 68 71 91
李四 10001004 90001004 82 80 75 90 87 64 81
李五 10001005 90001005 60 64 61 71 73 85 61
李六 10001006 90001006 62 69 77 73 96 67 82
李七 10001007 90001007 98 72 68 59 84 92 92
李一 10001001 90001001 87 84 75 91 83 76 87
李二 10001002 90001002 95 81 81 71 60 82 99
李三 10001003 90001003 68 80 65 79 68 71 91
李四 10001004 90001004 82 80 75 90 87 64 81
李五 10001005 90001005 60 64 61 71 73 85 61
李六 10001006 90001006 62 69 77 73 96 67 82</textarea>
<input type="button" id="submit" value="提交" onclick="loadcha('xid')">
<div id="tipx"></div></form>
</body>
</html>
