如何使用PHP和PDO将CSV文件导入MySQL数据库-php教程-PHP中文网

如何使用PHP和PDO将CSV文件导入MySQL数据库

本文详细介绍了如何使用PHP和PDO将CSV文件导入MySQL数据库。教程涵盖了从数据库连接、文件上传处理、CSV数据读取（包括自定义分隔符）、到使用PDO预处理语句进行数据插入的完整流程。特别强调了预处理语句中参数占位符的正确使用，并提供了完整的代码示例及错误处理和最佳实践建议，旨在帮助开发者构建稳定高效的CSV数据导入功能。

1. 引言

在web应用开发中，将外部数据（如csv格式）导入到数据库是一个常见的需求。php结合pdo（php data objects）为与mysql数据库交互提供了强大且安全的方式。本教程将引导您完成一个完整的csv文件导入mysql数据库的流程，并指出常见的错误及如何避免它们。

2. 前置准备

在开始之前，请确保您已具备以下环境和基础知识：

Web服务器环境： 如Apache或Nginx，并安装了PHP。
MySQL数据库： 确保MySQL服务正在运行。
PHP基础： 了解PHP语法、文件操作及表单处理。
PDO基础： 了解PDO的基本概念，如连接数据库、预处理语句等。

数据库表结构示例： 为了演示，我们假设有一个名为 users 的数据库表，包含以下字段：

CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `szam` VARCHAR(255) NOT NULL,
    `forras_szam` VARCHAR(255) NOT NULL
);

登录后复制

CSV文件格式示例： 假设您的CSV文件数据以分号（;）分隔，例如：

BGYR002217;FK-066
BGYR002218;FK-140

登录后复制

3. PHP脚本实现

整个导入过程可以分为几个主要步骤：数据库连接、文件上传处理、读取CSV数据并插入数据库。

3.1 数据库连接

首先，我们需要使用PDO建立与MySQL数据库的连接。为了提高安全性，建议将数据库凭据保存在配置文件中，但这里为了演示方便直接写入代码。

<?php
$dbHost = 'localhost';
$dbName = 'test';
$dbChar = 'utf8mb4'; // 推荐使用 utf8mb4 支持更广泛的字符集
$dbUser = 'root';
$dbPass = '';

try {
    $pdo = new PDO(
        "mysql:host=" . $dbHost . ";dbname=" . $dbName . ";charset=" . $dbChar,
        $dbUser,
        $dbPass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // 启用异常模式
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, // 默认关联数组获取结果
            PDO::ATTR_EMULATE_PREPARES => false // 禁用模拟预处理，使用原生预处理
        ]
    );
} catch (Exception $ex) {
    // 捕获数据库连接异常
    exit("数据库连接失败: " . $ex->getMessage());
}
?>

登录后复制

注意事项：

立即学习“PHP免费学习笔记（深入）”；

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION：这是非常重要的设置，它会使PDO在遇到错误时抛出异常，方便我们进行错误捕获和处理。
PDO::ATTR_EMULATE_PREPARES => false：禁用模拟预处理，确保使用数据库服务器的原生预处理功能，提高安全性和性能。
utf8mb4：推荐使用 utf8mb4 字符集，以支持更广泛的Unicode字符，包括表情符号等。

3.2 处理文件上传

CSV文件通常通过HTML表单上传到服务器。PHP通过 $_FILES 全局变量来处理上传的文件。

首先，您需要一个HTML表单来上传CSV文件：

库宝AI

库宝AI是一款功能多样的智能伙伴助手，涵盖AI写作辅助、智能设计、图像生成、智能对话等多个方面。

109

查看详情

<!DOCTYPE html>
<html lang="zh-CN">
<head>
    <meta charset="UTF-8">
    <title>CSV文件导入</title>
</head>
<body>
    <form action="import.php" method="post" enctype="multipart/form-data">
        <label for="upcsv">选择CSV文件:</label>
        <input type="file" name="upcsv" id="upcsv" accept=".csv">
        <button type="submit">上传并导入</button>
    </form>
