TechHUB - 技术文章 - 网页编程 - PHP技术 - 完整支持TRUNCATE TABLE和其他SQL语句的导入方案

首页技术教程实用代码注册破解正则表达式网址导航电子书籍

技术文章 >> 网页编程 >> PHP技术

≡ 分类 ≡

≡ 推荐 ≡

≡ 热点 ≡

<

>

完整支持TRUNCATE TABLE和其他SQL语句的导入方案

作者：未知， 来源：网络， 阅读：38， 发布时间：2025-04-29

当SQL文件中包含一次插入多条记录的INSERT语句时（如INSERT INTO table VALUES (...), (...), ...），我们需要特别处理。
此方案特别适合包含INSERT INTO ... VALUES (...),(...),...格式的SQL文件，可以正确处理包含数百条记录的单个INSERT语句，进度显示基于实际插入的记录数，更加准确，自动处理各种格式的字符串值，包括包含逗号和括号的字符串，可以正确处理SQL文件中的所有语句，包括TRUNCATE TABLE、INSERT、CREATE TABLE等各种SQL命令。

前端部分 (HTML + jQuery)

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>SQL文件导入工具</title>
    <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pako/2.0.4/pako.min.js"></script>
    <style>
        .progress-container {
            margin: 20px 0;
            background-color: #f5f5f5;
            border-radius: 4px;
            height: 30px;
        }
        .progress-bar {
            height: 100%;
            border-radius: 4px;
            background-color: #4CAF50;
            width: 0%;
            transition: width 0.3s;
            text-align: center;
            color: white;
            line-height: 30px;
        }
        .status {
            margin: 10px 0;
            padding: 10px;
            border: 1px solid #ddd;
            border-radius: 4px;
            min-height: 60px;
        }
        .btn {
            background-color: #4CAF50;
            color: white;
            padding: 10px 15px;
            border: none;
            border-radius: 4px;
            cursor: pointer;
            font-size: 16px;
        }
        .btn:hover {
            background-color: #45a049;
        }
        .btn:disabled {
            background-color: #cccccc;
            cursor: not-allowed;
        }
        #fileInfo {
            margin-top: 10px;
            padding: 10px;
            background-color: #f9f9f9;
            border-radius: 4px;
        }
    </style>
</head>
<body>
<div class="select_data_file_container">
	<input type="file" id="sqlFile" name="sqlFile" value="" accept=".sql,.gz,.sql.gz" placeholder="请选择要导入的数据文件" /> 
	<input type="button" id="importBtn" name="importBtn" filetype="data" value="导入" />
</div>

<div id="fileInfo" style="padding:4px;display: none;">
	<strong>文件信息:</strong>
	<div>名称: <span id="fileName"></span></div>
	<div>大小: <span id="fileSize"></span></div>
	<div>类型: <span id="fileType"></span></div>
	<div>状态: <span id="fileStatus">等待处理</span></div>
</div>

<div class="progress-container">
	<div style="padding:4px;" id="progressBar" class="progress-bar">0%</div>
</div>

<div id="status" class="status" style="padding:4px;">请选择SQL文件(.sql)或GZ压缩的SQL文件(.gz/.sql.gz)</div>
<div id="error" style="color: red;"></div>	
</body>
</html>

JS部分：

