PHP上传Excel至SQL Server遇类型转换错误，如何正确插入二进制数据？

1. 修复文件资源生命周期问题 #

你的代码存在核心逻辑错误：finally块在$stmt->execute()执行前就关闭了文件资源$fileContent，导致PDO无法读取文件内容，进而将参数识别为字符串类型，触发隐式转换错误。

调整代码结构，确保SQL执行完成后再关闭文件：

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $id = $_POST['id'];
    $manufacturer = $_POST['manufacturer'];
    $fileContent = null;

    if (isset($_FILES['spreadsheet_file']) && $_FILES['spreadsheet_file']['error'] == 0) {
        $filePath = $_FILES['spreadsheet_file']['tmp_name'];
        $fileContent = fopen($filePath, 'rb');
    } else {
        die("No file uploaded or an error occurred: {$_FILES['spreadsheet_file']['error']}");
    }

    try {
        $stmt = $conn->prepare("
            UPDATE  Test
            SET     manufacturer = :manufacturer,
                    spreadsheet_file = :fileContent
            WHERE   id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':manufacturer', $manufacturer);
        $stmt->bindParam(':fileContent', $fileContent, PDO::PARAM_LOB);
        
        // 将execute移至try块内，确保文件资源未关闭
        $stmt->execute();
    } catch (PDOException $e) {
        die("Error inserting the file into the database: " . $e->getMessage());
    } finally {
        if (is_resource($fileContent)) {
            fclose($fileContent);
        }
    }
}
?>

2. 备选方案：直接读取二进制内容绑定 #

若上述方法仍报错，可直接读取文件为二进制字符串，避免使用文件资源的兼容性问题：

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $id = $_POST['id'];
    $manufacturer = $_POST['manufacturer'];
    $fileBinary = null;

    if (isset($_FILES['spreadsheet_file']) && $_FILES['spreadsheet_file']['error'] == 0) {
        $filePath = $_FILES['spreadsheet_file']['tmp_name'];
        // 直接读取文件为二进制字符串
        $fileBinary = file_get_contents($filePath);
    } else {
        die("No file uploaded or an error occurred: {$_FILES['spreadsheet_file']['error']}");
    }

    try {
        $stmt = $conn->prepare("
            UPDATE  Test
            SET     manufacturer = :manufacturer,
                    spreadsheet_file = :fileBinary
            WHERE   id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':manufacturer', $manufacturer);
        // 绑定二进制字符串，指定参数类型为字符串
        $stmt->bindParam(':fileBinary', $fileBinary, PDO::PARAM_STR);
        
        $stmt->execute();
    } catch (PDOException $e) {
        die("Error inserting the file into the database: " . $e->getMessage());
    }
}
?>

3. 兜底方案：SQL显式转换 #

如果前两种方法无效，可在SQL语句中用CONVERT函数强制转换参数类型：

<?php
if ($_SERVER["REQUEST_METHOD"] == "POST") {
    $id = $_POST['id'];
    $manufacturer = $_POST['manufacturer'];
    $fileBinary = null;

    if (isset($_FILES['spreadsheet_file']) && $_FILES['spreadsheet_file']['error'] == 0) {
        $filePath = $_FILES['spreadsheet_file']['tmp_name'];
        $fileBinary = file_get_contents($filePath);
    } else {
        die("No file uploaded or an error occurred: {$_FILES['spreadsheet_file']['error']}");
    }

    try {
        // 用CONVERT显式将参数转为VARBINARY(MAX)
        $stmt = $conn->prepare("
            UPDATE  Test
            SET     manufacturer = :manufacturer,
                    spreadsheet_file = CONVERT(VARBINARY(MAX), :fileBinary)
            WHERE   id = :id");
        $stmt->bindParam(':id', $id);
        $stmt->bindParam(':manufacturer', $manufacturer);
        $stmt->bindParam(':fileBinary', $fileBinary, PDO::PARAM_STR);
        
        $stmt->execute();
    } catch (PDOException $e) {
        die("Error inserting the file into the database: " . $e->getMessage());
    }
}
?>

关键说明 #

• 优先修复文件资源生命周期问题，这是触发错误的最可能原因。
• 直接读取二进制内容的方式兼容性更强，避免PDO对文件资源处理的差异。
• CONVERT函数是SQL Server官方推荐的解决隐式转换问题的方法。

内容的提问来源于stack exchange，提问作者Darrell Brogdon