How to import csv data to mysql

Source: Internet
Author: User
This article provides a detailed analysis of how to import csv data into mysql. For more information, see mysql's own csv engine, you can use this engine to import csv data to a mysql database, which is much faster than a batch processing program written in php or python.
Specific implementation code example:

The code is as follows:


Load data infile '/tmp/file.csv' into table _ tablename (set character utf8)
Fields terminated ','
Enclosed '"'
Lines terminated by '\ r \ n ';


Some keywords involved in this code are explained as follows:
Fields terminated by '': specifies the Field Terminator in the csv file, that is, the delimiter between data;
Enclosed by '': specifies the envelope character;
Lines terminated by '': indicates the line terminator.
The csv format is described in detail in the csv document (RFC4180). The main points are:
(1) fields are separated by commas (,), and data rows are separated by \ n;
(2) the string is enclosed by double quotation marks, and the double quotation marks of the string itself are represented by two double quotation marks.
Through the above explanation, we should have a better understanding of the data import code.

Similarly, csv data can be imported into the mysql database, and data tables in mysql can also be exported to csv files. the exported code example is as follows:

The code is as follows:


Select * from tablename into outfile '/tmp/data.txt'
Fields terminated ','
Optionally enclosed '"'
Lines terminated by '\ n ';


After exporting data from the database to a file, you must follow the format defined in the exported file before importing the data to the database.

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.