MySQL导入CSV时quotechar含义及含单双引号数据导入问题咨询

Let me break this down clearly—dealing with CSV imports that have mixed single and double quotes is a common headache, but once you understand how quotechars work, it’s easy to fix.

1. What is quotechar, how does it work, and why can’t you import the full CSV directly? #

Let’s start with the basics:

• What it is: The quotechar (often called the "enclosure character") is a marker that tells your CSV parser: "Everything inside these characters is a single field—don’t split it up, even if it has commas, line breaks, or other special characters." By default, MySQL Workbench uses the double quote (") as this marker.
• How it works: When the parser sees the quotechar, it reads continuously until it hits the same quotechar again. If the data inside contains the same quotechar, it’s usually escaped by doubling it (e.g., a literal " becomes "" in the CSV). For example, your field 4" x 6" foo foo bar would be stored in the CSV as "4"" x 6"""—the outer quotes mark the field, and the doubled inner quotes tell the parser to treat them as literal characters.
• Why your import is failing:
  • When your data has both ' and ", the default parser gets confused if quotes aren’t properly escaped. If a field has an unescaped ", the parser might think the field ends early, truncating your data or shoving parts of it into the wrong column.
  • You can’t just omit the quotechar entirely because CSV relies on it to distinguish between structural characters (like commas that separate columns) and characters that are part of your actual data. Without it, the parser has no way to tell if a comma is a column separator or part of a product name, which throws the error you saw.

2. How to correctly import mixed-quote data (and keep it intact for search) #

Here are three reliable methods to get your data into MySQL without losing anything:

Method 1: Fix the Excel export first (and use the Workbench wizard) #

Excel handles quote escaping automatically when you save as UTF-8 CSV—you just need to make sure you’re doing it right:

1. When saving from Excel, choose CSV (UTF-8) as the format. Excel will wrap any field with quotes/commas in double quotes, and escape internal double quotes by doubling them (so 8'10" foo bar becomes "8'10"" foo bar").
2. In MySQL Workbench’s Import Wizard:
   • When you reach the "Configure Import Settings" step, keep Fields enclosed by set to " (default).
   • Set Fields escaped by to "—this tells the parser that doubled quotes are literal single quotes.
   • Make sure Lines terminated by matches your CSV’s line endings (usually \n for UTF-8 CSV from Excel, or \r\n if you’re on Windows).
   • Check "First row has column names" if your CSV includes headers.

Method 2: Use LOAD DATA INFILE (more control) #

If the wizard is giving you grief, using a MySQL query gives you full control over the import. Here’s a template (adjust values to match your setup):

LOAD DATA INFILE '/path/to/your/utf8_file.csv'
INTO TABLE your_target_table
CHARACTER SET utf8mb4 -- Supports all Unicode characters, including special quotes
FIELDS TERMINATED BY ','
ENCLOSED BY '"' -- This is your quotechar
ESCAPED BY '"' -- Tells MySQL to turn "" into " in the data
LINES TERMINATED BY '\n'
IGNORE 1 LINES; -- Add this if your CSV has a header row

• Single quotes (') don’t need special handling here—they’re just stored as-is, which is perfect for future search queries.

Method 3: Verify your table’s character set #

To ensure your data stays intact for search:

• Make sure your target table uses the utf8mb4 character set (not the older utf8). utf8mb4 supports all Unicode characters, so you won’t lose any special characters or quotes.
• When you run search queries later, remember to escape single quotes in your SQL (e.g., to search for 8'10" foo bar, use WHERE your_column LIKE '%8''10" foo bar%'—the doubled single quote tells MySQL it’s a literal character).

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