Author Archive

Mysql – enable table logging in general_log

Sometimes in DB used by different applications you might like to monitor what queries are executed and see them directly on your mysql client, like SqlYog or Sequel Pro.

How to show a column result in a one line comma separated list in MySql

Often I need to extract a list of articles IDs from my MySql databases but getting the result in the default column is not enough and it can be easier to convert the result from a column into a one line list of comma separated results, for instance from

Select id from myTable

but your expected output is 64, 85, 25, 63

To concatenate multiple rows results into a single mySql field you can use the group_concat built-in function (to make sure your MySql version has this function you would like to check in the manual).

Let’s see a real life example query where we can apply this function. In my db I have a list of additional information items identified by ID related to a specific website entry/profile. The query below allows me to collect all the IDs of the entries that relate to ‘www.example.com’

SELECT GROUP_CONCAT(additional_information_id)
FROM  `company_profile_additionalinformation`  WHERE  profile_website ='www.example.com' ;

Please note the result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. This can be changed. Check the manual for more information .