CODEWITHSUNDEEP
mysqlmysql-workbench
think.arthur
think.arthur

Reputation: 705

Exporting query results in MySQL Workbench beyond 1000 records

I'm trying to save a query result of about 1,000,000 records in MySQL Workbench.

When I run the SELECT, only 1000 records shows up (MySQL Workbench's default limit). I know I can change the limit, or remove the limit, but I don't want 1,000,000 records to be loaded into the result panel (which might crash my computer?), but I do want to save the results to a file.

Does MySQL Workbench let you save the results of a query directly to a file? Or save the whole result set instead of the 1,000?

Upvotes: 55

Views: 72510

Answers (4)

Sahil Seta
Sahil Seta

Reputation: 1

There is an option available in MySql to export all the records. Named Query results operations.

enter image description here

Upvotes: -1

user86614
user86614

Reputation: 1458

It is possible to change the query result row limit, or remove the limit entirely.

  1. Go to Edit → Preferences → SQL Editor (tab). If you can't find Query Results, go to SQL Queries(tab) instead.

  2. Locate the Query Results section and untick the Limit Rows checkbox

  3. Click OK.

  4. Re-run your query.

Upvotes: 55

vbence
vbence

Reputation: 20343

LOAD DATA INFILE has a sibling called SELECT ... INTO OUTFILE. You can use it like:

SELECT * FROM mytable
INTO OUTFILE '/tmp/mytable.csv'
FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This method will not use unnecessary resources in the UI.

As @florian pointed out: It will, however, create the output file on the database server, not on the client machine.

Also note that security frameworks like SELinux or AppArmor might prevent MySQL from writing files outside the standard databases folder. If you experience permission denied errors, even though the directory is writable by the mysql user, it is likely be one of these.

Upvotes: 42

Tonvin
Tonvin

Reputation: 95

Just add 'limit $number' append the SQL clause. if you do not add limit,the default returned lines is 1000.

Upvotes: 6

Related Questions