[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]