</body>
</html>

登录后复制

在 import.php 中，我们需要检查文件是否成功上传：

// ... (数据库连接代码) ...

if (!isset($_FILES["upcsv"]) || $_FILES["upcsv"]["error"] !== UPLOAD_ERR_OK) {
    exit("文件上传失败或未选择文件.");
}

$csvFilePath = $_FILES["upcsv"]["tmp_name"];
$fh = fopen($csvFilePath, "r");

if ($fh === false) {
    exit("无法打开上传的CSV文件.");
}

登录后复制

3.3 读取CSV数据并插入数据库

这是核心部分，我们将逐行读取CSV文件，并使用PDO预处理语句将数据插入到数据库中。

关键点：CSV分隔符fgetcsv() 函数默认使用逗号（,）作为分隔符。如果您的CSV文件使用其他分隔符（如本例中的分号 ;），您必须将其作为第二个参数（或第三个参数，如果指定了长度）传递给 fgetcsv()。

关键点：PDO预处理语句占位符 在PDO预处理语句中，参数占位符是问号 ? 或命名占位符 :name。原始代码中使用了 VALUES (?;?)，这是不正确的语法。正确的占位符应该是 VALUES (?,?)。

// ... (数据库连接和文件处理代码) ...

$rowCount = 0;
$errorCount = 0;

// 开始事务，确保数据一致性，如果导入过程中出现错误，可以回滚
$pdo->beginTransaction();

try {
    // 准备SQL插入语句
    // 注意：VALUES (?,?) 是正确的占位符语法
    $stmt = $pdo->prepare("INSERT INTO users (szam, forras_szam) VALUES (?,?)");

    // 循环读取CSV文件中的每一行
    // fgetcsv的第三个参数是分隔符，这里指定为分号 ';'
    while (($row = fgetcsv($fh, 0, ';')) !== false) {
        // 检查行数据是否符合预期，避免 Undefined array key 错误
        if (count($row) < 2) {
            echo "警告: 跳过无效行 (数据不足): " . implode(';', $row) . "<br>";
            $errorCount++;
            continue;
        }

        try {
            // 执行预处理语句，绑定CSV行中的数据
            $stmt->execute([$row[0], $row[1]]);
            $rowCount++;
        } catch (Exception $ex) {
            // 捕获每行插入时的数据库错误
            echo "插入数据失败 (行: " . implode(';', $row) . "): " . $ex->getMessage() . "<br>";
            $errorCount++;
            // 可以选择在这里中断导入或继续
        }
    }

    // 提交事务
    $pdo->commit();
    echo "数据导入完成。<br>";
    echo "成功导入 " . $rowCount . " 行。<br>";
    if ($errorCount > 0) {
        echo "有 " . $errorCount . " 行导入失败或被跳过。<br>";
    }

} catch (Exception $ex) {
    // 捕获整个导入过程中的异常，并回滚事务
    $pdo->rollBack();
    echo "导入过程中发生致命错误: " . $ex->getMessage() . "<br>";
    echo "所有更改已回滚。<br>";
} finally {
    // 确保文件句柄被关闭
    fclose($fh);
}

echo "DONE.";
?>

登录后复制

4. 完整代码示例

将上述所有部分组合起来，形成一个完整的 import.php 文件：

<?php

// 1. 数据库连接配置
$dbHost = 'localhost';
$dbName = 'test';
$dbChar = 'utf8mb4'; // 推荐使用 utf8mb4
$dbUser = 'root';
$dbPass = '';

try {
    $pdo = new PDO(
        "mysql:host=" . $dbHost . ";dbname=" . $dbName . ";charset=" . $dbChar,
        $dbUser,
        $dbPass,
        [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false
        ]
    );
} catch (Exception $ex) {
    exit("数据库连接失败: " . $ex->getMessage());
}

// 2. 处理文件上传
if (!isset($_FILES["upcsv"]) || $_FILES["upcsv"]["error"] !== UPLOAD_ERR_OK) {
    exit("文件上传失败或未选择文件.");
}

