How to solve the newline problem of LoadData record in mysql _ MySQL

How to solve the newline problem of LoadData record in mysql bitsCN.com

The problem is as follows:

The persons table has two fields: id and name.
The content in the document persons.txt (tabs are used to separate fields in each line ):
1 Bush
2 Carter
3 Bush

Use load data local infile into persons.txt "into table persons in the mysql command line to import data to the persons table.

The imported data in the persons table is the same as that in the persons.txt table. However, use the statement
Select distinct name from persons
In the query results, Bush appears twice (the normal result should be that Bush only appears once ).

Cause analysis:

After analysis, it is found that the line break in windows is "/r/n", and mysql uses "/n" by default to cut each row of records when loading data, as a result, the invisible characters "/r" are inserted at the end of the name field of the first two records in the table. that is to say, the two Bush queries using the distinct keyword, there is a carriage return character "/r" at the end of the first word, but not at the end of the second word.
Note:
1. mysql uses tab by default to split fields in each row.
2. because the line break in linux is "/n", the above problem will not occur in linux.

Modification method:
You only need to specify the line feed with "/r/n" when importing data.
The modified import data statement is:

Load data local infile into persons.txt "into table persons
Lines terminated by "/r/n ";

BitsCN.com