Mysql exports csv files separated by commas (,) and mysql exports csv files by commas (,).

Source: Internet
Author: User

Mysql exports csv files separated by commas (,) and mysql exports csv files by commas (,).

In actual work, CleverCode often needs to export some reports or log data. If you directly create a page, if the number of times is not large, the requirements are also different. Therefore, exporting csv files directly is more intuitive.

1 Export csv file 1.1 statement format SELECT [column name] FROM table [WHERE statement] [order by statement] [limit statement]
Into outfile 'destination file' [OPTION];
1.2 parameter description

The statement is divided into two parts. The first half is a normal SELECT statement. The SELECT statement is used to query the required data. The second half is the data exported. The "target file" parameter specifies the file to which the query record is exported. The "OPTION" parameter is an optional parameter, which may take the following values:

  • Fields terminated by 'string': Set the string to a delimiter between fields, which can be one or more characters. The default value is "\ t ".
  • Fields enclosed by 'characters': Set the character to enclose the field value. It can only be a single character. By default, no symbols are used.
  • Fields optionally enclosed by 'characters': Set characters to include character fields such as CHAR, VARCHAR, and TEXT. By default, no symbols are used.
  • Fields escaped by 'characters': Specifies the escape character, which can only be a single character. The default value is \.
  • Lines starting by 'string': Specifies the characters starting with data in each line, which can be one or more characters. By default, no characters are used.
  • Lines terminated by 'string': Specifies the character ending with each line of data, which can be one or more characters. The default value is \ n ".
1.3 important

Into outfile 'destination file', which is stored on the mysql server, because SQL statements are executed on the server. Therefore, the target file is saved on the mysql server. Into outfile '/tmp/test.csv'. Generally, the mysql server is on linux, and the into outfile 'e:/test.csv 'server is on windows. The target file must have the write permission and cannot exist.

1.4 example of installing mysql server in linux
select     * from proxy_list order by id asc limit 0,2 into outfile '/tmp/test.csv'  fields     terminated by ','     optionally enclosed by '"'     escaped by '"'      lines terminated by '\r\n'; 

1.5 example of installing mysql server on windows
select uid,nationfrom system_userorder by uid asclimit 0,2 into outfile 'e:/test.csv' fields terminated by ',' optionally enclosed by '"' escaped by '"' lines terminated by '\r\n'; 


1.6 error Example 1 no write permission

2. The file already exists.


Copyright statement: 1) original works are from the "CleverCode blog". Do not reprint them. Otherwise, the copyright is held legally liable.
2) original address: http://blog.csdn.net/clevercode/article/details/46965115.
3) classification address (Mysql database summary): http://blog.csdn.net/clevercode/article/category/3262205 (blog continues to increase, follow the favorite)
4) Welcome to my blog for more highlights: http://blog.csdn.net/clevercode.


Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

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.