Alright, let's work through this problem step by step. You're dealing with two key issues: mismatched column names between your CSV and MySQL table, and needing to strip the leading $ from the salary values. Since mysqlimport is just a command-line wrapper for MySQL's LOAD DATA INFILE statement, we can leverage that underlying functionality to handle both challenges.

First, Clarify the Column Mapping #

Let's align your CSV columns to your TABLE_A columns clearly:

• CSV id → TABLE_A.id (direct match)
• CSV first name → TABLE_A.name (name mismatch)
• CSV emp salary → TABLE_A.salary (name mismatch + needs $ removed)

The mysqlimport Command to Handle Both Issues #

We'll use user variables to temporarily store the raw CSV values, then transform the salary and map everything to the correct table columns. Here's the full command:

mysqlimport --local --user=your_db_user --password=your_db_password \
  --columns='id,@temp_name,@temp_salary' \
  --set='name=@temp_name,salary=SUBSTRING(@temp_salary, 2)' \
  your_database_name /path/to/file.csv

Breakdown of Key Parameters: #

• --local: Tells mysqlimport to read the CSV from your local machine (instead of looking for it on the MySQL server)
• --columns: Defines the order of columns in your CSV. We use @-prefixed variables for the columns that need mapping/cleaning (since we can't directly modify them here)
• --set: Maps the temporary variables to your actual table columns. SUBSTRING(@temp_salary, 2) removes the first character (the $) from the salary value before inserting it into the salary column
• If your CSV uses quotes around values (e.g., "John", "$500"), add --fields-enclosed-by='"' to handle that correctly

Equivalent LOAD DATA INFILE Statement (For Reference) #

If you prefer running this directly in MySQL (instead of using the command-line tool), here's the equivalent SQL. It's often easier to tweak and test this way:

LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE TABLE_A
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(id, @temp_name, @temp_salary)
SET name = @temp_name, salary = SUBSTRING(@temp_salary, 2);

Test with Your Sample Data #

Let's verify with your example CSV rows:

1, John, $500
2, Mary Bell, $600

After running the command/query, TABLE_A will have these rows:

Critical Notes to Avoid Errors #

• Ensure your MySQL user has the FILE privilege, and that local_infile is enabled (run SET GLOBAL local_infile = 1; in MySQL if you get permission errors)
• If your CSV has a header row (e.g., id,first name,emp salary), add --ignore-lines=1 to the mysqlimport command (or IGNORE 1 LINES in the LOAD DATA statement) to skip it
• Double-check the order of columns in --columns matches exactly how they appear in your CSV—this is a common source of bugs

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