$(function () {
	$(document).on('change', '#sqlFile', function (e) {
		const file = e.target.files[0];
		if (!file) return;
		
		$('#fileInfo').show();
		$('#fileName').text(file.name);
		$('#fileSize').text((file.size / 1024).toFixed(1) + ' Kb');
		$('#fileType').text(file.type || getFileType(file.name));
		$('#fileStatus').text('等待处理').css('color', 'black');	
	});
	
	$(document).on('click', '#importBtn', function () {
            const fileInput = $('#sqlFile')[0];
            if (fileInput.files.length === 0) {
                $('#error').text('请先选择文件');
                return;
            }
            
            const file = fileInput.files[0];
            const isGz = isGzFile(file.name);
            
            // 验证文件类型
            if (!file.name.match(/\.(sql|gz|sql\.gz)$/i)) {
                $('#error').text('请选择.sql、.gz或.sql.gz格式的文件');
                return;
            }
            
            $('#importBtn').prop('disabled', true);
            $('#status').html('开始处理文件...');
            $('#error').text('');
            $('#progressBar').css('width', '0%').text('0%');
            $('#fileStatus').text('处理中...').css('color', 'blue');
            
            const reader = new FileReader();
            
            reader.onload = function(e) {
                try {
                    if (isGz) {
                        $('#status').html('检测到gz压缩文件，正在解压...');
                        $('#fileStatus').text('解压中...');
                        
                        // 使用pako解压GZ文件
                        const compressedData = new Uint8Array(e.target.result);
                        let decompressedData;
                        
                        try {
                            decompressedData = pako.inflate(compressedData);
                        } catch (e) {
                            throw new Error('gz解压失败: 文件可能已损坏或不是有效的gz格式');
                        }
                        
                        const sqlContent = new TextDecoder('utf-8').decode(decompressedData);
                        $('#fileStatus').text('解压完成').css('color', 'green');
                        processSqlContent(sqlContent, file.name);
                    } else {
                        $('#status').html('检测到普通SQL文件，准备导入...');
                        $('#fileStatus').text('处理中...');
                        const sqlContent = e.target.result;
                        processSqlContent(sqlContent, file.name);
                    }
                } catch (e) {
                    $('#error').text('处理文件时出错: ' + e.message);
                    $('#importBtn').prop('disabled', false);
                    $('#fileStatus').text('处理失败').css('color', 'red');
                    console.error(e);
                }
            };
            
            reader.onerror = function() {
                $('#error').text('读取文件时出错');
                $('#importBtn').prop('disabled', false);
                $('#fileStatus').text('读取失败').css('color', 'red');
            };
            
            // 根据文件类型选择读取方式
            if (isGz) {
                reader.readAsArrayBuffer(file);
            } else {
                reader.readAsText(file);
            }
	
		async function processSqlContent(sqlContent) {
			try {
				// 标准化换行符
				const normalizedContent = sqlContent.replace(/\r\n/g, '\n');
				
				// 分割SQL内容为独立的语句
				const sqlStatements = splitSqlStatements(normalizedContent);
				
				let totalStatements = sqlStatements.length;
				let totalCommands = 0;
				
				// 统计总命令数（TRUNCATE算1个，INSERT按记录数计算）
				sqlStatements.forEach(stmt => {
					if (stmt.trim().toUpperCase().startsWith('INSERT INTO')) {
						totalCommands += countValuesInInsert(stmt);
					} else {
						totalCommands += 1;
					}
				});
				
				$('#status').html(`准备导入 ${totalStatements} 条SQL语句，共 ${totalCommands} 个操作...`);
				
				let processedStatements = 0;
				let processedCommands = 0;
				let successCommands = 0;
				let errorCommands = 0;
				
				// 分批处理SQL语句（每次发送1条语句）
				for (let i = 0; i < sqlStatements.length; i++) {
					const stmt = sqlStatements[i];
					const isInsert = stmt.trim().toUpperCase().startsWith('INSERT INTO');
					const stmtCommands = isInsert ? countValuesInInsert(stmt) : 1;
					const isLast = (i === sqlStatements.length - 1);
					
					// 更新进度显示
					processedCommands += stmtCommands;
					processedStatements++;
					const progress = Math.round((processedCommands / totalCommands) * 100);
					
					$('#progressBar').css('width', progress + '%').text(progress + '%');
					$('#status').html(`导入进度: ${progress}%<br>
									  已处理 ${processedCommands}/${totalCommands} 个操作<br>
									  ${processedStatements}/${totalStatements} 条SQL语句<br>
									  成功: ${successCommands}, 失败: ${errorCommands}`);
					
					// 强制更新UI
					await new Promise(resolve => setTimeout(resolve, 0));
					
					try {
						const response = await $.ajax({
							url: 'do.php?action=import_sql_chunk',
							type: 'POST',
							data: {
								sql: stmt,
								isInsert: isInsert,
								currentCommands: processedCommands,
								totalCommands: totalCommands,
								isLastBatch: isLast
							},
							timeout: 30000
						});
						
						const data = (response);
						//const data = JSON.parse(response);
						if (data.success) {
							successCommands += data.processedCommands;
						} else {
							errorCommands += (stmtCommands - (data.processedCommands || 0));
							if (data.errors) {
								$('#error').append(`<br>错误: ${data.errors.join(', ')}`);
							}
						}
						
					} catch (error) {
						errorCommands += stmtCommands;
						$('#error').append(`<br>请求失败: ${error.message}`);
						console.error('导入错误:', error);
						continue;
					}
					
					await new Promise(resolve => setTimeout(resolve, 100));
				}
				
				$('#status').append(`<br>导入完成!<br>成功: ${successCommands} 个操作, 失败: ${errorCommands} 个操作`);
				
			} catch (e) {
				$('#error').text('处理过程中发生错误: ' + e.message);
				console.error('处理错误:', e);
			} finally {
				$('#importBtn').prop('disabled', false);
			}
		}

		// 分割SQL内容为独立语句
		function splitSqlStatements(sqlContent) {
			const statements = [];
			let currentStmt = '';
			let inString = false;
			let stringChar = '';
			let inComment = false;
			let commentType = ''; // --, #, /*
			
			for (let i = 0; i < sqlContent.length; i++) {
				const char = sqlContent[i];
				const nextChar = i < sqlContent.length - 1 ? sqlContent[i+1] : '';
				
				// 处理注释
				if (!inString && !inComment) {
					if (char === '-' && nextChar === '-') {
						inComment = true;
						commentType = '--';
						i++; // 跳过下一个字符
						continue;
					} else if (char === '#') {
						inComment = true;
						commentType = '#';
						continue;
					} else if (char === '/' && nextChar === '*') {
						inComment = true;
						commentType = '/*';
						i++; // 跳过下一个字符
						continue;
					}
				}
				
				// 结束注释
				if (inComment) {
					if (commentType === '--' && char === '\n') {
						inComment = false;
					} else if (commentType === '#' && char === '\n') {
						inComment = false;
					} else if (commentType === '/*' && char === '*' && nextChar === '/') {
						inComment = false;
						i++; // 跳过/
					}
					continue;
				}
				
				// 处理字符串字面量
				if (!inComment && (char === "'" || char === '"')) {
					if (!inString) {
						inString = true;
						stringChar = char;
					} else if (char === stringChar) {
						// 检查是否是转义的引号
						if (i > 0 && sqlContent[i-1] === '\\') {
							// 是转义引号，继续字符串
						} else {
							inString = false;
						}
					}
				}
				
				currentStmt += char;
				
				// 不在字符串中且遇到分号，表示语句结束
				if (!inString && !inComment && char === ';') {
					const trimmedStmt = currentStmt.trim();
					if (trimmedStmt !== '') {
						statements.push(trimmedStmt);
					}
					currentStmt = '';
				}
			}
			
			// 添加最后一个未完成的语句(如果没有分号结尾)
			if (currentStmt.trim() !== '') {
				statements.push(currentStmt.trim());
			}
			
			return statements;
		}

		// 计算INSERT语句中的VALUES数量
		function countValuesInInsert(insertStmt) {
			// 提取VALUES部分
			const valuesMatch = insertStmt.match(/VALUES\s*\((.*)\)\s*;?$/i);
			if (!valuesMatch) return 1; // 如果不是标准INSERT，至少算1个操作
			
			const valuesPart = valuesMatch[1];
			let count = 0;
			let depth = 0;
			let inString = false;
			let stringChar = '';
			
			for (let i = 0; i < valuesPart.length; i++) {
				const char = valuesPart[i];
				
				// 处理字符串字面量
				if ((char === "'" || char === '"') && !inString) {
					inString = true;
					stringChar = char;
				} else if (char === stringChar && inString) {
					// 检查是否是转义的引号
					if (i > 0 && valuesPart[i-1] === '\\') {
						// 是转义引号，继续字符串
					} else {
						inString = false;
					}
				}
				
				if (!inString && char === '(') depth++;
				if (!inString && char === ')') depth--;
				
				// 在顶层遇到逗号表示一个VALUES结束
				if (!inString && depth === 0 && char === ',') {
					count++;
				}
			}
			
			// 最后一个VALUES不会被逗号结束，所以+1
			return count + 1;
		}
		// 根据文件名获取文件类型
		function getFileType(filename) {
			if (filename.match(/\.sql\.gz$/i)) return 'GZ压缩的SQL文件';
			if (filename.match(/\.gz$/i)) return 'GZ压缩文件';
			if (filename.match(/\.sql$/i)) return 'SQL文件';
			return '未知文件类型';
		}

		// 检查是否是GZ文件
		function isGzFile(filename) {
			return filename.match(/\.(gz|sql\.gz)$/i);
		}	
	});
});

