Recently, I went to dump a MYSQL table into a CSV file, with the fieldnames in the first line of the file.
This simple enough in Oracle’s SQLPLUS, so I did some internet searches to determine the syntax.
Instead of the simple MYSQL syntax, I found a number of relatively complex solutions. Some involved MYSQL scripts, and others utilized Unix string parsing commands.
However, I came up with a much simpler solution using one simple MYSQL command:
SELECT "field1" , "field2" , "field3" , "field4" from table1 union SELECT field1 , field2 , field3 , field4 from table1 INTO OUTFILE 'export1.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
You will find the .csv file under the datadir directory, which you can find using the command:
show variables like 'datadir'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+
Under the datadir directories, will be a subdirectory for the particular database you are using (use testdb).
If you specify a full path to a different directory, be sure that the Linux mysql user can write to that directory first.
That’s it. Nothing like simplicity over complexity.
Hope it’s useful!
Other Links:
MySQL: How to export data to csv with column headers
http://lifeboysays.wordpress.com/2012/06/23/mysql-how-to-export-data-to-csv-with-column-headers/
MySQL dump into CSV text files with column names at the top?
How to export / dump a MySql table into a text file including the field names (aka headers or column names)
Of course, this is much simpler than creating a function to deal with it, but the downside is that you have to go and find the column names first and then write the query. Using the function approach is more work, once and then a matter of called the function with a table name. If you have many tables with many columns it can become quite tedious.
HI Roland,
On the other hand, as I point out in DB Design Mistakes To Avoid, table structures rarely change, compared to processes and code.
http://rodgersnotes.wordpress.com/2010/09/14/database-design-mistakes-to-avoid/
So, unless you have dozens of tables, and hundreds of columns, to worry about, it’s probably faster to just edit a few queries. Any data migration always involves a certain amount of code and data wrangling.
This query to the data dictionary, to get the column names, can make things easier:
select concat ( COLUMN_NAME , ” ,” ) as column_name
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = “table1”
order by ORDINAL_POSITION
HTH