How to solve the line feed problem of load data record in MySQL

Source: Internet
Author: User

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 character "\ r" is 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, the first word ends with a carriage return character "\ r", but the second word ends with no.
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 "\ r \ n" to wrap the line when importing data.
The modified import data statement is:

CopyCodeThe Code is as follows: load data local infile into persons.txt "into Table persons
Lines terminated by "\ r \ n ";
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.