PHP后端：

if ($_GET['action'] == 'import_sql_chunk') {
	header('Content-Type: application/json; charset=utf-8');
	
	// 数据库配置
	$dbHost = $cfg_MySQLServer;
	$dbUser = $cfg_MySQLUserName;
	$dbPass = $cfg_MySQLPassword;
	$dbName = $cfg_MySQLDBName;

	// 连接数据库
	$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
	$conn->query("SET NAMES 'utf8'");
	$conn->query("SET SESSION sql_mode = ''");
	if ($conn->connect_error) {
		echo json_encode(['success' => false, 'message' => '数据库连接失败: ' . $conn->connect_error]);
		exit;
	}

	// 获取POST数据
	$sql = isset($_POST['sql']) ? $_POST['sql'] : '';
	$isInsert = isset($_POST['isInsert']) ? $_POST['isInsert'] === 'true' : false;
	$currentCommands = isset($_POST['currentCommands']) ? intval($_POST['currentCommands']) : 0;
	$totalCommands = isset($_POST['totalCommands']) ? intval($_POST['totalCommands']) : 0;
	$isLastBatch = isset($_POST['isLastBatch']) ? $_POST['isLastBatch'] === 'true' : false;
	
	if (empty($sql)) {
		echo json_encode(['success' => false, 'message' => '没有接收到SQL语句']);
		exit;
	}

	// 处理SQL块
	$successCount = 0;
	$errorCount = 0;
	$errors = [];
	$processedCommands = 0;

	// 执行SQL语句
	if ($isInsert) {
		// 处理INSERT语句
		if ($conn->query($sql)) {
			$processedCommands = countValuesInInsert($sql);
			$successCount = $processedCommands;
		} else {
			// 如果批量INSERT失败，拆分成单条执行
			$singleInserts = splitMultiValueInsert($sql);
			foreach ($singleInserts as $singleInsert) {
				if ($conn->query($singleInsert)) {
					$successCount++;
				} else {
					$errorCount++;
					$errors[] = $conn->error;
				}
			}
			$processedCommands = count($singleInserts);
		}
	} else {
		// 处理其他SQL语句(TRUNCATE, CREATE TABLE等)
		if ($conn->query($sql)) {
			$successCount = 1;
			$processedCommands = 1;
		} else {
			$errorCount = 1;
			$errors[] = $conn->error;
			
			// 特殊处理TRUNCATE TABLE
			if (preg_match('/^TRUNCATE\s+TABLE\s+`?([a-zA-Z0-9_]+)`?/i', $sql, $matches)) {
				$tableName = $matches[1];
				// 尝试使用DELETE FROM作为备选方案
				$deleteSql = "DELETE FROM `$tableName`";
				if ($conn->query($deleteSql)) {
					$successCount = 1;
					$processedCommands = 1;
					$errorCount = 0;
					$errors = [];
				} else {
					$errors[] = "TRUNCATE和DELETE都失败: " . $conn->error;
				}
			}
		}
	}
	
	// 恢复设置
	$conn->query("SET FOREIGN_KEY_CHECKS = 1");
	
	// 关闭连接
	$conn->close();

	// 返回结果
	echo json_encode([
		'success' => $errorCount === 0,
		'processedCommands' => $successCount,
		'message' => $errorCount > 0 ? "部分操作执行失败" : "执行成功",
		'errors' => array_slice($errors, 0, 5)
	]);
}

