MySQL – OUTFILE example

admin

May 8, 2012

[adsense id=”0514458240″ width=”468″ height=”60″]

MySQL provides a useful extension to SELECT statements allowing the contents to be placed into an external file.

Consider the following dataset:

mysql> select * from t1;
+--------+------+------+
| col1   | col2 | col3 |
+--------+------+------+
| ronan4 |  100 |  115 | 
| ronan4 |  155 |  116 | 
| ronan4 |  225 |  250 | 
| ronan4 |  300 |  275 | 
| ronan4 |  325 |  350 | 
| ronan4 |    1 |    2 | 
| ronan4 |    1 |    3 | 
| t13    |    7 |    6 | 
+--------+------+------+
8 rows in set (0.01 sec)

We want to return a single comma separated list of col1 entries:

mysql> select group_concat(col1) from t1;
+------------------------------------------------------+
| group_concat(col1)                                   |
+------------------------------------------------------+
| ronan4,ronan4,ronan4,ronan4,ronan4,ronan4,ronan4,t13 | 
+------------------------------------------------------+
1 row in set (0.00 sec)

In order to remove duplicates we will use the DISTINCT keyword:

mysql> select group_concat(distinct col1) from t1;
+-----------------------------+
| group_concat(distinct col1) |
+-----------------------------+
| ronan4,t13                  | 
+-----------------------------+
1 row in set (0.00 sec)

Now that we have determined our data set we want this output to a file:

mysql> select group_concat(distinct col1) into outfile '/tmp/names.txt' from t1;
Query OK, 1 row affected (0.00 sec)

Looking at the contents of this file:

$ cat /tmp/names.txt 
ronan4,t13

[googleplus]

MySQL