PHP无需手动指定字段自动建表导入CSV全量数据实现方案

现有问题 #

当前定时导入脚本为硬编码实现，存在以下局限：

• 需手动指定目标表名、字段映射关系
• CSV新增列必须修改代码才能适配
• 共享主机环境无MySQL SUPER权限，无法使用LOAD DATA LOCAL INFILE/LOAD DATA INFILE快速导入语句

原有硬编码代码如下：

<?php

include_once '/db-connection.php';

$csvFilePath = "stock_sold.csv";
$file = fopen($csvFilePath, "r");
fgets($file);
while (($row = fgetcsv($file)) !== FALSE) {
    $stmt = $conn->prepare("INSERT INTO `stock_sold` 
                (`sku`, `stock_sold`, `id`) VALUES (?, ?, ?)");
    $stmt->bind_param("sdi", $row[0], $row[1], $row[2]);
    $stmt->execute();
}

核心需求 #

• 自动以CSV文件名作为MySQL表名，不存在则自动建表
• 自动识别CSV表头完成全量字段导入，无需手动预设字段
• CSV后续新增列时，自动给目标表加字段，无需修改代码即可完成导入

实现思路 #

全程使用普通DML/DDL语句实现，仅需基础的CREATE、ALTER、SELECT、INSERT权限，完全适配共享主机限制：

• 预处理CSV文件名，过滤非法字符作为合规的MySQL表名
• 读取CSV第一行作为表头，过滤非法字符生成合规的MySQL字段名
• 检查目标表是否存在，不存在则自动建表，默认加自增主键提升查询性能，所有CSV字段默认使用TEXT类型最大化兼容性
• 表已存在时，自动对比表现有字段和当前CSV表头，缺失字段自动执行ALTER语句新增
• 动态生成INSERT预处理语句，适配任意列数的CSV导入，无需手动绑定参数

完整实现代码 #

<?php
include_once '/db-connection.php';

// 配置CSV路径
$csvFilePath = "stock_sold.csv";

// --------------------------
// 1. 预处理合规表名
// --------------------------
$tableRawName = pathinfo($csvFilePath, PATHINFO_FILENAME);
// 替换非字母、数字、下划线的字符为下划线，避免SQL语法错误
$tableName = preg_replace('/[^a-zA-Z0-9_]/', '_', $tableRawName);

// --------------------------
// 2. 读取并处理CSV表头
// --------------------------
$file = fopen($csvFilePath, "r");
if (!$file) {
    die("无法打开CSV文件");
}
// 读取第一行表头
$rawHeaders = fgetcsv($file);
if (!$rawHeaders) {
    die("CSV文件为空或表头读取失败");
}
// 处理表头为合规字段名
$fields = [];
foreach ($rawHeaders as $header) {
    $fieldName = trim($header);
    // 替换非法字符为下划线
    $fieldName = preg_replace('/[^a-zA-Z0-9_]/', '_', $fieldName);
    // 空字段名兜底
    if (empty($fieldName)) {
        $fieldName = 'field_'.count($fields);
    }
    $fields[] = $fieldName;
}
// 字段去重，避免重名报错
$fields = array_unique($fields);

// --------------------------
// 3. 检查表是否存在，不存在则建表
// --------------------------
$checkTableStmt = $conn->prepare("SHOW TABLES LIKE ?");
$checkTableStmt->bind_param("s", $tableName);
$checkTableStmt->execute();
$tableExist = $checkTableStmt->get_result()->num_rows > 0;
$checkTableStmt->close();

if (!$tableExist) {
    // 建表SQL，加自增主键避免无主键表性能问题
    $createSql = "CREATE TABLE `{$tableName}` (
        `_auto_import_pk` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY";
    foreach ($fields as $field) {
        $createSql .= ", `{$field}` TEXT DEFAULT NULL";
    }
    $createSql .= ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;";
    $conn->query($createSql);
} else {
    // --------------------------
    // 4. 表存在时检查是否有新增字段，自动加列
    // --------------------------
    $existColumns = [];
    $colRes = $conn->query("SHOW COLUMNS FROM `{$tableName}`");
    while ($col = $colRes->fetch_assoc()) {
        $existColumns[] = $col['Field'];
    }
    foreach ($fields as $field) {
        if (!in_array($field, $existColumns)) {
            // 新增缺失字段，用TEXT类型兼容所有内容
            $alterSql = "ALTER TABLE `{$tableName}` ADD COLUMN `{$field}` TEXT DEFAULT NULL;";
            $conn->query($alterSql);
        }
    }
}

// --------------------------
// 5. 动态构造INSERT语句批量导入
// --------------------------
$fieldStr = implode('`, `', $fields);
$placeholderStr = implode(', ', array_fill(0, count($fields), '?'));
$insertSql = "INSERT INTO `{$tableName}` (`{$fieldStr}`) VALUES ({$placeholderStr})";
$insertStmt = $conn->prepare($insertSql);
// 所有字段按字符串类型绑定，TEXT类型兼容任意格式内容，无需单独判断类型
$bindTypes = str_repeat('s', count($fields));

// 逐行导入数据
while (($row = fgetcsv($file)) !== FALSE) {
    // 容错：列数和表头不匹配时自动补空/截断
    $row = array_pad(array_slice($row, 0, count($fields)), count($fields), '');
    // 动态绑定参数
    $bindParams = array_merge([$bindTypes], $row);
    foreach ($bindParams as $k => $v) {
        $bindParams[$k] = &$bindParams[$k];
    }
    call_user_func_array([$insertStmt, 'bind_param'], $bindParams);
    $insertStmt->execute();
}

// 资源释放
$insertStmt->close();
fclose($file);
$conn->close();

注意事项 #

• 所有CSV导入字段默认使用TEXT类型，无需提前识别字段格式，可兼容字符串、数字、日期等任意内容，避免字段长度不足、类型不匹配导致的导入失败，若后续有查询性能需求可手动调整字段类型、加索引
• 自动过滤表名、字段名中的特殊字符，避免SQL语法错误，同时对空表头、重名字段做了兜底处理
• 全程使用预处理语句执行写入操作，避免SQL注入风险
• 仅使用MySQL基础权限，不需要SUPER权限，完全适配绝大多数共享主机环境
• CSV新增任意列时，脚本下次执行会自动识别并给表加对应字段，无需修改任何代码

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