CSV file import MySQL garbled and other problems (reprint)

Import the *.csv file to MySQL using the load DATA INFILE,

Outline of the wording:

Load data in File

Specific syntax use can be referred to: Http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#load-data

If only English characters are present in a table, the import does not appear to be problematic, and the writing is extremely simple:

LOAD DATA LOCAL INFILE ' d:\\ports.csv ' into TABLE ports;

Problem one: Chinese charactersexist in the Ports.csv file and are garbled after importing data

First, modify the encoding of tables and fields

ALTER TABLE ports character set gbk2312;
ALTER TABLE ports modify Port_chinese varchar (character set gbk2312;

Use Show create TABLE ports to see how tables and fields are encoded, and, of course, to modify the encoding format of a field by using a visualizer

The command line I have imported is as follows:

LOAD DATA LOCAL INFILE ' d:\\ports.csv ' into TABLE ports fields TERMINATED ', ' enclosed by ' "' LINES TERMINATED by ' \ r \ n ' Starting by ';

But I modified the above two items after I import data is still garbled, my CSV file encoding format is GBK,

Find an article on this issue on the Web: http://www.sqlstudy.com/sql_article.php?id=2008081901, use the wording in the article to modify the statement as follows:

LOAD DATA LOCAL INFILE ' d:\\ports.csv ' into TABLE ports character   Set gbk2312Fields TERMINATED ', ' enclosed by ' "' LINES TERMINATED by ' \ r \ n ' starting by ';

At this point garbled characters become the correct Chinese characters.

Problem two: when you view data after importing data, you find that the first row of valid data in the CSV file is missing, and that there are three rows of weird invalid data rows, and there are 39 warnings when you execute the import command

First I use Show WARNINGS, the command to view the warning contents of the prompt, found in the CSV file column header import the database has more than n warning, and the key reason for data errors in LINES TERMINATED by ' \ r \ n ' the sentence, Because the first column header does not end with a carriage return newline character, the first row of header columns fails during the conversion process and causes the first row of valid data to appear incorrectly during the import process. I save the data after adding a carriage return to the last column header of the file title in the CSV file, and the first row of valid data in the CSV is finally imported into the data, but there is more than one row of header row data, which can be resolved by the Ignore command.

The final CSV import command line is written as follows:

LOAD DATA LOCAL INFILE ' d:\\ports.csv ' into TABLE ports character set gbk2312 fields TERMINATED ', ' enclosed by ' "' LIN ES TERMINATED by ' \ r \ n ' starting by ' IGNORE 1 LINES;

CSV file import MySQL garbled and other problems (reprint)