LOAD DATA LOCAL INFILE导入CSV时最后一行丢失问题求助

Got it, let's break down why your last line isn't being imported and fix that statement right up.

The Root Cause #

Your problem boils down to a mismatch between how your CSV escapes double quotes and how MySQL parses CSV by default. Look at that problematic line:

"AAAAA","DDDDD","file C:\hghjghj\gfhfhg\ssss\""

You're using \" to escape the trailing double quote in the file path, but MySQL's default CSV rules expect double double quotes ("") to escape a quote inside a field. When MySQL sees \", it treats the backslash as a literal character and the following " as part of the field content—not the closing quote for the field. This means MySQL thinks the field never ends, so it gobbles up the next line's content into that broken field, leaving the last line unrecognized as a separate row.

The Fix: Adjust the Escape Character #

We need to explicitly tell MySQL to use the backslash as the escape character, so it correctly interprets \" as an escaped quote. Here's the modified LOAD DATA statement:

LOAD DATA LOCAL INFILE 'Import/file.TXT'
INTO TABLE `cree_re_import`
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'  -- Double backslash because SQL requires escaping the backslash itself
LINES TERMINATED BY '\r\n';

A Quick Note on the Double Backslash

In SQL syntax, the backslash is a special escape character, so to pass a single literal backslash to MySQL's ESCAPED BY parameter, we need to write two backslashes (\\). MySQL will parse this as a single backslash, which will then correctly handle the \" sequences in your CSV.

This change ensures:

• Trailing \" in fields are recognized as escaped quotes, closing the field properly
• Windows-style path backslashes (like C:\hghjghj) are preserved as literal characters in your database
• The final row is parsed as a separate, valid entry

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