The problem is this:
Table persons has two fields: ID and name
Content in text document Persons.txt (with tab split between each row of fields):
1 Bush
2 Carter
3 Bush
Use the load data local infile "persons.txt" into table persons to import data into the persons table under the MySQL command line.
View the data of the persons table after import, consistent with the contents of the Persons.txt. But using statements
Select distinct name from persons
Query, the result is that Bush appeared two times (the normal result should be bush only once).
Reason Analysis:
After analysis, found that the reason is that Windows line break is "\ r \ n", and MySQL in the load data by default to use "\ n" to cut each row of records, resulting in the table inserted into the first two records in the end of the name field more than inserted invisible character "\ r" The first of the two Bush queries using the DISTINCT keyword has a carriage return "\ R" and the second suffix does not.
Description
1. mysql uses tab to divide the fields of each row by default.
2. Because the Linux line break is "\ n", so the above problems will not occur under Linux.
To modify the method:
As long as you specify "\ r \ n" for the data to be imported, you can change the line.
The modified import data statement is:
Copy Code code as follows:
Load data local infile "persons.txt" into table persons
Lines terminated by "\ r \ n";