char在mysql中的意思,导入数据时，quotechar在mysql中是什么意思？

Someone sent me a xlsx excel file and I opened it with excel and saved as csv with utf8 encoding.

I use mysql workbench import wizard to import an excel-made utf8 csv file to a database table. But the imported result missed some data (less than it should have).

And I think it has something to do with the quotechar.

By default the quotechar is double quote but I have some data like this (mixing single quote and double quote):

8'10" foo bar

4" x 6" foo foo bar

I've try to omit the value but it can't (see the error from the pic).

So here I want to figure out:

What does quotechar mean here? How does it work? Why does it matter? Can't it just import everything from the csv file?

How can I import the data correctly while my data mixes single quote and double quote (later I need to retrieve them and use as search keywords, so it'd be better to keep the original form)?

my data looks like this in excel:

解决方案

You are going to export your data from Excel as a CSV, I assume, so how this looks in Excel is irrelevant.

When you export the data from excel as a CSV format it's going to encapsulate your data in double quotes. Any double quotes in the data itself is going to be escaped by a second double quote automatically by excel.

As an example, if your data is:

8"

When you export it will be:

"8"""

You have to tell Mysql that you are enclosing strings in character ". That is the quotechar it's talking about. It's the second field on that form you are filling out.

I'm not sure how picky MySQl is going to be here since I haven't imported CSV to Mysql in forever and ever and ever. The trick with the Excel CSV output is that if you have data like:

8"

8'

It will output it as CSV:

"8"""

8'

The second record/field doesn't gain the double quote encapsulation since it doesn't contain a character that requires encapsulation (A double quote, a carriage return, or a line feed).

Mysql might choke on that second record (Hopefully it's import process is robust enough to handle encapsulated and non-encaps'd fields though)