MySQL导入CSV仅首行插入且数据错误问题求助

Alright, let's tackle this CSV import issue you're facing—only the first row is inserting, and the data entries are incorrect. Looking at your provided code, there are a few critical mistakes causing these problems. Let's break them down and fix things properly.

Key Issues in Your Current Code #

1. Mismatched Variable Names
   You defined the variable @numEmps to capture empty numEmps values from your CSV, but in the SET clause, you tried to cast @emps (a non-existent variable) to an unsigned integer. This triggers an immediate conversion error, making MySQL stop importing after the first failed row.

2. Unhandled Empty Values
   Your CSV has empty entries for numEmps (like the first three rows). When you try to cast an empty string directly to UNSIGNED, strict SQL modes will throw an error and abort the entire import process.

3. Ambiguous Variable Naming
   Variables like @funded and @raised work, but using names that align with your table columns makes the code easier to read and debug.

Corrected Solution #

First, let's adjust the LOAD DATA statement to fix these issues. We'll also add safeguards for edge cases like empty values and potential field formatting:

-- Optional: Temporarily disable strict mode to avoid empty value conversion errors (if needed)
SET sql_mode = '';

LOAD DATA INFILE '/var/lib/mysql-files/TechCrunchcontinentalUSA.csv'
INTO TABLE fund
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'  -- Prevents issues if future fields contain commas
LINES TERMINATED BY '\n'
(permalink, company, @numEmps, category, city, state, @fundedDate, @raisedAmt, raisedCurrency, round)
SET 
    numEmps = CASE WHEN @numEmps = '' THEN NULL ELSE CAST(@numEmps AS UNSIGNED) END,
    fundedDate = STR_TO_DATE(@fundedDate, '%d-%b-%Y'),
    raisedAmt = CAST(@raisedAmt AS UNSIGNED);

What We Fixed: #

• Variable Name Match: Changed @emps to @numEmps in the SET clause so the variable references are consistent.
• Empty Value Handling: Used a CASE statement to set numEmps to NULL when the CSV value is empty, avoiding a failed cast that would halt imports.
• Readable Variable Names: Renamed @funded to @fundedDate and @raised to @raisedAmt to align with your table columns, making the code easier to follow.
• Added Field Enclosure: The OPTIONALLY ENCLOSED BY '"' clause is a best practice—even if your current CSV doesn't use quotes, it protects against future cases where fields might contain commas or special characters.

Post-Import Verification #

After running the corrected query, verify the results with:

SELECT * FROM fund;

You should see all CSV rows imported correctly, with numEmps as NULL where the CSV had empty values, and dates/amounts properly converted.

Bonus Optimization #

Your fund table defines raisedCurrency as longtext—since currency codes (like USD) are short, you can optimize this to VARCHAR(10) to save space and improve performance:

ALTER TABLE fund MODIFY COLUMN raisedCurrency VARCHAR(10);

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