Sunday, July 29, 2012

MySQL: list all fields into a CSV result or set custom delimiter

So the topic here is how would you come up with listing all the fields of a table where it will output into a comma separated values?


Say for example, you have a database named BILLING with a sample table name and fields below.


__________________
|                 |
|    CUSTOMERS    |
|_________________|
| name            |
| address         |
| username        |
| password        |
|_________________|


and wanted to have a result of name, address, username, and password. You have to query the database supplied by default in MySQL using the DB name INFORMATION_SCHEMA and then filter the result with its table name and select only the COLUMNS field.

So the query should look something like this,

// set a value for the variable @table
mysql > set @table='customers';

mysql> select GROUP_CONCAT(COLUMN_NAME SEPARATOR ',') from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='BILLING' AND TABLE_NAME=@table;

The result would look like:

name,address,username,password.


Hope this helps.




No comments: