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

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.