自动匹配 CSV 文件名与数据库表名并批量导入 MySQL 教程-php教程-PHP中文网

自动匹配 CSV 文件名与数据库表名并批量导入 MySQL 教程

本文介绍如何根据 csv 文件名自动推导对应 mysql 表名，并安全、高效地批量导入多文件数据，涵盖文件遍历、命名映射、pdo 预处理插入及 csv 结构化解析等核心步骤。

本文介绍如何根据 csv 文件名自动推导对应 mysql 表名，并安全、高效地批量导入多文件数据，涵盖文件遍历、命名映射、pdo 预处理插入及 csv 结构化解析等核心步骤。

在日常数据同步场景中（如交通摄像头日志、IoT 设备上报），系统每天生成多个固定命名的 CSV 文件（例如 OwlertonGreenIN.CSV、SheffieldNorthOUT.CSV），需自动导入到结构匹配的 MySQL 表中。手动为每个文件写硬编码导入逻辑不可维护，关键在于实现 “文件名 → 表名” 的智能映射 与 结构化、防注入的数据批量写入。

✅ 核心思路：文件名标准化 + 表名自动推导

CSV 文件名通常携带业务语义（如 OwlertonGreenIN.CSV 表示 Owlerton Green 入口数据）。我们可通过规则将其映射为合法表名：

去除扩展名（.CSV → OwlertonGreenIN）
转小写（owlertongreenin）

替换非法字符（如 -、空格 → _），确保符合 MySQL 标识符规范
示例代码：

function filenameToTableName(string $filename): string {
  $basename = pathinfo($filename, PATHINFO_FILENAME); // 提取无扩展名部分
  $clean = strtolower(preg_replace('/[^a-z0-9]+/', '_', $basename)); // 小写+下划线替换
  return trim($clean, '_'); // 去首尾下划线
}

// 示例：OwlertonGreenIN.CSV → owlertongreenin // Sheffield-North-OUT.csv → sheffield_north_out

### ✅ 推荐方案：使用 PDO + 面向对象封装（替代 mysqli）

原代码使用 `mysqli` 且 SQL 拼接存在严重 SQL 注入风险（如 `$import="INSERT into table name (...) values(...)"`）。强烈建议升级为 **PDO 预处理 + 自动字段绑定** 方案，既安全又灵活。

以下是一个精简可用的 `DB` 工具类（已适配您的需求）：

```php
<?php class DB extends PDO {
    private $host = 'localhost';
    private $dbname;
    private $user = 'your_db_user';
    private $pass = 'your_db_pass';

    public function __construct(string $dbname) {
        $this->dbname = $dbname;
        $dsn = "mysql:host={$this->host};dbname={$this->dbname};charset=utf8mb4";
        try {
            parent::__construct($dsn, $this->user, $this->pass, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
        } catch (PDOException $e) {
            throw new RuntimeException("DB connection failed: " . $e->getMessage());
        }
    }

    // 安全插入单行数据：自动匹配数组键与表字段
    public function insertRow(string $table, array $row): bool {
        if (empty($row)) return false;

        $columns = array_keys($row);
        $placeholders = array_map(fn($col) => ":{$col}", $columns);

        $sql = "INSERT INTO `{$table}` (`" . implode('`,`', $columns) . "`) 
                VALUES (" . implode(',', $placeholders) . ")";

        $stmt = $this->prepare($sql);

        foreach ($row as $key => $value) {
            $type = is_int($value) ? PDO::PARAM_INT :
                   is_bool($value) ? PDO::PARAM_BOOL : PDO::PARAM_STR;
            $stmt->bindValue(":{$key}", $value, $type);
        }

        return $stmt->execute();
    }

    // 解析 CSV 文件为关联数组（支持带表头）
    public static function parseCsv(string $filepath): array {
        if (!file_exists($filepath) || !is_readable($filepath)) {
            throw new InvalidArgumentException("CSV file not accessible: {$filepath}");
        }

        $rows = array_map('str_getcsv', file($filepath, FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES));
        if (empty($rows)) throw new RuntimeException("Empty CSV file: {$filepath}");

        $headers = array_map('trim', $rows[0]); // 第行为字段名
        $data = [];

        for ($i = 1; $i <h3>✅ 完整自动化导入流程（主程序）</h3><pre class="brush:php;toolbar:false;">try {
    $db = new DB('your_database_name');
    $csvDir = $_SERVER['DOCUMENT_ROOT'] . '/csv_files/'; // 存放 CSV 的目录

    // 获取所有 .csv 或 .CSV 文件
    $csvFiles = glob($csvDir . '*.csv') ?: [];
    $csvFiles = array_merge($csvFiles, glob($csvDir . '*.CSV') ?: []);

    foreach ($csvFiles as $filePath) {
        $filename = basename($filePath);
        $tableName = filenameToTableName($filename);

        echo "Processing {$filename} → table `{$tableName}`\n";

        // 检查表是否存在（可选，增强健壮性）
        $exists = $db->query("SHOW TABLES LIKE '{$tableName}'")->fetch();
        if (!$exists) {
            echo "⚠️  Table `{$tableName}` does not exist. Skipping.\n";
            continue;
        }

        // 解析 CSV 并逐行导入
        $csvData = DB::parseCsv($filePath);
        $successCount = 0;

        foreach ($csvData as $row) {
            if ($db->insertRow($tableName, $row)) {
                $successCount++;
            }
        }

        echo "✅ Imported {$successCount}/" . count($csvData) . " rows into `{$tableName}`\n";
    }

} catch (Exception $e) {
    error_log("Import failed: " . $e->getMessage());
    echo "Error: " . $e->getMessage();
}

⚠️ 关键注意事项

安全性第一：绝不拼接 SQL 字符串！必须使用预处理语句（prepare() + bindValue()）防止注入。
字符集统一：连接 DSN 中显式指定 charset=utf8mb4，避免中文乱码。
表结构一致性：CSV 表头字段名必须与目标表列名完全一致（大小写敏感取决于 MySQL 配置），否则 array_combine() 会失败。
大文件优化：单次导入超万行时，建议改用 LOAD DATA INFILE（需服务器权限）或分批次 INSERT ... VALUES (...), (...), ...。
错误监控：生产环境应记录失败文件、行号及错误详情，便于重试与排查。

通过以上方案，您即可实现零人工干预的 CSV 到 MySQL 自动化同步——文件名即表名，结构即契约，安全即默认。