MySQL IF() usage

This article discusses the usage of the MySQL IF() function to display the status of a BOOLEAN or TINYINT(1) column. While working on a tool to track a migration, I needed a means to show the migration status of a collection of tasks. If all the tasks had been migrated, then I needed to summarize that collection as being “migrated”. Where as, if any one task was incomplete, all tasks in that collection needed to be summarized as being “not migrated”.

This particular project was to make changes to collections of templates that were grouped by a server. To do this, I used the MySQL IF() function to check the number of templates in the collection was equal to the summation of the boolean flag associated with each template. When a template was migrated, it’s BOOLEAN flag was set to 1.

The IF() function usage is as follows:

IF(expr1, expr2, expr3)

This means that IF expr1 is true, then expr2 is returned. Meanwhile, if expr1 is false, expr3 is returned. To accomplish my original task, I had to use the SUM() function to add up all the “migrated” BOOLEAN flags and compare that number with the total # of templates. If the two numbers were equal, then I wanted to display “migrated”. If they didn’t equal each other, that meant that either none or some of the templates were migrated, but NOT all.

The following is the code used to perform this nifty calculation:

SELECT DISTINCT(a.server_id), 
IF(SUM(b.migrated_flag) = COUNT(b.migrated_flag), 'migrated', 'not migrated')
FROM servers a, templates b
WHERE a.template_id = b.template_id
GROUP BY a.server_id