MySQL IF EXISTS - example removing users

It wasn't until MySQL 5.7 where the CREATE USER IF NOT EXISTS and DROP USER IF EXISTS syntax became available. Previous to that you could use SELECT IF (EXISTS... commands to do the same thing.

Here is an example of using SELECT IF (EXISTS... to drop a user.

-- Step 1: First create two test users with different ip ranges:

CREATE USER 'test'@'%' IDENTIFIED BY 'blabla';
CREATE USER 'test'@'10.%' IDENTIFIED BY 'blabla';


-- Step 2: Next set the user you want to drop:

SET @user = 'test';
SELECT (SELECT host
FROM mysql.user WHERE user = @user LIMIT 1) into @ip_range;

-- Step 3: Confirm variables have values

SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');

-- Step 4: Run the SELECT IF (EXISTS and then run the prepared statement

SELECT IF (EXISTS(
         SELECT DISTINCT user
         FROM mysql.user
         WHERE user = @user )
         ,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
         ,concat('select \'user does not exist: ',@user,'\';')) into @a;
     

PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

-- Step 5: Check and see if there are additional users with the same name but different IP range, if so repeat

SELECT IF (EXISTS(
         SELECT DISTINCT user
         FROM mysql.user
         WHERE user = @user )
         ,concat('Another user called \'',@user,'\'',' still exists.')
         ,concat('User \'',@user,'\' no longer exists.')) into @b;


SELECT @b;


Here are some actual examples I've written:

-- Clean up monitor_user user which doesn't have host value as %
SET sql_log_bin = 0;
SET @user = 'monitor_user';
SELECT IFNULL((SELECT host
FROM mysql.user WHERE user = @user AND host <> '%' LIMIT 1),'HOST_DOES_NOT_EXISTS') into @ip_range;
SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');
SELECT IF (EXISTS(
         SELECT DISTINCT user
         FROM mysql.user
         WHERE user = @user
         AND host = @ip_range)
         ,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
         ,concat('select \'user does not exist: ',@user,'@',@ip_range,'\';')) into @a;
         SELECT @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET sql_log_bin = 1;

-- Clean up root user that has the host value which is the same as the hostname
SET sql_log_bin = 0;
SET @user = 'root';
SELECT IFNULL((SELECT host
FROM mysql.user WHERE user = @user AND host <> '%' AND host = @@hostname LIMIT 1),'HOST_DOES_NOT_EXISTS')  into @ip_range;
SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');
SELECT IF (EXISTS(
         SELECT DISTINCT user
         FROM mysql.user
         WHERE user = @user
          AND host = @ip_range)
         ,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
         ,concat('select \'user does not exist: ',@user,'\';')) into @a;
         SELECT @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET sql_log_bin = 1;

-- Remove root user that has host value from previous co-master
SET sql_log_bin = 0;

SELECT (SELECT host
FROM mysql.user
WHERE host NOT IN ('%','localhost','::1','127.0.0.1')
AND host LIKE CONCAT(LEFT(host, LENGTH(host) - 1),'%') LIMIT 1) into @old_co_master;
SELECT @old_co_master;
SET @user = 'root';
SELECT IFNULL((SELECT host
FROM mysql.user WHERE user = @user AND host <> '%' AND host = @old_co_master LIMIT 1),'HOST_DOES_NOT_EXISTS')  into @ip_range;
SELECT concat('\'',@user,'\'@\'',@ip_range,'\'');
SELECT IF (EXISTS(
         SELECT DISTINCT user
         FROM mysql.user
         WHERE user = @user
          AND host = @ip_range)
         ,concat('drop user \'',@user,'\'@\'',@ip_range,'\';')
         ,concat('select \'user does not exist: ',@user,'\';')) into @a;
         SELECT @a;
PREPARE stmt1 FROM @a;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

SET sql_log_bin = 1;