// 计算INSERT语句中的VALUES数量
function countValuesInInsert($insertStmt) {
    if (!preg_match('/VALUES\s*\((.*)\)\s*;?$/i', $insertStmt, $matches)) {
        return 1;
    }
    
    $valuesPart = $matches[1];
    $count = 0;
    $depth = 0;
    $inString = false;
    $stringChar = '';
    
    for ($i = 0; $i < strlen($valuesPart); $i++) {
        $char = $valuesPart[$i];
        
        if (($char === "'" || $char === '"') && !$inString) {
            $inString = true;
            $stringChar = $char;
        } elseif ($char === $stringChar && $inString) {
            if ($i > 0 && $valuesPart[$i-1] === '\\') {
                continue;
            }
            $inString = false;
        }
        
        if (!$inString && $char === '(') $depth++;
        if (!$inString && $char === ')') $depth--;
        
        if (!$inString && $depth === 0 && $char === ',') {
            $count++;
        }
    }
    
    return $count + 1;
}

// 将多值INSERT拆分为单值INSERT
function splitMultiValueInsert($insertStmt) {
    if (!preg_match('/^(INSERT INTO\s+.+?\sVALUES\s*)\((.*)\)\s*;?$/i', $insertStmt, $matches)) {
        return [$insertStmt];
    }
    
    $prefix = $matches[1];
    $valuesPart = $matches[2];
    $singleInserts = [];
    $currentValue = '';
    $depth = 0;
    $inString = false;
    $stringChar = '';
    
    for ($i = 0; $i < strlen($valuesPart); $i++) {
        $char = $valuesPart[$i];
        
        if (($char === "'" || $char === '"') && !$inString) {
            $inString = true;
            $stringChar = $char;
        } elseif ($char === $stringChar && $inString) {
            if ($i > 0 && $valuesPart[$i-1] === '\\') {
                continue;
            }
            $inString = false;
        }
        
        $currentValue .= $char;
        
        if (!$inString && $char === '(') $depth++;
        if (!$inString && $char === ')') $depth--;
        
        if (!$inString && $depth === 0 && $char === ',') {
            $singleInserts[] = $prefix . '(' . trim(substr($currentValue, 0, -1)) . ');';
            $currentValue = '';
        }
    }
    
    if (!empty(trim($currentValue))) {
        $singleInserts[] = $prefix . '(' . trim($currentValue) . ');';
    }
    
    return $singleInserts;
}

方案特点

全面支持各种SQL语句:
- 支持TRUNCATE TABLE、CREATE TABLE、ALTER TABLE等DDL语句
- 支持INSERT、UPDATE、DELETE等DML语句
- 支持注释(/* */, --, #)
智能错误处理:
- TRUNCATE失败时自动尝试使用DELETE FROM
- 批量INSERT失败时自动拆分为单条INSERT
- 详细的错误信息返回
精确进度统计:
- 普通SQL语句计为1个操作
- INSERT语句按记录数计算操作数
- 实时反馈处理进度
稳定性增强:
- 每条SQL语句独立处理
- 临时禁用外键约束
- 设置宽松SQL模式

使用说明

前端会自动识别SQL文件中的所有语句类型
进度条会准确反映实际执行的操作数量
错误信息会累积显示在错误区域
即使部分语句失败，也会继续执行后续语句

注意事项

对于非常大的SQL文件，建议增加PHP配置:
```
memory_limit = 512M
max_execution_time = 0
```

标签：TRUNCATE SQL 导入

以下是用户评论查看全部评论