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 the csv file 11 statement-based CleverCode. in actual work, you often need 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.
