Laravel导入13万行CSV遇SQL整数格式错误求助

我完全明白你遇到的困扰——明明数据表所有字段都设为了nullable，但用fast-excel和csv league导入13万行数据时，还是触发了PostgreSQL的类型转换错误。核心原因其实很清晰：

PostgreSQL对于integer类型字段，哪怕允许为null，也不接受空字符串""作为有效值，它只认NULL。而Laravel Excel内部帮你自动把空字符串转换成了null，但fast-excel和csv league没有做这个处理，直接把CSV里的空值（空字符串）传给了数据库，所以才会报la sintaxis de entrada no es válida para integer: «»这个错误。

下面给你几个可行的解决方案，结合你的代码场景优化：

方案1：手动给字段添加空值转换逻辑 #

你可以写一个简单的辅助函数，批量把空字符串转换成null，然后在导入时调用：

/**
 * 将空字符串转换为null，保留其他有效值
 */
function convertEmptyToNull($value) {
    return $value === '' ? null : $value;
}

修改csv league的导入代码： #

$csv = Reader::createFromPath(storage_path('/app/uso_suelo.csv'));
$csv->setHeaderOffset(0);
$records = $csv->getRecords();

foreach ($records as $offset => $record) {
    UsoSuelo::create([
        'cod_pais' => convertEmptyToNull($record['COD_PAIS']),
        'cod_fundo' => convertEmptyToNull($record['COD_FUNDO']),
        'nom_fundo' => convertEmptyToNull($record['NOM_FUNDO']),
        'cod_uso_suelo' => convertEmptyToNull($record['COD_USOSUELO']),
        'dsc_uso_suelo' => convertEmptyToNull($record['DSC_USOSUELO']),
        'cod_rodal' => convertEmptyToNull($record['COD_RODAL']),
        'cod_especie' => convertEmptyToNull($record['COD_ESPECIE']),
        'dsc_especie' => convertEmptyToNull($record['DSC_ESPECIE']),
        'ano_plantacion' => convertEmptyToNull($record['ANO_PLANTACION']),
        'cod_esquema' => convertEmptyToNull($record['COD_ESQUEMA']),
        'dsc_esquema' => convertEmptyToNull($record['DSC_ESQUEMA']),
        'dsc_restriccion_suelo' => convertEmptyToNull($record['DSC_RESTRICCION_SUELO']),
        'sup_ha' => convertEmptyToNull($record['SUP_HA']),
        'cod_z_cpino' => convertEmptyToNull($record['COD_Z_CPINO']),
        'cod_z_prod' => convertEmptyToNull($record['COD_Z_PROD']),
        'cod_sz_prod' => convertEmptyToNull($record['COD_SZ_PROD']),
        'dsc_sz_prod' => convertEmptyToNull($record['DSC_SZ_PROD']),
        'id_area_us' => convertEmptyToNull($record['ID_AREA_US'])
    ]);
}

修改fast-excel的导入代码： #

$usoSuelo = (new FastExcel)->import(storage_path('/app/uso_suelo.csv'), function ($line) {
    return UsoSuelo::create([
        'cod_pais' => convertEmptyToNull($line['COD_PAIS']),
        'cod_fundo' => convertEmptyToNull($line['COD_FUNDO']),
        'nom_fundo' => convertEmptyToNull($line['NOM_FUNDO']),
        'cod_uso_suelo' => convertEmptyToNull($line['COD_USOSUELO']),
        'dsc_uso_suelo' => convertEmptyToNull($line['DSC_USOSUELO']),
        'cod_rodal' => convertEmptyToNull($line['COD_RODAL']),
        'cod_especie' => convertEmptyToNull($line['COD_ESPECIE']),
        'dsc_especie' => convertEmptyToNull($line['DSC_ESPECIE']),
        'ano_plantacion' => convertEmptyToNull($line['ANO_PLANTACION']),
        'cod_esquema' => convertEmptyToNull($line['COD_ESQUEMA']),
        'dsc_esquema' => convertEmptyToNull($line['DSC_ESQUEMA']),
        'dsc_restriccion_suelo' => convertEmptyToNull($line['DSC_RESTRICCION_SUELO']),
        'sup_ha' => convertEmptyToNull($line['SUP_HA']),
        'cod_z_cpino' => convertEmptyToNull($line['COD_Z_CPINO']),
        'cod_z_prod' => convertEmptyToNull($line['COD_Z_PROD']),
        'cod_sz_prod' => convertEmptyToNull($line['COD_SZ_PROD']),
        'dsc_sz_prod' => convertEmptyToNull($line['DSC_SZ_PROD']),
        'id_area_us' => convertEmptyToNull($line['ID_AREA_US'])
    ]);
});

方案2：在模型中全局自动处理（更优雅） #

你可以在UsoSuelo模型里重写setAttribute方法，实现全局的空字符串转null逻辑，这样所有创建模型的场景都会自动处理：

class UsoSuelo extends Model
{
    // 模型其他代码...

    public function setAttribute($key, $value)
    {
        // 针对所有integer类型字段，自动将空字符串转为null
        $columnType = $this->getConnection()->getDoctrineColumn($this->getTable(), $key)->getType()->getName();
        if ($columnType === 'integer' && $value === '') {
            $value = null;
        }
        parent::setAttribute($key, $value);
    }
}

这样你原有的导入代码不需要任何修改，就能自动解决空字符串的问题。

额外优化：批量插入提升13万行数据的导入速度 #

循环调用create会产生大量数据库请求，速度极慢，建议改成批量插入（每1000条一批）：

以csv league为例：

$csv = Reader::createFromPath(storage_path('/app/uso_suelo.csv'));
$csv->setHeaderOffset(0);
$records = $csv->getRecords();

$batch = [];
$batchSize = 1000; // 可根据服务器性能调整

foreach ($records as $offset => $record) {
    // 先处理空值
    $processedRecord = array_map('convertEmptyToNull', $record);
    // 映射到模型字段
    $batch[] = [
        'cod_pais' => $processedRecord['COD_PAIS'],
        'cod_fundo' => $processedRecord['COD_FUNDO'],
        'nom_fundo' => $processedRecord['NOM_FUNDO'],
        'cod_uso_suelo' => $processedRecord['COD_USOSUELO'],
        'dsc_uso_suelo' => $processedRecord['DSC_USOSUELO'],
        'cod_rodal' => $processedRecord['COD_RODAL'],
        'cod_especie' => $processedRecord['COD_ESPECIE'],
        'dsc_especie' => $processedRecord['DSC_ESPECIE'],
        'ano_plantacion' => $processedRecord['ANO_PLANTACION'],
        'cod_esquema' => $processedRecord['COD_ESQUEMA'],
        'dsc_esquema' => $processedRecord['DSC_ESQUEMA'],
        'dsc_restriccion_suelo' => $processedRecord['DSC_RESTRICCION_SUELO'],
        'sup_ha' => $processedRecord['SUP_HA'],
        'cod_z_cpino' => $processedRecord['COD_Z_CPINO'],
        'cod_z_prod' => $processedRecord['COD_Z_PROD'],
        'cod_sz_prod' => $processedRecord['COD_SZ_PROD'],
        'dsc_sz_prod' => $processedRecord['DSC_SZ_PROD'],
        'id_area_us' => $processedRecord['ID_AREA_US'],
        'created_at' => now(),
        'updated_at' => now()
    ];

    // 每攒够一批就插入
    if (count($batch) >= $batchSize) {
        UsoSuelo::insert($batch);
        $batch = [];
    }
}

// 插入剩余的不足一批的数据
if (!empty($batch)) {
    UsoSuelo::insert($batch);
}

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