Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to export MySQL query results to a file". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to export MySQL query results to a file.

If you need to include a row number that represents the record in the entire MySQL query result set in the column returned by the query statement, the method proposed by the ISOSQL:2003 standard is to provide the ROW_NUMBER () / RANK () function. The standard method (version 8i and above) can be used in Oracle, or the non-standard ROWNUM;MSSQLServer provides the ROW_NUMBER () function in version 2005; however, there seems to be no such system-built-in functionality in MySQL. Although LIMIT can easily filter the number and location of returned result sets, the row numbers of filtered records cannot be SELECT. It is said that MySQL has long wanted to add this feature, but I haven't found it yet.

The solution is to predefine user variables:

Set@mycnt=0;select (@ mycnt:=@mycnt1) asROWNUM,othercolfromtblnameorderbyothercol

In this way, the ROWNUM in the query result set saves the row number information. Some use of this row numbering information is when you need to sort the data according to a certain rule and take out a row of data after sorting, and you want to know where the row of data is in the previous sort. For example:

Set@mycnt=0;select*from (select (@ mycnt:=@mycnt1) asROWNUM,othercolfromtblnameorderbyothercol) asAwhereothercol=OneKeyID

Of course, you can also write the query results to a temporary table with an auto_increment field by creating a temporary table, but consider the possible problems caused by the temporary table in MySQLmaster/slave mode. It is more concise to calculate the row number of each row of a query result set in this way of temporary user-defined variables-- unless you are willing to process the entire result set returned in PHP or other language scripts.

What are the methods of exporting MySQL query results to a file

Mysql > select1intooutfile'/tmp/t1.txt';QueryOK,1rowaffected (0.00sec) mysql > select1intooutfile'/tmp/t1.txt';ERROR1086 (HY000): File'/tmp/t1.txt'alreadyexists

You can also use another method:

Mysql > pagercat > / tmp/t1.txtPAGERsetto'cat > / tmp/t1.txt'mysql > select1;\! cat/tmp/t1.txt1rowinset (0.00sec) +-- + | 1 | +-+ | 1 | +-+

In this way, you can easily find the differences between the two sql items:

Mysql > pagercat > / tmp/t01.txtPAGERsetto'cat > / tmp/t01.txt'mysql > select12345unionselect67890;2rowsinset (0.02sec) mysql > pagercat > / tmp/t02.txtPAGERsetto'cat > / tmp/t02.txt'mysql > select12345unionselect67891;2rowsinset (0.00sec) mysql >\! vimdiff-o/tmp/t0 [12] .txt2filestoedit +-+ | 12345 | +-+ | 12345 | 67890 | +-+ / tmp/t01.txt+-+ | 12345 | +-+ | 12345 | 67891 | +-+ / tmp/t02.txt

At this point, I believe that everyone on the "MySQL query results how to export to the file" have a deeper understanding, might as well to the actual operation of it! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.