SQL statement for SELECT INTO outfile

SELECT into ... The OUTFILE statement exports the table data to a text file and uses the load data ... The infile statement restores the data. However, this method can only export or import the contents of the data, not including the structure of the table, if the structure of the table is damaged, you must first restore the original table structure.

First, SELECT into ... outfile Syntax:

SELECT * from Table to outfile '/path/filename '
Fields terminated by ', '
Enclosed by ' "'
Lines terminated by ' \ r \ n '

(1) The path directory must have read and Write permissions 777
(2) file name must be unique
(3) fields terminated by ', ' must exist, otherwise the column of the open file appears in the same cell
(4) I verify that the table structure is GBK, otherwise garbled

Fields clause: There are three Yazi sentences in the fields clause: TERMINATED by, [optionally] enclosed by and escaped by. If you specify a fields clause, you must specify at least one of these three Yazi sentences.

(1) TERMINATED by is used to specify the symbol between field values, for example, "TERMINATED by", "" specifies a comma as a flag between two field values.

(2) The enclosed by clause is used to specify the symbol for the character value in the package file, for example, "enclosed by" "means that the character value in the file is placed between the double quotation marks, and if the keyword optionally means that all values are placed between the double quotation marks.

(3) The escaped by clause is used to specify the escape character, for example, "escaped by '" designates "*" as an escape character, instead of "\", such as a space that is represented as "*n".

LINES clause: In the LINES clause, use TERMINATED by specifying a line to end the flag, such as "LINES TERMINATED by '?" Represents a row with "?" As the end flag.

Second, LOAD DATA ... InFile statement is SELECT INTO ... The complement of the OUTFILE statement, which can import data from a file into a database.

SQL statements

SELECT * FROM
(SELECT ' Phoneno ', ' taskname ', ' Status ', ' dialdtimes ', ' Agent ', ' dialtime ', ' dialendtime ', ' dialresult ', ' talkendtime '
From T_title
UNION ALL
SELECT * FROM
(select T.phoneno, ' Task_20_2 ' as Name,ifnull (B.status_name, ') as Status_name, Ifnull (T.dialedtimes, '), Ifnull (T. Agentaccount, '), Ifnull (T.dialtime, '), Ifnull (T.dialendtime, '), ' not called ' as Result,ifnull (T.talkendtime, ') from T_od_ Taskphoneno T
Left JOIN Biz_outdial.phone_status
B on t.status = b.status_id where t.enterprise_id = "30092638" and TaskId = "$" and dialtime are null ORDER by id DESC li MIT 10
As TMP1) as TMP
into outfile '/mnt/backup/test00001.csv '
Fields terminated by ', '
Enclosed by ' "'
Lines terminated by ' \ r \ n

1. The difference between Union all and union

Union, which is combined with two result sets, excluding duplicate rows, and sorting the default rules;

Union all, which is set up for two result sets, including repeating rows, without sorting;

Intersect, the intersection of two result sets, excluding duplicate rows, and the sorting of the default rules;

Minus, differential operation on two result sets, excluding duplicate rows, and sorting of default rules

2.IFNULL (T.agentaccount, "), or/n instead of the default value. Because do not want this effect, so add ifnull (T.agentaccount, ") This limit condition

3.as TMP1 must have, otherwise order by ID DECs prompt "id not present" error when sorting

SQL statement for SELECT INTO outfile