动态导入任意列数 CSV 文件到 MariaDB 的灵活设计方案-php教程-PHP中文网

动态导入任意列数 CSV 文件到 MariaDB 的灵活设计方案

本文介绍一种不依赖预知列数的 csv 导入方案：通过“键值对+元数据”范式替代传统宽表建模，避免动态建表与 sql 注入风险，兼顾可扩展性、存储效率与查询灵活性。

本文介绍一种不依赖预知列数的 csv 导入方案：通过“键值对+元数据”范式替代传统宽表建模，避免动态建表与 sql 注入风险，兼顾可扩展性、存储效率与查询灵活性。

在实际业务中（如考勤日报、销售明细、传感器时序数据），CSV 文件常以“日期横向展开”形式组织——首行为字段名（如 ID;Turno;10/06/2022;11/06/2022;12/06/2022），列数动态变化。若强行为每份文件创建专属宽表（如 CREATE TABLE ... (ID VARCHAR, Turno VARCHAR,10/06/2022VARCHAR, ...)），将引发三大问题：

✅ SQL 注入风险：原始代码中直接拼接 $columns 生成 CREATE TABLE 语句，未过滤特殊字符（如分号、反引号、保留字），导致语法错误（如报错中的 ';Turno;10/06/2022;'）；
✅ 维护灾难：100 个不同结构的 CSV → 100 张表 → 字段命名冲突、索引冗余、DDL 变更不可控；
✅ 存储低效：宽表中大量单元格为空（如仅部分日期有数据），稀疏矩阵导致磁盘浪费与查询性能下降。

✅ 推荐方案：采用“归一化键值存储”模型

核心思想是解耦结构与数据：不为 CSV 列创建物理字段，而是将每一行的每个单元格抽象为一条记录，用元数据描述其归属关系。推荐表结构如下：

CREATE TABLE csv_imports (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  file_name VARCHAR(255) NOT NULL,
  line_no INT NOT NULL COMMENT 'CSV 行号（从 1 开始）',
  column_key VARCHAR(255) NOT NULL COMMENT '原 CSV 列名（如 "ID", "10/06/2022"）',
  column_value TEXT COMMENT '单元格值（支持 NULL）',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_file_line (file_name, line_no),
  INDEX idx_key_value (column_key, column_value(100))
);

✅ PHP 导入实现（安全、健壮）

以下代码替代原始 import.php，使用 PDO 预处理防止注入，并自动处理 CSV 头部解析与逐行写入：

Knowledge Graph - Csv Graph Loader Generator

从 CSV 数据集生成图数据库加载器和三元组映射。将表格 CSV 数据转换为图数据库可直接使用的节点、边和三元组。

下载

<?php require_once('config.php');

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_FILES['file']) && $_FILES['file']['error'] === UPLOAD_ERR_OK) {
    $file_path = $_FILES['file']['tmp_name'];
    $file_name = basename($_FILES['file']['name']);

    // 读取 CSV 头部获取列名
    $handle = fopen($file_path, 'r');
    if (!$handle) die("无法打开文件");

    $header = fgetcsv($handle, 0, ';'); // 注意：原图显示分隔符为分号 ';'
    if (!$header) die("CSV 无有效头部");

    // 初始化数据库连接
    try {
        $pdo = new PDO("mysql:host=$host;dbname=$database;charset=utf8mb4", $username, $password, [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
        ]);

        // 预处理插入语句（高效批量写入）
        $stmt = $pdo->prepare(
            "INSERT INTO csv_imports (file_name, line_no, column_key, column_value) 
             VALUES (?, ?, ?, ?)"
        );

        $line_no = 1;
        while (($row = fgetcsv($handle, 0, ';')) !== FALSE) {
            $line_no++;
            foreach ($header as $col_index => $column_key) {
                $column_value = $col_index execute([$file_name, $line_no, trim($column_key), $column_value]);
            }
        }

        fclose($handle);
        echo "<div class="alert alert-success">✅ 成功导入 {$line_no} 行数据到 csv_imports 表</div>";

    } catch (PDOException $e) {
        error_log("CSV 导入失败: " . $e->getMessage());
        echo "<div class="alert alert-danger">❌ 导入失败: " . htmlspecialchars($e->getMessage()) . "</div>";
    }
}

⚠️ 关键注意事项

分隔符一致性：根据你的截图，CSV 使用分号 ; 而非逗号 ,，务必在 fgetcsv() 中指定正确分隔符；
字段名清洗：生产环境需对 $column_key 进行标准化（如替换 /, -, 空格为下划线），避免后续 SQL 查询歧义；
大文件优化：超过 10MB 的 CSV 应启用事务分批提交（如每 1000 行 commit() 一次），防止内存溢出；

查询示例：要还原某行原始数据，可用：

SELECT column_key, column_value 
FROM csv_imports 
WHERE file_name = 'data.csv' AND line_no = 2 
ORDER BY FIELD(column_key, 'ID', 'Turno', '10/06/2022', '11/06/2022');

此方案彻底规避了动态建表的复杂性与风险，以极简模型支撑无限列扩展，同时保持 SQL 标准化、可审计、易索引。当业务需要“按日期聚合”或“跨文件比对”时，只需增加视图或物化查询，无需重构底层结构。