Exporting MySQL table into a csv file

问题:

I have a MySQL table which has to be taken out as a csv file, The query I used is

SELECT "ID","NAME","SALARY","SAL1","SAL2","SAL3","SAL4","SAL5","SAL6","SAL7","SAL8","SAL9","SAL10","SAL11","SAL12","SAL13","SAL14","SAL15","SAL16","SAL17","SAL18","SAL19","SAL20","SAL21","SAL22","SAL23","SAL24","SAL25","SAL26"
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE "E:\\JOSE DATA\\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This query works, what if I have 200 column names? Is there any way to do it without manually typing it?

回答1:

This command almost gives you what you want, and it even works with a remote server. The only caveat is that it generates a TSV files (fields are separated by a tab).

mysql mydb -e "select * from mytable" -B > mytable.tsv 

But you could convert it to CSV using sed, as suggested in this answer

mysql mydb -e "select * from mytable" -B | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > mytable.csv

回答2:

DESCRIBE addstock25;

Strip the first column and first 3 entry of that column (depends on your usage). You will get the list of fields in addstock25.

This will bring only field names using virtual tables derived in core... called information schema.

SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_NAME`='foo';

Lets say name of this query would be sq_fieldnamelist

So, above table have one column and it has the field names of th "foo" table.

If directly write like this

    SELECT (sq_fieldnamelist)
    UNION ALL
    SELECT *
    FROM addstock25
    INTO OUTFILE "E:\\JOSE DATA\\addstock7.csv"
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n

Mysql will give an error. "subquery returns multiple row"

We must edit sq_fieldnamelist to concat all entries back to back separated with commas.

Select GROUP_CONCAT(COLUMN_NAME)
FROM
(SELECT `COLUMN_NAME` 
FROM `INFORMATION_SCHEMA`.`COLUMNS` 
WHERE `TABLE_NAME`='ffd_companies'
LIMIT 3,100
) AS fafa
GROUP BY 'COLUMN_NAME' // this group by is just to make group concat work

Lets say this is sq_fieldnamelist2

If we edit sq_fieldnamelist like this. It will return only one value which is all field names seperated with commas. So now we can put this subquery to your select statement to acquire needed fields.

SELECT (sq_fieldnamelist2)
UNION ALL
SELECT *
FROM addstock25
INTO OUTFILE "E:\\JOSE DATA\\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n

You need to edit LIMIT 3,100 in sq_fieldnamelist2 for you own purpose.

lets say your table is like fil1,fil2...filN,sal1,sal2,sal3....,salI to see the only salary fields you should use LIMIT N,x>I+N . if you want to see all use LIMIT 0,x>N+I

回答3:

I'm not seeing why you can't do

SELECT *
FROM addstock25
INTO OUTFILE "E:\\JOSE DATA\\addstock7.csv"
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n

?

回答4:

I think you are looking for something like this.

    SET @sql = NULL;
        SELECT GROUP_CONCAT("'",COLUMN_NAME,"'")
          FROM
        (SELECT `COLUMN_NAME`
        FROM `INFORMATION_SCHEMA`.`COLUMNS` 
        WHERE `TABLE_SCHEMA` = 'yourdatabasename'
        and `TABLE_NAME`='ffd_companies'
        ) AS colnames
        GROUP BY 'COLUMN_NAME' 
        into @sql;

        SET @sql = concat ("SELECT", @sql, " from dual
        UNION ALL
        SELECT *
        FROM addstock25
        INTO OUTFILE 'E:\\JOSE DATA\\addstock7.csv'
        FIELDS TERMINATED BY ','
        ENCLOSED BY '", '"',"'
        )"
        );

        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

Hope this helps you.

Note: i add the WHERE clause TABLE_SCHEMA = 'yourdatabasename'

回答5:

I have integrated the script for simple UI based code. Here you can enter query and simply can get the csv file containing all rows including column names.

This script works for both windows and linux systems.

https://github.com/Bihari12/databasetocsv

This would save the result in csv file in the same folder in which the code exists.