Questions tagged [into-outfile]

MySQL's `SELECT ... INTO OUTFILE` command lets you quickly dump the results of a MySQL query into a file on the MySQL server. It generally should not be used for production use.

MySQL's SELECT ... INTO OUTFILE command lets you quickly dump the results of a MySQL query into a file on the MySQL server. It generally should not be used for production use.

From the MySQL documentation

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.

One must be able have access to the MySQL server machine, and permissions to operate on files as the mysql user in order to access and manage the dump files. These restrictions mean the SELECT ... INTO OUTFILE command is generally suitable only for testing and debugging.

If you want a dump of a table from a MySQL database on your client machine, you can call the mysqldump binary on the client. If you want a CSV of the results of a MySQL query, you generally need to roll your own code to create that file in your client application, though there are libraries that can help with the CSV format.

139 questions
3
votes
3 answers

Query's result set is too big

I have a query that can be fast or slow depending on how many records I'm fetching. Here's a table showing the number in my LIMIT clause and the corresponding time it takes to execute the query and fetch the results: LIMIT | Seconds…
Jason Swett
  • 43,526
  • 67
  • 220
  • 351
3
votes
1 answer

Optimizing for SELECT INTO OUTFILE

I have a SELECT INTO OUTFILE query that can return up to ~160,000 rows or a few dozen megabytes of data. It takes a long time to run - like 20 minutes. If I do LIMIT 100 the query takes a fraction of a second, so it seems like my query is probably…
Jason Swett
  • 43,526
  • 67
  • 220
  • 351
3
votes
2 answers

Where does MariaDB `mysql` client save the output file of a select statement?

From this documentation: https://mariadb.com/kb/en/library/select-into-outfile/ I ran MariaDB []> select ... into outfile 'leads_ny.csv'; Query OK, 6674 rows affected (0.47 sec) Now I can't find the file. I looked in the current working directory,…
Chloe
  • 25,162
  • 40
  • 190
  • 357
3
votes
2 answers

printing weird symbols to output file, c++

I am writing a code that reads an input file of numbers, sorts them in ascending order, and prints them to output. The only thing printed to output is some really freaky symbols. Here is my code #include #include using namespace…
Morgan
  • 153
  • 1
  • 2
  • 14
3
votes
3 answers

mysql select into outfile without any line termination character

I try to print all the data from one field from a table into a file witout any line/field termination character. I would simply like to have all the rows concatenated and printed as one line in the output line. Here how I am doing it: CREATE TABLE…
3
votes
3 answers

java - mysql - select query outfile - where is file getting saved

connecting to a mysql database from java using jdbc. declaring a query String query = "SELECT *"+ "FROM tt2"+ "INTO OUTFILE 'DataFormatted.csv'"+ "FIELDS TERMINATED BY…
silverkid
  • 9,291
  • 22
  • 66
  • 92
3
votes
1 answer

LOAD DATA INFILE when fields real escaped fields contain \n and \"

Schema for my sms table is - sms(id, mobileNumber, text) where text is of type text. One of the row has below text - Foo bar\nLorem ipsum dolor sit amet.\n\nconsectetur adipisicing elit, said:\n\n\" sed do eiusmod tempor incididunt ut labore et…
Hussain
  • 5,057
  • 6
  • 45
  • 71
3
votes
1 answer

How to SELECT INTO OUTFILE with header labels enclosed by quotes but not body fields?

I do not believe this question is a duplicate. I want rows in the body to be "OPTIONALLY ENCLOSED BY" double quotes. Numerical values should not be enclosed. That's easy to do without a header. But when you include a header using UNION, MySQL now…
Buttle Butkus
  • 9,206
  • 13
  • 79
  • 120
2
votes
1 answer

Round Trip INTO OUTFILE, Excel Editing, and LOAD DATA INFILE without corruption

I have been trying to export a mysql table with INTO OUTFILE to CSV for editing in Excel and then inserting back into mysql using LOAD DATA INFILE. I can export ok, and even reinsert the unedited file. However, if I open in Excel make changes and…
christian
  • 2,279
  • 4
  • 31
  • 42
2
votes
1 answer

Using sqlalchemy to generate: SELECT * ... INTO OUTFILE "file";

I have recently started using SQLALCHEMY to query a my-sql database. I want to generate a select statement that uses the "INTO OUTFILE " syntax to export query results to a test file. For example: SELECT * FROM table INTO OUTFILE…
Eric
  • 331
  • 1
  • 2
  • 11
2
votes
1 answer

How to get all rows from table using MySQL OUTFILE

I'm using MySQL to output the contents of a database table to a CSV file using the following syntax: SELECT 'Keyword_1_Frequency', 'Keyword_2_Frequency', 'Keyword_3_Frequency', 'Keyword_4_Frequency', 'Keyword_5_Frequency', 'Keyword_6_Frequency',…
Mr Morgan
  • 23
  • 2
2
votes
2 answers

Use MySQL into OUTFILE to generate Excel readable UTF8 data

I have a problem similar to this question. That is - I need to export some UTF8 data within a MySQL database to MS Excel. The gotchas kindly Excel provides: Excel opens UTF8 formatted CSV files as ANSCI, thus breaking Excel will open…
Jack Shepherd
  • 165
  • 4
  • 12
2
votes
0 answers

Problems exporting a table in mysql with INTO OUTFILE in MAC OS

I have a MAC OS X with MySQL Community server. I just finished my query and I have one table with 6,000,000 rows and 32 columns. If I try to select * in the table, the program show me the error Error Code: 2008 (mysql client ran out of memory) I…
2
votes
2 answers

How To Use Zend Framework To Export To CSV Using mySQL's INTO OUTFILE Functionality

I am looking to export a large amount of data into a CSV file for user download using Zend Framework. Is there any way to use Zend_Db's functionaity and use the "INTO OUTFILE" syntax to output the file as a csv? Basically I want to be able to adapt…
Brendan
  • 21
  • 1
  • 2
2
votes
2 answers

how can i save the file after SELECT ... INTO OUTFILE 'result.csv'

i use this query to dump table into csv file : $sql = "SELECT * INTO OUTFILE 'result.csv' FIELDS TERMINATED BY ';' FROM tableName"; i get a result.csv file in the db folder of mysql how can i save it at root of my site ?
cranberies
  • 441
  • 3
  • 9
  • 23
1 2
3
9 10