How to export table to excel from mysql database?

问题:

I have a table in mysql which is quite big for having over 100k rows and I want to export it to excel. However, I tried the export to excel function on phpmyadmin but it takes forever to dump the excel file. It's not even dumping. The error is always, "the connection is reset". Is there an alternative way on how to do this??

回答1:

First, 100k rows in Excel sounds like a horrible idea and of course it'll take awhile. This is going to take awhile just to open. If you MUST do this try:

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

This should give you a file called: /tmp/orders.csv which will open in Excel.

回答2:

The slowness of your export is likely due to the server on which phpmyadmin runs. I regularly export millions of rows with surprising speed.

If you have access to the file system of your database server, this is a very fast way of converting to .csv, which in turn can be opened by Excel.

SELECT order_id,product_name,qty
FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

See this: http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

回答3:

I've used the OUTFILE method before to do this:

SELECT
    'First Name',
    'Last Name',
    'Email Address'
UNION
SELECT
    First_Name,
    Last_Name,
    Email
INTO OUTFILE
    '/tmp/data_2012-05-03.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
FROM
    `data`
WHERE
    Reg_Date >= '2012-05-02 00:00:00' AND
    Reg_Date <= '2012-05-02 23:59:59' AND
    Email_Status = 1;