MariaDB Load Data Infile无法读取最后一行问题求助

The root of your issue is that MySQL's LOAD DATA INFILE expects rows to be terminated by a newline character by default. When your last CSV line ends with a comma (for an empty field) but no newline, MySQL doesn't recognize that the row is complete—it stops parsing after the 9th field (black) and thinks you're missing the 10th column, hence the error.

Here are three straightforward solutions, ordered by simplicity:

1. Add a trailing newline to your CSV during generation #

This is the cleanest fix. Ensure every line in your CSV (including the last one) ends with a newline character. If you're building the CSV manually in PHP, adjust your code to append a newline after the final row:

// Example: If you have an array of CSV lines
$csvLines = [
    // ... your existing lines ...
    "58,5/3/17,8:30 PM,Jazz L6/7,0,,Thursday,1074,black,"
];

// Join lines with newlines and add a trailing newline
$csvContent = implode("\n", $csvLines) . "\n";
file_put_contents('your_data.csv', $csvContent);

If you're using fputcsv, note that it automatically adds a newline after each row—double-check that you aren't overriding this behavior with custom string handling.

2. Tweak your LOAD DATA INFILE query #

If modifying the CSV isn't feasible, adjust your query to handle rows without a trailing newline. For example, if your table has 10 columns, you can explicitly map the fields and set the last column to an empty string or NULL if it's missing:

LOAD DATA INFILE '/path/to/your_data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
-- Map the first 9 columns directly
(column1, column2, column3, column4, column5, column6, column7, column8, column9)
-- Set the 10th column to empty string if it's missing
SET column10 = '';

This works because MySQL will populate the first 9 columns from the last line, and explicitly set the 10th to an empty value, avoiding the "missing columns" error.

3. Preprocess the CSV before loading #

If you can't change the CSV generation or query, add a trailing newline to the file in PHP right before running the LOAD DATA command:

$csvPath = 'your_data.csv';
$content = file_get_contents($csvPath);

// Check if the file doesn't end with a newline
$lastChar = substr($content, -1);
if ($lastChar !== "\n" && $lastChar !== "\r") {
    $content .= "\n";
    file_put_contents($csvPath, $content);
}

// Now run your LOAD DATA query
$query = <<<EOF
TRUNCATE TABLE your_table;
LOAD DATA INFILE '/path/to/your_data.csv'
INTO TABLE your_table
FIELDS TERMINATED BY ',';
EOF;
// Execute the query...

Why adding "test" or a newline fixes it #

• Adding "test" after the comma gives MySQL a 10th field, so it sees all required columns and accepts the row (even without a newline).
• Adding a newline tells MySQL that the row ends at that point, so it recognizes the trailing comma as an empty 10th field.

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