PHP读取CSV导入MariaDB时数据类型转换与SQL语法错误问题

核心错误点 #

1. 变量名与类型判断逻辑错误

   • 你定义的类型数组是$dataType，但代码里判断isset($columnDataTypes[$key])，这个变量从未定义，导致类型转换逻辑完全没执行。
   • 类型判断时错误地用整个数组$dataType和字符串比较，应该用之前赋值的$Type变量，比如if ($Type === 'boolean')而非if ($dataType === 'boolean')。

2. SQL拼接的双重引号冲突

   • 你在类型转换时已经给字符串加上了单引号，但后续拼接SQL的VALUES部分又用了' . implode("', '", $currentRow) . "'，导致字符串被嵌套引号（比如'abc'变成''abc''`），数字类型也被错误包裹引号，直接触发SQL语法错误。

3. 未定义的$num变量

   • 循环for ($c=0; $c < $num; $c++)中的$num没有定义，应该替换为count($data)来获取当前行的列数，避免数据丢失或越界。

修正后的代码 #

类型转换部分修正 #

// 遍历当前行的每个值
foreach ($currentRow as $key => &$value) {
    // 检查对应列的类型是否定义
    if (isset($dataType[$key])) {
        $Type = $dataType[$key];
        // 根据类型转换值
        if ($Type === 'boolean') {
            $value = $currentRow[$key] ? 1 : 0;
        } elseif ($Type === 'integer') {
            $value = (int) $value;
        } elseif ($Type === 'float') {
            $value = (float) $value;
        } else {
            // 字符串需转义，避免引号冲突和SQL注入
            $value = $conn->real_escape_string($value);
        }
    }
}

SQL拼接部分修正 #

// 分别处理字符串（加引号）和数字/布尔（不加引号）
$values = [];
foreach ($currentRow as $key => $val) {
    $type = $dataType[$key] ?? 'string';
    $values[] = $type === 'string' ? "'{$val}'" : $val;
}

// 拼接完整SQL语句
$sql = "INSERT INTO trackfeatures(" . implode(", ", $syntaxHeaders) . ") VALUES (" . implode(", ", $values) . ")";

完整循环部分修正 #

$dataType = ["string", "string", "string", "string", "boolean", "float", "float", "integer", "float", "integer", "float", "float", "float", "float", "float", "float", "integer", "integer", "integer"];

// 打开CSV文件
if (($handle = fopen("testTrackFeatures.csv", "r")) !== FALSE) {
    $row = 0;
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $row++;
        $currentRow = [];   
        // 用当前行的列数替代未定义的$num
        $num = count($data);
        for ($c=0; $c < $num; $c++) {
            array_push($currentRow, $data[$c]);
        }
        
        // 类型转换与转义
        foreach ($currentRow as $key => &$value) {
            if (isset($dataType[$key])) {
                $Type = $dataType[$key];
                if ($Type === 'boolean') {
                    $value = $value ? 1 : 0;
                } elseif ($Type === 'integer') {
                    $value = (int) $value;
                } elseif ($Type === 'float') {
                    $value = (float) $value;
                } else {
                    $value = $conn->real_escape_string($value);
                }
            }
        }
        
        // 构建VALUES部分
        $values = [];
        foreach ($currentRow as $key => $val) {
            $type = $dataType[$key] ?? 'string';
            $values[] = $type === 'string' ? "'{$val}'" : $val;
        }
        
        $sql = "INSERT INTO trackfeatures(" . implode(", ", $syntaxHeaders) . ") VALUES (" . implode(", ", $values) . ")";      
        
        // 执行SQL并输出结果
        if ($conn->query($sql) === TRUE) {
            echo "第{$row}行数据插入成功<br />";
        } else {
            echo "第{$row}行插入失败: " . $conn->error . "<br />";
            // 输出错误SQL便于调试
            echo "错误SQL语句: " . $sql . "<br />";
        }
    }
    fclose($handle);
    $conn->close();
}

更优方案：使用预处理语句 #

直接拼接SQL易出现语法错误和注入风险，推荐使用mysqli预处理语句，自动处理类型转换与转义：

// 预处理语句模板
$placeholders = implode(", ", array_fill(0, count($dataType), '?'));
$sql = "INSERT INTO trackfeatures(" . implode(", ", $syntaxHeaders) . ") VALUES ($placeholders)";
$stmt = $conn->prepare($sql);

// 绑定参数类型：s=字符串，i=整数/布尔，d=浮点数
$types = '';
foreach ($dataType as $type) {
    switch($type) {
        case 'integer':
        case 'boolean':
            $types .= 'i';
            break;
        case 'float':
            $types .= 'd';
            break;
        default:
            $types .= 's';
            break;
    }
}

// 绑定参数
$stmt->bind_param($types, ...$currentRow);

// 执行语句
if ($stmt->execute()) {
    echo "第{$row}行数据插入成功<br />";
} else {
    echo "第{$row}行插入失败: " . $stmt->error . "<br />";
}
$stmt->close();

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