$csvFilePath = $_FILES["upcsv"]["tmp_name"];
$fh = fopen($csvFilePath, "r");

if ($fh === false) {
    exit("无法打开上传的CSV文件.");
}

// 3. 读取CSV数据并插入数据库
$rowCount = 0;
$errorCount = 0;

// 开始事务
$pdo->beginTransaction();

try {
    // 准备SQL插入语句，使用正确的占位符 '?'
    $stmt = $pdo->prepare("INSERT INTO users (szam, forras_szam) VALUES (?,?)");

    // 循环读取CSV文件中的每一行
    // fgetcsv的第三个参数是分隔符，这里指定为分号 ';'
    while (($row = fgetcsv($fh, 0, ';')) !== false) {
        // 简单的数据完整性检查
        if (count($row) < 2) {
            echo "警告: 跳过无效行 (数据不足): " . htmlspecialchars(implode(';', $row)) . "<br>";
            $errorCount++;
            continue;
        }

        try {
            // 执行预处理语句
            $stmt->execute([$row[0], $row[1]]);
            $rowCount++;
        } catch (Exception $ex) {
            // 捕获每行插入时的数据库错误
            echo "插入数据失败 (行: " . htmlspecialchars(implode(';', $row)) . "): " . $ex->getMessage() . "<br>";
            $errorCount++;
        }
    }

    // 提交事务
    $pdo->commit();
    echo "数据导入完成。<br>";
    echo "成功导入 " . $rowCount . " 行。<br>";
    if ($errorCount > 0) {
        echo "有 " . $errorCount . " 行导入失败或被跳过。<br>";
    }

} catch (Exception $ex) {
    // 捕获整个导入过程中的异常，并回滚事务
    $pdo->rollBack();
    echo "导入过程中发生致命错误: " . $ex->getMessage() . "<br>";
    echo "所有更改已回滚。<br>";
} finally {
    // 确保文件句柄被关闭
    if ($fh) {
        fclose($fh);
    }
}

echo "DONE.";
?>

登录后复制

5. 注意事项与最佳实践

错误处理： 使用 try-catch 块捕获所有可能的异常，包括数据库连接、文件操作和SQL执行错误。
事务： 对于批量数据导入，强烈建议使用事务（beginTransaction(), commit(), rollBack()）。这样可以确保数据的一致性，如果导入过程中发生任何错误，可以回滚所有已执行的操作，避免部分数据导入成功而导致的数据不完整。
CSV分隔符： 务必根据您的CSV文件实际使用的分隔符，正确设置 fgetcsv() 的第三个参数。
数据验证： 在 $stmt->execute() 之前，对从CSV读取的数据进行严格的验证（如数据类型、长度、格式等），以防止无效数据插入数据库或SQL注入风险。
安全性：
- 永远不要直接将用户输入拼接到SQL查询中。使用PDO预处理语句是防止SQL注入的最佳实践。
- 限制上传文件的大小和类型，防止恶意文件上传。
- 上传的文件应存储在Web服务器不可直接访问的目录中，处理完成后及时删除。
性能优化：
- 对于非常大的CSV文件，可以考虑分批次导入（例如，每1000行提交一次事务），以减少内存消耗和提高效率。
- 如果数据量巨大，LOAD DATA INFILE 是MySQL原生提供的一种更高效的导入方式，但它需要文件在数据库服务器上可访问。
用户反馈： 在导入完成后，向用户提供清晰的反馈，说明导入结果、成功行数、失败行数及原因。

6. 总结

通过本文的详细教程，您应该已经掌握了如何使用PHP和PDO安全高效地将CSV文件导入MySQL数据库。核心要点在于正确使用PDO预处理语句的占位符 ?，以及根据CSV文件的实际分隔符配置 fgetcsv() 函数。遵循上述最佳实践，可以帮助您构建一个健壮、安全且高效的数据导入功能。

以上就是如何使用PHP和PDO将CSV文件导入MySQL数据库的详细内容，更多请关注php中文网其它相关文章！